[Home] [Help]
PACKAGE BODY: APPS.JL_BR_SPED_PUB
Source
1 PACKAGE BODY JL_BR_SPED_PUB AS
2 /* $Header: jlspedpb.pls 120.0.12010000.2 2008/11/13 08:00:42 pakumare noship $ */
3
4 -- Global Constant variable for holding Package Name
5
6 G_PKG_NAME CONSTANT VARCHAR2(20):= 'JL_BR_SPED_PUB';
7
8 -- Declare VARRAY
9
10 TYPE token_array IS TABLE OF VARCHAR2(25) INDEX BY binary_integer;
11 TYPE value_for_token IS TABLE OF VARCHAR2(25) INDEX BY binary_integer;
12
13
14 -- Function REPLACE_TOKEN for replacing tokens with actual value
15
16 FUNCTION REPLACE_TOKEN(
17 msg IN VARCHAR2,
18 tokens IN token_array,
19 tokenValues IN value_for_token) RETURN VARCHAR2 IS
20
21 message VARCHAR2(1000);
22
23 BEGIN
24 message := msg;
25 FOR iNtex IN tokens.FIRST .. tokens.LAST LOOP
26 message := replace(message,tokens(iNtex),tokenValues(iNtex));
27 END LOOP;
28
29 RETURN message;
30
31 END REPLACE_TOKEN;
32
33 -- UPDATE_ATTRIBUTES Procedure - To update the attributes
34
35 PROCEDURE UPDATE_ATTRIBUTES (
36 P_API_VERSION IN NUMBER DEFAULT 1.0,
37 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
38 P_CUSTOMER_TRX_ID IN NUMBER,
39 P_ELECT_INV_WEB_ADDRESS IN VARCHAR2,
40 P_ELECT_INV_STATUS IN VARCHAR2,
41 P_ELECT_INV_ACCESS_KEY IN VARCHAR2,
42 P_ELECT_INV_PROTOCOL IN VARCHAR2,
43 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
44 X_MSG_DATA OUT NOCOPY VARCHAR2)
45
46 IS
47
48 -- Declaration part
49
50 CURSOR C_EXT IS
51 SELECT COUNT(*) as cnt FROM JL_BR_CUSTOMER_TRX_EXTS
52 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
53
54 CURSOR C_TRX IS
55 SELECT 'Yes' as isExist
56 FROM JL_BR_CUSTOMER_TRX_EXTS
57 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
58 AND ELECTRONIC_INV_STATUS IN ('2','7');
59
60 CURSOR C_CUST_TRX_EXIST IS
61 SELECT 'Yes' as isExist FROM RA_CUSTOMER_TRX_ALL
62 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
63
64 CURSOR C_VALID_EI_STATUS IS
65 SELECT 'Yes' as isExist FROM FND_LOOKUPS
66 WHERE LOOKUP_TYPE = 'JLBR_EI_STATUS'
67 AND LOOKUP_CODE = P_ELECT_INV_STATUS;
68
69 l_count NUMBER;
70 isExist VARCHAR2(5) := 'No';
71 l_api_name VARCHAR2(30) := 'UPDATES_ATTRIBUTES';
72 l_api_version NUMBER := 1.0;
73 inv_status_final EXCEPTION;
74 invalid_cust_trx_id EXCEPTION;
75 invalid_ei_status EXCEPTION;
76 incompatible_apiversion EXCEPTION;
77 invalid_commit_param EXCEPTION;
78 invalid_apiversion EXCEPTION;
79
80 tok_arr token_array;
81 val_for_token value_for_token;
82
83 BEGIN
84
85 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
86
87 -- Standard call to check for call compatibility
88
89 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, P_API_VERSION, l_api_name, G_PKG_NAME) THEN
90 tok_arr(1) := '&'||'CURR_VER_NUM';
91 tok_arr(2) := '&'||'API_NAME';
92 tok_arr(3) := '&'||'PKG_NAME';
93 tok_arr(4) := '&'||'CALLER_VER_NUM';
94 val_for_token(1) := l_api_version;
95 val_for_token(2) := l_api_name;
96 val_for_token(3) := G_PKG_NAME;
97 val_for_token(4) := P_API_VERSION;
98 IF TRUNC(l_api_version) > TRUNC(P_API_VERSION) THEN
99 RAISE incompatible_apiversion;
100 ELSE
101 RAISE invalid_apiversion;
102 END IF;
103 END IF;
104
105
106 FOR cust_trx_exist_rec IN C_CUST_TRX_EXIST LOOP
107 isExist := cust_trx_exist_rec.isExist;
108 END LOOP;
109
110 /* If Valid customer Trx ID is passed */
111 IF isExist = 'Yes' THEN
112
113 isExist := 'No'; -- Reinitializing the var
114
115 FOR ei_status_rec IN C_VALID_EI_STATUS LOOP
116 isExist := ei_status_rec.isExist;
117 END LOOP;
118
119 /* If Valid Electronic Invoice Staus is passed */
120 IF isExist = 'Yes' THEN
121 FOR ext_rec IN C_EXT LOOP
122 l_count := ext_rec.cnt;
123 END LOOP;
124
125 IF l_count = 0 THEN
126 -- Create an entry in JL_BR_CUSTOMER_TRX_EXTS table
127 INSERT INTO JL_BR_CUSTOMER_TRX_EXTS(
128 CUSTOMER_TRX_ID,
129 ELECTRONIC_INV_WEB_ADDRESS,
130 ELECTRONIC_INV_STATUS,
131 ELECTRONIC_INV_ACCESS_KEY,
132 ELECTRONIC_INV_PROTOCOL,
133 LAST_UPDATE_DATE,
134 LAST_UPDATED_BY,
135 LAST_UPDATE_LOGIN,
136 CREATED_BY,
137 CREATION_DATE)
138 VALUES (
139 P_CUSTOMER_TRX_ID,
140 P_ELECT_INV_WEB_ADDRESS,
141 P_ELECT_INV_STATUS,
142 P_ELECT_INV_ACCESS_KEY,
143 P_ELECT_INV_PROTOCOL,
144 SYSDATE,
145 -1,
146 -1,
147 -1,
148 SYSDATE);
149 ELSE
150
151 FOR trx_rec IN C_TRX LOOP
152 RAISE inv_status_final;
153 END LOOP;
154
155 UPDATE JL_BR_CUSTOMER_TRX_EXTS
156 SET ELECTRONIC_INV_WEB_ADDRESS = P_ELECT_INV_WEB_ADDRESS,
157 ELECTRONIC_INV_STATUS = P_ELECT_INV_STATUS,
158 ELECTRONIC_INV_ACCESS_KEY = P_ELECT_INV_ACCESS_KEY,
159 ELECTRONIC_INV_PROTOCOL = P_ELECT_INV_PROTOCOL,
160 LAST_UPDATE_DATE = SYSDATE,
161 LAST_UPDATED_BY = -1,
162 LAST_UPDATE_LOGIN = -1
163 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
164
165 END IF;
166
167 IF P_ELECT_INV_STATUS IN ('2','7') THEN
168 UPDATE AR_PAYMENT_SCHEDULES_ALL
169 SET SELECTED_FOR_RECEIPT_BATCH_ID = NULL
170 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
171 END IF;
172
173
174 BEGIN
175 IF FND_API.TO_BOOLEAN(P_COMMIT) THEN
176 COMMIT;
177 ELSE
178 ROLLBACK;
179 END IF;
180 EXCEPTION
181 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
182 RAISE invalid_commit_param;
183 END;
184
185
186 ELSE /* If Invalid Electronic Invoice Status is passed */
187 tok_arr(1) := '&'||'INV_STATUS';
188 val_for_token(1) := P_ELECT_INV_STATUS;
189 RAISE invalid_ei_status;
190 END IF;
191 ELSE /* If Invalid customer Trx ID is passed */
192 tok_arr(1) := '&'||'TRX_ID';
193 val_for_token(1) := P_CUSTOMER_TRX_ID;
194 RAISE invalid_cust_trx_id;
195 END IF;
196
197 EXCEPTION
198
199 WHEN invalid_apiversion THEN
200 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
201 X_MSG_DATA := FND_MESSAGE.GET_STRING('FND','FND_AS_INVALID_VER_NUM',NULL);
202 X_MSG_DATA := REPLACE_TOKEN(X_MSG_DATA,tok_arr,val_for_token);
203
204 WHEN incompatible_apiversion THEN
205 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
206 X_MSG_DATA := FND_MESSAGE.GET_STRING('FND','FND_AS_INCOMPATIBLE_API_CALL',NULL);
207 X_MSG_DATA := REPLACE_TOKEN(X_MSG_DATA,tok_arr,val_for_token);
208
209 WHEN invalid_cust_trx_id THEN
210 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
211 X_MSG_DATA := FND_MESSAGE.GET_STRING('JL','JL_BR_INVALID_CUST_TRX_ID',NULL);
212 X_MSG_DATA := REPLACE_TOKEN(X_MSG_DATA,tok_arr,val_for_token);
213
214 WHEN invalid_ei_status THEN
215 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
216 X_MSG_DATA := FND_MESSAGE.GET_STRING('JL','JL_BR_INVALID_EI_STATUS',NULL);
217 X_MSG_DATA := REPLACE_TOKEN(X_MSG_DATA,tok_arr,val_for_token);
218
219 WHEN inv_status_final THEN
220 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
221 X_MSG_DATA := FND_MESSAGE.GET_STRING('JL','JL_BR_EI_FINALIZED',NULL);
222
223 WHEN invalid_commit_param THEN
224 ROLLBACK;
225 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
226 X_MSG_DATA := FND_MESSAGE.GET_STRING('JL','JL_BR_API_COMMIT',NULL);
227
228 WHEN OTHERS THEN
229 ROLLBACK;
230 tok_arr(1) := 'ERRMSG';
231 val_for_token(1) := SQLERRM;
232 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
233 X_MSG_DATA := FND_MESSAGE.GET_STRING('JL','JL_BR_API_ERROR',NULL);
234 X_MSG_DATA := REPLACE_TOKEN(X_MSG_DATA,tok_arr,val_for_token);
235
236 END UPDATE_ATTRIBUTES;
237
238 -- INSERT_LOG Procedure - To insert the log
239
240 PROCEDURE INSERT_LOG (
241 P_API_VERSION IN NUMBER DEFAULT 1.0,
242 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
243 P_CUSTOMER_TRX_ID IN NUMBER,
244 P_OCCURRENCE_DATE IN DATE,
245 P_ELECT_INV_STATUS IN VARCHAR2,
246 P_MESSAGE_TEXT IN VARCHAR2,
247 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
248 X_MSG_DATA OUT NOCOPY VARCHAR2)
249
250 IS
251
252 -- Declaration part
253
254 CURSOR C_INV_STATUS IS
255 SELECT ELECTRONIC_INV_STATUS
256 FROM JL_BR_CUSTOMER_TRX_EXTS
257 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
258
259 CURSOR C_CUST_TRX_EXIST IS
260 SELECT 'Yes' as isExist FROM RA_CUSTOMER_TRX_ALL
261 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
262
263 CURSOR C_VALID_EI_STATUS IS
264 SELECT 'Yes' as isExist FROM FND_LOOKUPS
265 WHERE LOOKUP_TYPE = 'JLBR_EI_STATUS'
266 AND LOOKUP_CODE = P_ELECT_INV_STATUS;
267
268 l_api_name VARCHAR2(30) := 'INSERT_LOG';
269 l_api_version NUMBER := 1.0;
270 isExist VARCHAR2(5) :='No';
271 inv_status_differs EXCEPTION;
272 invalid_cust_trx_id EXCEPTION;
273 invalid_ei_status EXCEPTION;
274 incompatible_apiversion EXCEPTION;
275 invalid_commit_param EXCEPTION;
276 invalid_apiversion EXCEPTION;
277
278 tok_arr token_array;
279 val_for_token value_for_token;
280
281 BEGIN
282
283 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
284
285 -- Standard call to check for call compatibility
286
287 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, P_API_VERSION, l_api_name, G_PKG_NAME) THEN
288 tok_arr(1) := '&'||'CURR_VER_NUM';
289 tok_arr(2) := '&'||'API_NAME';
290 tok_arr(3) := '&'||'PKG_NAME';
291 tok_arr(4) := '&'||'CALLER_VER_NUM';
292 val_for_token(1) := l_api_version;
293 val_for_token(2) := l_api_name;
294 val_for_token(3) := G_PKG_NAME;
295 val_for_token(4) := P_API_VERSION;
296 IF TRUNC(l_api_version) > TRUNC(P_API_VERSION) THEN
297 RAISE incompatible_apiversion;
298 ELSE
299 RAISE invalid_apiversion;
300 END IF;
301 END IF;
302
303
304 FOR cust_trx_exist_rec IN C_CUST_TRX_EXIST LOOP
305 isExist := cust_trx_exist_rec.isExist;
306 END LOOP;
307
308 /* If Valid customer Trx ID is passed */
309 IF isExist = 'Yes' THEN
310
311 isExist := 'No'; -- Reinitializing the var
312
313 FOR ei_status_rec IN C_VALID_EI_STATUS LOOP
314 isExist := ei_status_rec.isExist;
315 END LOOP;
316
317 /* If Valid Electronic Invoice Staus is passed */
318 IF isExist = 'Yes' THEN
319
320 FOR inv_status_rec IN C_INV_STATUS LOOP
321 IF inv_status_rec.ELECTRONIC_INV_STATUS <> P_ELECT_INV_STATUS THEN
322 RAISE inv_status_differs;
323 END IF;
324 END LOOP;
325
326
327 -- Create an entry in JL_BR_EILOG table
328 INSERT INTO JL_BR_EILOG (
329 OCCURRENCE_ID,
330 OCCURRENCE_DATE,
331 CUSTOMER_TRX_ID,
332 ELECTRONIC_INV_STATUS,
333 MESSAGE_TXT,
334 LAST_UPDATE_DATE,
335 LAST_UPDATED_BY,
336 LAST_UPDATE_LOGIN,
337 CREATION_DATE,
338 CREATED_BY)
339 VALUES
340 (JL_BR_EILOG_S.NEXTVAL,
341 P_OCCURRENCE_DATE,
342 P_CUSTOMER_TRX_ID,
343 P_ELECT_INV_STATUS,
344 P_MESSAGE_TEXT,
345 SYSDATE,
346 -1,
347 -1,
348 SYSDATE,
349 -1);
350
351 BEGIN
352 IF FND_API.TO_BOOLEAN(P_COMMIT) THEN
353 COMMIT;
354 ELSE
355 ROLLBACK;
356 END IF;
357 EXCEPTION
358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
359 RAISE invalid_commit_param;
360 END;
361
362 ELSE /* If Invalid Electronic Invoice Status is passed */
363 tok_arr(1) := '&'||'INV_STATUS';
364 val_for_token(1) := P_ELECT_INV_STATUS;
365 RAISE invalid_ei_status;
366 END IF;
367 ELSE /* If Invalid customer Trx ID is passed */
368 tok_arr(1) := '&'||'TRX_ID';
369 val_for_token(1) := P_CUSTOMER_TRX_ID;
370 RAISE invalid_cust_trx_id;
371 END IF;
372
373
374 EXCEPTION
375
376 WHEN invalid_apiversion THEN
380
377 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
378 X_MSG_DATA := FND_MESSAGE.GET_STRING('FND','FND_AS_INVALID_VER_NUM',NULL);
379 X_MSG_DATA := REPLACE_TOKEN(X_MSG_DATA,tok_arr,val_for_token);
381 WHEN incompatible_apiversion THEN
382 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
383 X_MSG_DATA := FND_MESSAGE.GET_STRING('FND','FND_AS_INCOMPATIBLE_API_CALL',NULL);
384 X_MSG_DATA := REPLACE_TOKEN(X_MSG_DATA,tok_arr,val_for_token);
385
386 WHEN invalid_cust_trx_id THEN
387 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
388 X_MSG_DATA := FND_MESSAGE.GET_STRING('JL','JL_BR_INVALID_CUST_TRX_ID',NULL);
389 X_MSG_DATA := REPLACE_TOKEN(X_MSG_DATA,tok_arr,val_for_token);
390
391 WHEN invalid_ei_status THEN
392 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
393 X_MSG_DATA := FND_MESSAGE.GET_STRING('JL','JL_BR_INVALID_EI_STATUS',NULL);
394 X_MSG_DATA := REPLACE_TOKEN(X_MSG_DATA,tok_arr,val_for_token);
395
396 WHEN inv_status_differs THEN
397 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
398 X_MSG_DATA := FND_MESSAGE.GET_STRING('JL','JL_BR_INV_STATUS_DIFFERS',NULL);
399
400 WHEN invalid_commit_param THEN
401 ROLLBACK;
402 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
403 X_MSG_DATA := FND_MESSAGE.GET_STRING('JL','JL_BR_API_COMMIT',NULL);
404
405 WHEN OTHERS THEN
406 ROLLBACK;
407 tok_arr(0) := 'ERRMSG';
408 val_for_token(0) := SQLERRM;
409 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
410 X_MSG_DATA := FND_MESSAGE.GET_STRING('JL','JL_BR_API_ERROR',NULL);
411 X_MSG_DATA := REPLACE_TOKEN(X_MSG_DATA,tok_arr,val_for_token);
412
413 END INSERT_LOG;
414
415
416 -- GET_IBGE_CODES Procedure - To retrieve IBGE code for a given Location
417
418 PROCEDURE GET_IBGE_CODES (
419 P_API_VERSION IN NUMBER DEFAULT 1.0,
420 P_LOCATION_ID IN NUMBER ,
421 X_STATE_CODE OUT NOCOPY VARCHAR2,
422 X_CITY_CODE OUT NOCOPY VARCHAR2,
423 X_CENTRAL_BANK_CODE OUT NOCOPY VARCHAR2,
424 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
425 X_MSG_DATA OUT NOCOPY VARCHAR2)
426
427 IS
428
429 -- Declaration part
430
431 CURSOR get_state_code IS
432 SELECT
433 identifier_value state
434 FROM
435 hz_geography_identifiers geo_ident,
436 hz_geo_name_references geo_ref,
437 hz_locations loc
438 WHERE
439 loc.location_id = P_LOCATION_ID
440 and loc.location_id = geo_ref.location_id
441 and geo_ref.geography_type = 'STATE'
442 and geo_ident.identifier_subtype = 'IBGE'
443 and geo_ident.geography_id = geo_ref.geography_id
444 and geo_ident.geography_type = 'STATE';
445
446
447 CURSOR get_city_code IS
448 SELECT
449 identifier_value city
450 FROM
451 hz_geography_identifiers geo_ident,
452 hz_geo_name_references geo_ref,
453 hz_locations loc
454 WHERE
455 loc.location_id = P_LOCATION_ID
456 and loc.location_id = geo_ref.location_id
457 and geo_ref.geography_type = 'CITY'
458 and geo_ident.identifier_subtype = 'IBGE'
459 and geo_ident.geography_id = geo_ref.geography_id
460 and geo_ident.geography_type = 'CITY';
461
462 CURSOR get_bank_code IS
463 SELECT meaning
464 FROM FND_LOOKUPS
465 WHERE lookup_type = 'JLBR_CBANK_COUNTRY_CODES'
466 and lookup_code = (SELECT country
467 FROM hz_locations
468 WHERE location_id = P_LOCATION_ID);
469
470 CURSOR c_valid_location IS
471 SELECT
472 'Yes' as isexist
473 FROM HZ_LOCATIONS
474 WHERE
475 location_id = P_LOCATION_ID;
476
477
478 l_api_name VARCHAR2(30) := 'GET_IBGE_CODES';
479 l_api_version NUMBER := 1.0;
480 isExist VARCHAR2(5) := 'No';
481 invalid_loc_id EXCEPTION;
482 incompatible_apiversion EXCEPTION;
483 invalid_apiversion EXCEPTION;
484 l_state_col varchar2(30);
485 l_city_col varchar2(30);
486
487 tok_arr token_array;
488 val_for_token value_for_token;
489
490 BEGIN
491
492 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
493
494 -- Standard call to check for call compatibility
495
496 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, P_API_VERSION, l_api_name, G_PKG_NAME) THEN
497 tok_arr(1) := '&'||'CURR_VER_NUM';
498 tok_arr(2) := '&'||'API_NAME';
499 tok_arr(3) := '&'||'PKG_NAME';
500 tok_arr(4) := '&'||'CALLER_VER_NUM';
501 val_for_token(1) := l_api_version;
502 val_for_token(2) := l_api_name;
503 val_for_token(3) := G_PKG_NAME;
504 val_for_token(4) := P_API_VERSION;
505 IF TRUNC(l_api_version) > TRUNC(P_API_VERSION) THEN
506 RAISE incompatible_apiversion;
507 ELSE
508 RAISE invalid_apiversion;
509 END IF;
510 END IF;
511
512
513 FOR location_rec IN c_valid_location LOOP
514 isExist := location_rec.isExist;
515 END LOOP;
516
517 /* If Valid Location ID is passed */
518 IF isExist = 'Yes' THEN
519
520 -- get state code
521 FOR c_state_code_rec IN get_state_code LOOP
522 X_STATE_CODE := c_state_code_rec.state;
523 END LOOP;
524
525 -- get city code
526
527 FOR c_city_code_rec IN get_city_code LOOP
528 X_CITY_CODE := c_city_code_rec.city;
532 FOR c_bank_code_rec IN get_bank_code LOOP
529 END LOOP;
530
531 -- get bank code
533 X_CENTRAL_BANK_CODE := c_bank_code_rec.meaning;
534 END LOOP;
535
536 ELSE
537 tok_arr(1) := '&'||'LOC_ID';
538 val_for_token(1) := P_LOCATION_ID;
539 RAISE invalid_loc_id;
540 END IF;
541
542 EXCEPTION
543
544 WHEN invalid_apiversion THEN
545 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
546 X_MSG_DATA := FND_MESSAGE.GET_STRING('FND','FND_AS_INVALID_VER_NUM',NULL);
547 X_MSG_DATA := REPLACE_TOKEN(X_MSG_DATA,tok_arr,val_for_token);
548
549 WHEN incompatible_apiversion THEN
550 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
551 X_MSG_DATA := FND_MESSAGE.GET_STRING('FND','FND_AS_INCOMPATIBLE_API_CALL',NULL);
552 X_MSG_DATA := REPLACE_TOKEN(X_MSG_DATA,tok_arr,val_for_token);
553
554 WHEN invalid_loc_id THEN
555 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
556 X_MSG_DATA := FND_MESSAGE.GET_STRING('JL','JL_BR_INVALID_LOC_ID',NULL);
557 X_MSG_DATA := REPLACE_TOKEN(X_MSG_DATA,tok_arr,val_for_token);
558
559 WHEN OTHERS THEN
560 ROLLBACK;
564 X_MSG_DATA := FND_MESSAGE.GET_STRING('JL','JL_BR_API_ERROR',NULL);
561 tok_arr(1) := 'ERRMSG';
562 val_for_token(1) := SQLERRM;
563 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
565 X_MSG_DATA := REPLACE_TOKEN(X_MSG_DATA,tok_arr,val_for_token);
566
567 END GET_IBGE_CODES;
568
569 END JL_BR_SPED_PUB;