[Home] [Help]
PACKAGE BODY: APPS.FUN_BAL_UTILS_GRP
Source
1 PACKAGE BODY FUN_BAL_UTILS_GRP AS
2 /* $Header: fungbalutilb.pls 120.7.12010000.7 2009/02/23 14:35:59 srampure ship $ */
3 g_debug_level NUMBER;
4 g_package_name VARCHAR2(30) := 'FUN_BAL_UTILS_GRP';
5
6 CURSOR c_get_le_id (p_ledger_id NUMBER,
7 p_bsv VARCHAR2,
8 p_gl_date DATE)
9 IS
10 SELECT vals.legal_entity_id
11 FROM gl_ledger_le_bsv_specific_v vals
12 WHERE vals.segment_value = p_bsv
13 AND vals.ledger_id = p_ledger_id
14 AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(vals.start_date, p_gl_date)) AND
15 TRUNC(NVL(vals.end_date, p_gl_date)));
16
17 CURSOR c_get_seg_num(p_ledger_id NUMBER,
18 p_segment_type VARCHAR2)
19 IS
20 SELECT fun_bal_pkg.get_segment_index(ledgers.chart_of_accounts_id,
21 p_segment_type),
22 ledgers.chart_of_accounts_id
23 FROM gl_ledgers ledgers
24 WHERE ledgers.ledger_id = p_ledger_id;
25
26 /* ----------------------------------------------------------------------------
27 -- API name : FUN_BAL_UTILS_GRP.get_inter_intra_account
28 -- Type : Group
29 -- Pre-reqs : None.
30 -- Function : Given a transacting and trading Balancing segment value, the
31 -- the procedure determines what type of account is required
32 -- ie inter or intra company accounts and returns the same
33 -- Parameters :
34 -- IN :
35 -- p_api_version IN NUMBER Required
36 -- p_init_msg_list IN VARCHAR2 Optional
37 -- p_ledger_id IN NUMBER Required
38 -- p_from_bsv IN VARCHAR2 Required
39 -- p_to_bsv IN VARCHAR2 Required
40 -- p_source IN VARCHAR2 Optional
41 -- p_category IN VARCHAR2 Optional
42 -- p_gl_date IN DATE Required
43 -- p_acct_type IN VARCHAR2 Required
44 -- Account type would be 'D'ebit(Receivables)
45 -- Or 'C'redit' (Payables)
46 --
47 -- OUT :
48 -- x_status VARCHAR2
49 -- x_msg_count NUMBER
50 -- x_msg_data VARCHAR2
51 -- x_ccid VARCHAR2 CCID requested
52 -- x_reciprocal_ccid VARCHAR2 Reciprocal CCID
53 -- Eg. If receivable account ccid is requested for BSV1 => BSV2
54 -- x_reciprocal_ccid will contain the payable account for
55 -- BSV2 => BSV1
56 --
57 -- Version : Current version 1.0
58 -- Previous version 1.0
59 -- Initial version 1.0
60 ------------------------------------------------------------------------------*/
61 PROCEDURE get_inter_intra_account (p_api_version IN NUMBER,
62 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
63 p_ledger_id IN NUMBER,
64 p_to_ledger_id IN NUMBER,
65 p_from_bsv IN VARCHAR2,
66 p_to_bsv IN VARCHAR2,
67 p_source IN VARCHAR2,
68 p_category IN VARCHAR2,
69 p_gl_date IN DATE,
70 p_acct_type IN VARCHAR2,
71 x_status IN OUT NOCOPY VARCHAR2,
72 x_msg_count IN OUT NOCOPY NUMBER,
73 x_msg_data IN OUT NOCOPY VARCHAR2,
74 x_ccid IN OUT NOCOPY NUMBER ,
75 x_reciprocal_ccid IN OUT NOCOPY NUMBER)
76 IS
77
78
79 l_api_name CONSTANT VARCHAR2(30) := 'Get_Inter_Intra_Account';
80 l_api_version CONSTANT NUMBER := 1.0;
81 l_return_status VARCHAR2(1);
82 l_from_le_id NUMBER ;
83 l_to_le_id NUMBER;
84 l_intra_txn BOOLEAN := FALSE;
85 l_inter_txn BOOLEAN := FALSE;
86 l_acct_type VARCHAR2(1);
87
88 BEGIN
89
90 -- variable p_validation_level is not used .
91 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
92
93 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
94 THEN
95 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
96 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.begin',
97 'begin');
98 END IF;
99
100 -- Standard call to check for call compatibility.
101 IF NOT FND_API.Compatible_API_Call ( l_api_version,
102 p_api_version,
103 l_api_name,
104 g_package_name )
105 THEN
106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
107 END IF;
108
109 -- Initialize message list if p_init_msg_list is set to TRUE.
110 IF FND_API.to_Boolean( p_init_msg_list )
111 THEN
112 FND_MSG_PUB.initialize;
113 END IF;
114
115 -- Initialize API return status to success
116 x_status := FND_API.G_RET_STS_SUCCESS;
117
118 OPEN c_get_le_id (p_ledger_id => p_ledger_id,
119 p_bsv => p_from_bsv,
120 p_gl_date => p_gl_date);
121 FETCH c_get_le_id INTO l_from_le_id;
122 CLOSE c_get_le_id;
123
124 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
125 THEN
126 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
127 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.frm_le',
128 'Fetched From LE Id : ' || l_from_le_id);
129 END IF;
130
131
132 OPEN c_get_le_id (p_ledger_id => p_to_ledger_id,
133 p_bsv => p_to_bsv,
134 p_gl_date => p_gl_date);
135 FETCH c_get_le_id INTO l_to_le_id;
136 CLOSE c_get_le_id;
137
138 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
139 THEN
140 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
141 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.to_le',
142 'Fetching To LE Id : ' ||l_to_le_id);
143 END IF;
144
145 IF Nvl(l_from_le_id,-99) = Nvl(l_to_le_id, -99)
146 THEN
147 -- This is an intracompany transaction
148 l_intra_txn := TRUE;
149 END IF;
150
151 IF l_from_le_id <> l_to_le_id
152 THEN
153 -- This is an intercompany transaction
154 l_inter_txn := TRUE;
155 END IF;
156
157 IF NOT(l_inter_txn) AND NOT(l_intra_txn)
158 THEN
159 -- This is an error situation
160 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTER_BSV_NOT_ASSIGNED');
161 FND_MSG_PUB.Add;
162 x_status := FND_API.G_RET_STS_ERROR;
163 END IF;
164
165 IF l_inter_txn
166 THEN
167 IF p_acct_type = 'D'
168 THEN
169 l_acct_type := 'R';
170 ELSE
171 l_acct_type := 'P';
172 END IF;
173
174 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
175 THEN
176 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
177 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.get',
178 'Fetching intercompany account ');
179 END IF;
180
181 get_intercompany_account (p_api_version => p_api_version,
182 p_init_msg_list => p_init_msg_list,
183 p_ledger_id => p_ledger_id,
184 p_from_le => l_from_le_id,
185 p_from_bsv => p_from_bsv,
186 p_to_ledger_id => p_to_ledger_id,
187 p_to_le => l_to_le_id,
188 p_to_bsv => p_to_bsv,
189 p_gl_date => p_gl_date,
190 p_acct_type => l_acct_type,
191 x_status => x_status,
192 x_msg_count => x_msg_count,
193 x_msg_data => x_msg_data,
194 x_ccid => x_ccid,
195 x_reciprocal_ccid => x_reciprocal_ccid);
196
197 END IF;
198
199 IF l_intra_txn
200 THEN
201
202 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
203 THEN
204 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
205 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.get',
206 'Fetching the intracompany ccid');
207 END IF;
208
209 get_intracompany_account (p_api_version => p_api_version,
210 p_init_msg_list => p_init_msg_list,
211 p_ledger_id => p_ledger_id,
212 p_from_le => l_from_le_id,
213 p_source => p_source,
214 p_category => p_category,
215 p_dr_bsv => p_from_bsv,
216 p_cr_bsv => p_to_bsv,
217 p_gl_date => p_gl_date,
218 p_acct_type => p_acct_type,
219 x_status => x_status,
220 x_msg_count => x_msg_count,
221 x_msg_data => x_msg_data,
222 x_ccid => x_ccid,
223 x_reciprocal_ccid => x_reciprocal_ccid);
224
225 END IF;
226
227 -- Standard call to get message count and if count is 1, get message info.
228 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
229 p_data => x_msg_data);
230
231 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
232 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.end', 'end');
233 END IF;
234
235 EXCEPTION
236 WHEN FND_API.G_EXC_ERROR
237 THEN
238 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
239 THEN
240 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
241 'fun.plsql.fun_bal_pkg.get_inter_intra_account.error',
242 SUBSTR(SQLERRM,1, 4000));
243 END IF;
244
245 x_status := FND_API.G_RET_STS_ERROR ;
246 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
247 p_data => x_msg_data);
248
249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
250 THEN
251 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
252 THEN
253 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
254 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.unexpected_error_norm',
255 SUBSTR(SQLCODE ||' : ' || SQLERRM,1, 4000));
256 END IF;
257
258 x_status := FND_API.G_RET_STS_UNEXP_ERROR ;
259 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
260 p_data => x_msg_data);
261
262 WHEN OTHERS
263 THEN
264 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
265 THEN
266 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
267 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.unexpected_error_others',
268 SUBSTR(SQLCODE ||' : ' || SQLERRM,1, 4000));
269 END IF;
270
271 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
272 THEN
273 FND_MSG_PUB.Add_Exc_Msg(g_package_name, l_api_name);
274 END IF;
275
276 x_status := FND_API.G_RET_STS_UNEXP_ERROR ;
277 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
278 p_data => x_msg_data);
279
280 END get_inter_intra_account;
281
282 /* ----------------------------------------------------------------------------
283 -- API name : FUN_BAL_UTILS_GRP.get_intercompany_account
284 -- Type : Group
285 -- Pre-reqs : None.
286 -- Function : Given a transacting and trading Balancing segment value,
287 -- the procedure returns the intercompany receivables and
288 -- payables account
289 -- Parameters :
290 -- IN : p_api_version IN NUMBER Required
291 -- p_init_msg_list IN VARCHAR2 Optional
292 -- p_ledger_id IN NUMBER Required
293 -- p_from_le IN NUMBER Required
294 -- p_from_bsv IN VARCHAR2 Required
295 -- p_to_le IN NUMBER Required
296 -- p_to_bsv IN VARCHAR2 Required
297 -- p_gl_date IN DATE Required
298 -- p_acct_type IN VARCHAR2 Required
299 -- Account type would be 'R'eceivables or 'P'ayables
300 --
301 -- OUT : x_status VARCHAR2
302 -- x_msg_count NUMBER
303 -- x_msg_data VARCHAR2
304 -- x_ccid VARCHAR2 CCID requested
305 -- x_reciprocal_ccid VARCHAR2 Reciprocal CCID
306 -- Eg. If receivable account ccid is requested for BSV1 => BSV2
307 -- x_reciprocal_ccid will contain the payable account for
308 -- BSV2 => BSV1
309 --
310 -- Version : Current version 1.0
311 -- Previous version 1.0
312 -- Initial version 1.0
313 ------------------------------------------------------------------------------*/
314 PROCEDURE get_intercompany_account (p_api_version IN NUMBER,
315 p_init_msg_list IN VARCHAR2,
316 p_ledger_id IN NUMBER,
317 p_from_le IN NUMBER,
318 p_from_bsv IN VARCHAR2,
319 p_to_ledger_id IN NUMBER,
320 p_to_le IN NUMBER,
321 p_to_bsv IN VARCHAR2,
322 p_gl_date IN DATE,
323 p_acct_type IN VARCHAR2,
324 x_status IN OUT NOCOPY VARCHAR2,
325 x_msg_count IN OUT NOCOPY NUMBER,
326 x_msg_data IN OUT NOCOPY VARCHAR2,
327 x_ccid IN OUT NOCOPY NUMBER ,
328 x_reciprocal_ccid IN OUT NOCOPY NUMBER)
329 IS
330
331 CURSOR c_get_ccid (p_ledger_id NUMBER,
332 p_from_le_id NUMBER,
333 p_to_le_id NUMBER,
334 p_from_bsv VARCHAR2,
335 p_to_bsv VARCHAR2,
336 p_acct_type VARCHAR2,
337 p_gl_date DATE)
338 IS
339 SELECT NVL( (SELECT ccid
340 FROM fun_inter_accounts accts
341 WHERE accts.ledger_id = p_ledger_id
342 AND accts.from_le_id = p_from_le_id
343 AND accts.to_le_id = p_to_le_id
344 AND accts.trans_bsv = p_from_bsv
345 AND accts.tp_bsv = p_to_bsv
346 AND accts.type = p_acct_type
347 AND accts.default_flag = 'Y'
348 AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(accts.start_date, p_gl_date))
349 AND TRUNC(NVL(accts.end_date, p_gl_date)))),
350 NVL((SELECT ccid
351 FROM fun_inter_accounts accts
352 WHERE accts.ledger_id = p_ledger_id
353 AND accts.from_le_id = p_from_le_id
354 AND accts.to_le_id = p_to_le_id
355 AND accts.trans_bsv = p_from_bsv
356 AND accts.tp_bsv = 'OTHER1234567890123456789012345'
357 AND accts.type = p_acct_type
358 AND accts.default_flag = 'Y'
359 AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(accts.start_date, p_gl_date))
360 AND TRUNC(NVL(accts.end_date, p_gl_date)))),
361 NVL((SELECT ccid
362 FROM fun_inter_accounts accts
363 WHERE accts.ledger_id = p_ledger_id
364 AND accts.from_le_id = p_from_le_id
365 AND accts.to_le_id = p_to_le_id
366 AND accts.trans_bsv = 'OTHER1234567890123456789012345'
367 AND accts.tp_bsv = p_to_bsv
368 AND accts.type = p_acct_type
369 AND accts.default_flag = 'Y'
370 AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(accts.start_date, p_gl_date))
371 AND TRUNC(NVL(accts.end_date, p_gl_date)))),
372 NVL((SELECT ccid
373 FROM fun_inter_accounts accts
374 WHERE accts.ledger_id = p_ledger_id
375 AND accts.from_le_id = p_from_le_id
376 AND accts.to_le_id = p_to_le_id
377 AND accts.trans_bsv = 'OTHER1234567890123456789012345'
378 AND accts.tp_bsv = 'OTHER1234567890123456789012345'
379 AND accts.type = p_acct_type
380 AND accts.default_flag = 'Y'
381 AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(accts.start_date, p_gl_date))
382 AND TRUNC(NVL(accts.end_date, p_gl_date)))),
383 (SELECT ccid
384 FROM fun_inter_accounts accts
385 WHERE accts.ledger_id = p_ledger_id
386 AND accts.from_le_id = p_from_le_id
387 AND accts.to_le_id = -99
388 AND accts.type = p_acct_type
389 AND accts.default_flag = 'Y'
390 AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(accts.start_date, p_gl_date))
391 AND TRUNC(NVL(accts.end_date, p_gl_date)))))))) ccid
392 From Dual;
393
394 l_api_name CONSTANT VARCHAR2(30) := 'Get_Intercompany_Account';
395 l_api_version CONSTANT NUMBER := 1.0;
396 l_return_status VARCHAR2(1);
397 l_from_le_id NUMBER ;
398 l_to_le_id NUMBER;
399 l_recip_acct_type VARCHAR2(1);
400
401 l_setup_ccid NUMBER;
402 l_setup_recp_ccid NUMBER;
403 l_coa NUMBER;
404 l_dummy NUMBER;
405 l_ic_seg_num NUMBER;
406 l_bal_seg_num NUMBER;
407 l_recp_coa NUMBER;
408 l_recp_ic_seg_num NUMBER;
409 l_recp_bal_seg_num NUMBER;
410 l_insert_flag VARCHAR2(1) := 'N'; --8200511
411
412 BEGIN
413
414 -- variable p_validation_level is not used .
415 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
416
417 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
418 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.begin', 'begin');
419 END IF;
420
421 -- Standard call to check for call compatibility.
422 IF NOT FND_API.Compatible_API_Call ( l_api_version,
423 p_api_version,
424 l_api_name,
425 g_package_name )
426 THEN
427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
428 END IF;
429
430 -- Initialize message list if p_init_msg_list is set to TRUE.
431 IF FND_API.to_Boolean( p_init_msg_list )
432 THEN
433 FND_MSG_PUB.initialize;
434 END IF;
435
436 -- Initialize API return status to success
437 x_status := FND_API.G_RET_STS_SUCCESS;
438
439 IF p_from_le IS NULL OR p_to_le IS NULL
440 THEN
441 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTER_BSV_NOT_ASSIGNED');
442 FND_MSG_PUB.Add;
443 x_status := FND_API.G_RET_STS_ERROR;
444 END IF;
445
446 IF x_status = FND_API.G_RET_STS_SUCCESS
447 THEN
448 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
449 THEN
450 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
451 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.get_ccid',
452 'Fetching the ccid');
453 END IF;
454
455 -- Rules of precedence is to find matching records for the IC Accts using
456 -- 1) From LE, From BSV => To LE, To BSV
457 -- 2) From LE, From BSV => To LE
458 -- 3) From LE => To LE, To BSV
459 -- 4) From LE => To LE
460 -- 5) From LE => To All Others
461 OPEN c_get_ccid (p_ledger_id => p_ledger_id,
462 p_from_le_id => p_from_le,
463 p_to_le_id => p_to_le,
464 p_from_bsv => p_from_bsv,
465 p_to_bsv => p_to_bsv,
466 p_acct_type => p_acct_type,
467 p_gl_date => p_gl_date);
468 FETCH c_get_ccid INTO l_setup_ccid;
469 CLOSE c_get_ccid;
470
471 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
472 THEN
473 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
474 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.got_ccid',
475 'Fetched the ccid : ' || l_setup_ccid);
476 END IF;
477
478 -- Now get the reciprocal account
479
480 IF p_acct_type = 'R'
481 THEN
482 l_recip_acct_type := 'P';
483 ELSE
484 l_recip_acct_type := 'R';
485 END IF;
486
487 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
488 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
489 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.get_recip_ccid',
490 'Fetching the reciprocal ccid');
491 END IF;
492
493 OPEN c_get_ccid (p_ledger_id => p_to_ledger_id,
494 p_from_le_id => p_to_le,
495 p_to_le_id => p_from_le,
496 p_from_bsv => p_to_bsv,
497 p_to_bsv => p_from_bsv,
498 p_acct_type => l_recip_acct_type,
499 p_gl_date => p_gl_date);
500 FETCH c_get_ccid INTO l_setup_recp_ccid ;
501 CLOSE c_get_ccid;
502
503 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
504 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
505 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.got_recip_ccid',
506 'Fetched the reciprocal ccid : ' || l_setup_recp_ccid);
507 END IF;
508
509 -- Enhancement 7520196 Start
510 -- If the Legal entities belong to the same cahrt of accounts, then the intercompany
511 -- and balancing segment value of the intercompany accounts are switched with the
512 -- participating balancing segment values.
513
514 OPEN c_get_seg_num(p_ledger_id,
515 'GL_BALANCING');
516 FETCH c_get_seg_num INTO l_bal_seg_num,
517 l_coa;
518 CLOSE c_get_seg_num;
519
520 -- Next find out the segment numbers for the Intercompany segment
521 OPEN c_get_seg_num(p_ledger_id,
522 'GL_INTERCOMPANY');
523 FETCH c_get_seg_num INTO l_ic_seg_num,
524 l_dummy;
525 CLOSE c_get_seg_num;
526
527 OPEN c_get_seg_num(p_to_ledger_id,
528 'GL_BALANCING');
529 FETCH c_get_seg_num INTO l_recp_bal_seg_num,
530 l_recp_coa;
531 CLOSE c_get_seg_num;
532
533 -- Next find out the segment numbers for the Intercompany segment
534 OPEN c_get_seg_num(p_to_ledger_id,
535 'GL_INTERCOMPANY');
536 FETCH c_get_seg_num INTO l_recp_ic_seg_num,
537 l_dummy;
538 CLOSE c_get_seg_num;
539
540 IF l_coa = l_recp_coa
541 THEN
542 -- Now we need to generate the new accounts by replacing the intercompany
543 -- segments and the balancing segments
544 -- First find out the segment numbers for the Balancing segment
545
546 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
547 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
548 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.get_new_ccid',
549 'Generating new acc using ccid : '||l_setup_ccid ||
550 'Bal BSV :'||p_from_bsv||'IC BSV :' ||p_to_bsv);
551 END IF;
552
553 -- Call the procedure to generate the new account
554 -- For eg if from the setup table we get back account 99-00-4350-00-99
555 -- and if the from bsv is 01 and to bsv is 02, this api should return ccid
556 -- for account 01-00-4350-00-02
557 x_ccid := fun_bal_pkg.get_ccid (
558 ccid => l_setup_ccid,
559 chart_of_accounts_id => l_coa,
560 bal_seg_val => p_from_bsv,
561 intercompany_seg_val => p_to_bsv,
562 bal_seg_column_number => l_bal_seg_num,
563 intercompany_column_number => l_ic_seg_num,
564 gl_date => p_gl_date);
565 --bug: 8200511
566 IF ( x_ccid <> l_setup_ccid) THEN
567 BEGIN
568
569 SELECT 'Y' INTO l_insert_flag
570 FROM FUN_INTER_ACCOUNTS_V
571 WHERE FROM_LE_ID = p_from_le
572 AND LEDGER_ID = p_ledger_id
573 AND TO_LE_ID = p_to_le
574 AND CCID = x_ccid
575 AND TYPE = p_acct_type
576 AND TRANS_BSV = p_from_bsv
577 AND TP_BSV = p_to_bsv;
578 EXCEPTION
579 WHEN NO_DATA_FOUND then
580 l_insert_flag := 'N';
581 WHEN OTHERS THEN
582 l_insert_flag := 'Y';
583 END;
584 IF (l_insert_flag = 'N') THEN
585 INSERT INTO FUN_INTER_ACCOUNTS_ADDL(FROM_LE_ID
586 , LEDGER_ID
587 , TO_LE_ID
588 , CCID
589 , TYPE
590 , START_DATE
591 , DEFAULT_FLAG
592 , OBJECT_VERSION_NUMBER
593 , CREATED_BY
594 , CREATION_DATE
595 , LAST_UPDATED_BY
596 , LAST_UPDATE_DATE
597 , LAST_UPDATE_LOGIN
598 , TRANS_BSV
599 , TP_BSV
600 )
601 VALUES(p_from_le
602 , p_ledger_id
603 , p_to_le
604 , x_ccid
605 , p_acct_type
606 , SYSDATE
607 , NULL
608 , 1
609 , FND_GLOBAL.USER_ID
610 , SYSDATE
611 , FND_GLOBAL.USER_ID
612 , SYSDATE
613 , fnd_global.login_id
614 , p_from_bsv
615 , p_to_bsv
616 );
617 END IF;
618 END IF;
619
620 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
621 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
622 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.get_new_recp_ccid',
623 'Generating new reciprocal acc using ccid : '||l_setup_recp_ccid ||
624 'Bal BSV :'||p_to_bsv||'IC BSV :' ||p_from_bsv);
625 END IF;
626
627 -- Call the procedure to generate the new account
628 -- For eg if from the setup table we get back account 99-00-5670-00-99
629 -- and if the from bsv is 01 and to bsv is 02, this api should return ccid
630 -- for account 02-00-5670-00-01
631 x_reciprocal_ccid := fun_bal_pkg.get_ccid (
632 ccid => l_setup_recp_ccid,
633 chart_of_accounts_id => l_recp_coa,
634 bal_seg_val => p_to_bsv,
635 intercompany_seg_val => p_from_bsv,
636 bal_seg_column_number => l_recp_bal_seg_num,
637 intercompany_column_number => l_recp_ic_seg_num,
638 gl_date => p_gl_date);
639 --bug: 8200511
640 IF ( x_reciprocal_ccid <> l_setup_ccid) THEN
641 BEGIN
642 SELECT 'Y' INTO l_insert_flag
643 FROM FUN_INTER_ACCOUNTS_V
644 WHERE FROM_LE_ID = p_to_le
645 AND LEDGER_ID = p_to_ledger_id
646 AND TO_LE_ID = p_from_le
647 AND CCID = x_reciprocal_ccid
648 AND TYPE = l_recip_acct_type
649 AND TRANS_BSV = p_to_bsv
650 AND TP_BSV = p_from_bsv;
651 EXCEPTION
652 WHEN NO_DATA_FOUND then
653 l_insert_flag := 'N';
654 WHEN OTHERS THEN
655 l_insert_flag := 'Y';
656 END;
657 IF (l_insert_flag = 'N') THEN
658 INSERT INTO FUN_INTER_ACCOUNTS_ADDL(FROM_LE_ID
659 , LEDGER_ID
660 , TO_LE_ID
661 , CCID
662 , TYPE
663 , START_DATE
664 , DEFAULT_FLAG
665 , OBJECT_VERSION_NUMBER
666 , CREATED_BY
667 , CREATION_DATE
668 , LAST_UPDATED_BY
669 , LAST_UPDATE_DATE
670 , LAST_UPDATE_LOGIN
671 , TRANS_BSV
672 , TP_BSV
673 )
674 VALUES(p_to_le
675 , p_to_ledger_id
676 , p_from_le
677 , x_reciprocal_ccid
678 , l_recip_acct_type
679 , SYSDATE
680 , NULL
681 , 1
682 , FND_GLOBAL.USER_ID
683 , SYSDATE
684 , FND_GLOBAL.USER_ID
685 , SYSDATE
686 , fnd_global.login_id
687 , p_to_bsv
688 , p_from_bsv
689 );
690 END IF;
691 END IF;
692
693 ELSE
694 -- If the Legal Entities are from different Ledgers, then the intercompany
695 -- and balancing segment values are not switched.
696
697 x_ccid := l_setup_ccid;
698 x_reciprocal_ccid := l_setup_recp_ccid;
699 END IF;
700
701 -- Enhancement 7520196 End
702
703 END IF;
704
705 IF Nvl(x_ccid,0) <= 0
706 THEN
707 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTRA_CC_NOT_CREATED');
708 FND_MSG_PUB.Add;
709 x_status := FND_API.G_RET_STS_ERROR;
710 END IF;
711
712 -- Standard call to get message count and if count is 1, get message info.
713 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
714 p_data => x_msg_data);
715
716 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
717 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.end', 'end');
718 END IF;
719
720 EXCEPTION
721 WHEN FND_API.G_EXC_ERROR
722 THEN
723 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
724 THEN
725 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
726 'fun.plsql.fun_bal_pkg.get_intercompany_account.error',
727 SUBSTR(SQLERRM,1, 4000));
728 END IF;
729
730 x_status := FND_API.G_RET_STS_ERROR ;
731 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
732 p_data => x_msg_data);
733
734 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
735 THEN
736 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
737 THEN
738 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
739 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.unexpected_error_norm',
740 SUBSTR(SQLCODE ||' : ' || SQLERRM,1, 4000));
741 END IF;
742
743 x_status := FND_API.G_RET_STS_UNEXP_ERROR ;
744 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
745 p_data => x_msg_data);
746
747 WHEN OTHERS
748 THEN
749 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
750 THEN
751 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
752 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.unexpected_error_others',
753 SUBSTR(SQLCODE ||' : ' || SQLERRM,1, 4000));
754 END IF;
755
756 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
757 THEN
758 FND_MSG_PUB.Add_Exc_Msg(g_package_name, l_api_name);
759 END IF;
760
761 x_status := FND_API.G_RET_STS_UNEXP_ERROR ;
762 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
763 p_data => x_msg_data);
764
765 END get_intercompany_account;
766
767 /* ----------------------------------------------------------------------------
768 -- API name : FUN_BAL_UTILS_GRP.get_intracompany_account
769 -- Type : Group
770 -- Pre-reqs : None.
771 -- Function : Given a transacting and trading Balancing segment value, the
772 -- the procedure returns the intracompany credit and debit
773 -- account
774 -- Parameters :
775 -- IN : p_api_version IN NUMBER Required
776 -- p_init_msg_list IN VARCHAR2 Optional
777 -- p_ledger_id IN NUMBER Required
778 -- p_from_le IN NUMBER Optional
779 -- p_source IN VARCHAR2 Optional
780 -- If not provided, source of 'Other' will be used to derive
781 -- the account
782 -- p_category IN VARCHAR2 Optional
783 -- If not provided, category of 'Other' will be used to derive
784 -- the account
785 -- p_from_bsv IN VARCHAR2 Required
786 -- p_to_bsv IN VARCHAR2 Required
787 -- p_gl_date IN DATE Required
788 -- p_acct_type IN VARCHAR2 Required
789 -- Account type would be 'D'ebit or 'C'redit
790 --
791 -- OUT : x_status VARCHAR2
792 -- x_msg_count NUMBER
793 -- x_msg_data VARCHAR2
794 -- x_ccid VARCHAR2 CCID requested
795 -- x_reciprocal_ccid VARCHAR2 Reciprocal CCID
796 -- Eg. If debit account ccid is requested for BSV1 => BSV2
797 -- x_reciprocal_ccid will contain the credit account for
798 -- BSV2 => BSV1
799 --
800 -- Version : Current version 1.0
801 -- Previous version 1.0
802 -- Initial version 1.0
803 ------------------------------------------------------------------------------*/
804 PROCEDURE get_intracompany_account (p_api_version IN NUMBER,
805 p_init_msg_list IN VARCHAR2,
806 p_ledger_id IN NUMBER,
807 p_from_le IN NUMBER,
808 p_source IN VARCHAR2,
809 p_category IN VARCHAR2,
810 p_dr_bsv IN VARCHAR2,
811 p_cr_bsv IN VARCHAR2,
812 p_gl_date IN DATE,
813 p_acct_type IN VARCHAR2,
814 x_status IN OUT NOCOPY VARCHAR2,
815 x_msg_count IN OUT NOCOPY NUMBER,
816 x_msg_data IN OUT NOCOPY VARCHAR2,
817 x_ccid IN OUT NOCOPY NUMBER ,
818 x_reciprocal_ccid IN OUT NOCOPY NUMBER)
819 IS
820
821 CURSOR c_get_template (p_ledger_id NUMBER,
822 p_le_id NUMBER,
823 p_source_name VARCHAR2,
824 p_category_name VARCHAR2)
825 IS
826 SELECT NVL((SELECT opts.template_id
827 FROM fun_balance_options opts
828 WHERE opts.ledger_id = p_ledger_id
829 AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
830 AND opts.je_source_name = p_source_name
831 AND opts.je_category_name = p_category_name
832 AND opts.status_flag = 'Y'),
833 NVL((SELECT opts.template_id
834 FROM fun_balance_options opts
835 WHERE opts.ledger_id = p_ledger_id
836 AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
837 AND opts.je_source_name = p_source_name
838 AND opts.je_category_name = 'Other'
839 AND opts.status_flag = 'Y'),
840 NVL((SELECT opts.template_id
841 FROM fun_balance_options opts
842 WHERE opts.ledger_id = p_ledger_id
843 AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
844 AND opts.je_source_name = 'Other'
845 AND opts.je_category_name = p_category_name
846 AND opts.status_flag = 'Y'),
847 (SELECT opts.template_id
848 FROM fun_balance_options opts
849 WHERE opts.ledger_id = p_ledger_id
850 AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
851 AND opts.je_source_name = 'Other'
852 AND opts.je_category_name = 'Other'
853 AND opts.status_flag = 'Y')))) template_id
854 From Dual;
855
856 CURSOR c_get_ccid (p_template_id NUMBER,
857 p_dr_bsv VARCHAR2,
858 p_cr_bsv VARCHAR2,
859 p_acct_type VARCHAR2)
860 IS
861 SELECT NVL((SELECT DECODE (p_acct_type, 'D', accts.dr_ccid, 'C', cr_ccid)
862 FROM fun_balance_accounts accts
863 WHERE accts.template_id = p_template_id
864 AND accts.dr_bsv = p_dr_bsv
865 AND accts.cr_bsv = p_cr_bsv),
866 NVL((SELECT DECODE (p_acct_type, 'D', accts.dr_ccid, 'C', cr_ccid)
867 FROM fun_balance_accounts accts
868 WHERE accts.template_id = p_template_id
869 AND accts.dr_bsv = p_dr_bsv
870 AND accts.cr_bsv = 'OTHER1234567890123456789012345'),
871 NVL((SELECT DECODE (p_acct_type, 'D', accts.dr_ccid, 'C', cr_ccid)
872 FROM fun_balance_accounts accts
873 WHERE accts.template_id = p_template_id
874 AND accts.dr_bsv = 'OTHER1234567890123456789012345'
875 AND accts.cr_bsv = p_cr_bsv),
876 (SELECT DECODE (p_acct_type, 'D', accts.dr_ccid, 'C', cr_ccid)
877 FROM fun_balance_accounts accts
878 WHERE accts.template_id = p_template_id
879 AND accts.dr_bsv = 'OTHER1234567890123456789012345'
880 AND accts.cr_bsv = 'OTHER1234567890123456789012345')))) ccid
881 From Dual;
882
883 l_api_name CONSTANT VARCHAR2(30) := 'Get_Intracompany_Account';
884 l_api_version CONSTANT NUMBER := 1.0;
885 l_return_status VARCHAR2(1);
886 l_from_le_id NUMBER ;
887 l_recip_acct_type VARCHAR2(1);
888 l_template_id NUMBER;
889 l_source gl_je_sources.je_source_name%TYPE;
890 l_category gl_je_categories.je_category_name%TYPE;
891
892 l_setup_ccid NUMBER;
893 l_setup_recp_ccid NUMBER;
894 l_coa NUMBER;
895 l_dummy NUMBER;
896 l_ic_seg_num NUMBER;
897 l_bal_seg_num NUMBER;
898 -- Bug: 7368523
899 l_dr_bsv FUN_BALANCE_ACCOUNTS.DR_BSV%TYPE;
900 l_cr_bsv FUN_BALANCE_ACCOUNTS.CR_BSV%TYPE;
901 BEGIN
902
903 -- Bug 7193385 Start
904 IF (p_acct_type = 'C')
905 THEN
906 l_dr_bsv := p_cr_bsv;
907 l_cr_bsv := p_dr_bsv;
908 ELSE
909 l_dr_bsv := p_dr_bsv;
910 l_cr_bsv := p_cr_bsv;
911 END IF;
912 -- Bug 7193385 End
913
914 -- variable p_validation_level is not used .
915 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
916
917 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
918 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_utils_grp.Get_Intracompany_Account.begin', 'begin');
919 END IF;
920
921 -- Standard call to check for call compatibility.
922 IF NOT FND_API.Compatible_API_Call ( l_api_version,
923 p_api_version,
924 l_api_name,
925 g_package_name )
926 THEN
927 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
928 END IF;
929
930 -- Initialize message list if p_init_msg_list is set to TRUE.
931 IF FND_API.to_Boolean( p_init_msg_list )
932 THEN
933 FND_MSG_PUB.initialize;
934 END IF;
935
936 -- Initialize API return status to success
937 x_status := FND_API.G_RET_STS_SUCCESS;
938
939 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
940 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
941 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.get_template',
942 'Fetching the template id');
943
944 END IF;
945
946 l_source := Nvl(p_source, 'Other');
947 l_category := Nvl(p_category, 'Other');
948
949 -- Rules of precedence to find template id. Look for ..
950 -- 1) Source , Category
951 -- 2) Source , 'Other'
952 -- 3) 'Other', Category
953 -- 4) 'Other', 'Other'
954
955 OPEN c_get_template (p_ledger_id => p_ledger_id,
956 p_le_id => p_from_le,
957 p_source_name => l_source,
958 p_category_name => l_category);
959 FETCH c_get_template INTO l_template_id;
960 CLOSE c_get_template;
961
962 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
963 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
964 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.got_template',
965 'Template id is '||l_template_id );
966 END IF;
967
968 IF l_template_id IS NULL
969 THEN
970 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTRA_RULE_NOT_ASSIGNED');
971 FND_MSG_PUB.Add;
972 x_status := FND_API.G_RET_STS_ERROR;
973 END IF;
974
975 IF x_status = FND_API.G_RET_STS_SUCCESS
976 THEN
977 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
978 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
979 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.get_ccid',
980 'Fetching the ccid');
981 END IF;
982
983 -- Now get the debit or credit account.
984 -- Rules of precedence to find ccid. Look for ..
985 -- 1) Dr BSV , Cr BSV
986 -- 2) Dr BSV , 'Other'
987 -- 3) 'Other', Cr BSV
988 -- 4) 'Other', 'Other'
989 OPEN c_get_ccid (p_template_id => l_template_id,
990 p_dr_bsv => l_dr_bsv,
991 p_cr_bsv => l_cr_bsv,
992 p_acct_type => p_acct_type);
993 FETCH c_get_ccid INTO l_setup_ccid;
994 CLOSE c_get_ccid ;
995
996 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
997 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
998 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.got_ccid',
999 'Fetched the ccid : '|| l_setup_ccid);
1000 END IF;
1001
1002 -- Now get the reciprocal account
1003 IF p_acct_type = 'D'
1004 THEN
1005 l_recip_acct_type := 'C';
1006 ELSE
1007 l_recip_acct_type := 'D';
1008 END IF;
1009
1010 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1011 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1012 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.get_recip_ccid',
1013 'Fetching the reciprocal ccid');
1014 END IF;
1015
1016 -- Bug 7409706: For Intracompany Rule
1017 -- Debit BSV Credit BSV Debit Accoun Credit Account
1018 -- 01 02 01.XXX.02 02.XXX.01
1019 -- 02 01 02.YYY.01 01.YYY.02
1020 -- Id ccid = 01.XXX.02, then reciprocal_ccid = 02.XXX.01 and not 02.YYY.01
1021 OPEN c_get_ccid (p_template_id => l_template_id,
1022 p_dr_bsv => l_dr_bsv,
1023 p_cr_bsv => l_cr_bsv,
1024 p_acct_type => l_recip_acct_type);
1025 FETCH c_get_ccid INTO l_setup_recp_ccid;
1026 CLOSE c_get_ccid ;
1027
1028 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1029 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1030 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.got_recip_ccid',
1031 'Fetched the reciprocal ccid : ' || l_setup_recp_ccid);
1032 END IF;
1033
1034 -- Now we need to generate the new accounts by replacing the intercompany
1035 -- segments and the balancing segments
1036 -- First find out the segment numbers for the Balancing segment
1037 OPEN c_get_seg_num(p_ledger_id,
1038 'GL_BALANCING');
1039 FETCH c_get_seg_num INTO l_bal_seg_num,
1040 l_coa;
1041 CLOSE c_get_seg_num;
1042
1043 -- Next find out the segment numbers for the Intercompany segment
1044 OPEN c_get_seg_num(p_ledger_id,
1045 'GL_INTERCOMPANY');
1046 FETCH c_get_seg_num INTO l_ic_seg_num,
1047 l_dummy;
1048 CLOSE c_get_seg_num;
1049
1050 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1051 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1052 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.get_new_ccid',
1053 'Generating new acc using ccid : '||l_setup_ccid ||
1054 'Bal BSV :'||p_dr_bsv||'IC BSV :' ||p_cr_bsv);
1055 END IF;
1056
1057 -- Call the procedure to generate the new account
1058 -- For eg if from the setup table we get back account 99-00-4350-00-99
1059 -- and if the dr bsv is 01 and cr bsv is 02, this api should return ccid
1060 -- for account 01-00-4350-00-02
1061 x_ccid := fun_bal_pkg.get_ccid (
1062 ccid => l_setup_ccid,
1063 chart_of_accounts_id => l_coa,
1064 bal_seg_val => p_dr_bsv,
1065 intercompany_seg_val => p_cr_bsv,
1066 bal_seg_column_number => l_bal_seg_num,
1067 intercompany_column_number => l_ic_seg_num,
1068 gl_date => p_gl_date);
1069
1070 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1071 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1072 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.get_new_recp_ccid',
1073 'Generating new reciprocal acc using ccid : '||l_setup_recp_ccid ||
1074 'Bal BSV :'||p_cr_bsv||'IC BSV :' ||p_dr_bsv);
1075 END IF;
1076
1077 -- Call the procedure to generate the new account
1078 -- For eg if from the setup table we get back account 99-00-5670-00-99
1079 -- and if the dr bsv is 01 and cr bsv is 02, this api should return ccid
1080 -- for account 02-00-5670-00-01
1081 x_reciprocal_ccid := fun_bal_pkg.get_ccid (
1082 ccid => l_setup_recp_ccid,
1083 chart_of_accounts_id => l_coa,
1084 bal_seg_val => p_cr_bsv,
1085 intercompany_seg_val => p_dr_bsv,
1086 bal_seg_column_number => l_bal_seg_num,
1087 intercompany_column_number => l_ic_seg_num,
1088 gl_date => p_gl_date);
1089
1090 END IF;
1091
1092 IF Nvl(x_ccid,0) <= 0
1093 THEN
1094 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTRA_CC_NOT_CREATED');
1095 FND_MSG_PUB.Add;
1096 x_status := FND_API.G_RET_STS_ERROR;
1097 END IF;
1098
1099 -- Standard call to get message count and if count is 1, get message info.
1100 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1101 p_data => x_msg_data);
1102
1103 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1104 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_utils_grp.Get_Intracompany_Account.end', 'end');
1105 END IF;
1106
1107 EXCEPTION
1108 WHEN FND_API.G_EXC_ERROR
1109 THEN
1110 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
1111 THEN
1112 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1113 'fun.plsql.fun_bal_pkg.Get_Intracompany_Account.error',
1114 SUBSTR(SQLERRM,1, 4000));
1115 END IF;
1116
1117 x_status := FND_API.G_RET_STS_ERROR ;
1118 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1119 p_data => x_msg_data);
1120
1121 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1122 THEN
1123 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
1124 THEN
1125 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1126 'fun.plsql.fun_bal_utils_grp.Get_Intracompany_Account.unexpected_error_norm',
1127 SUBSTR(SQLCODE ||' : ' || SQLERRM,1, 4000));
1128 END IF;
1129
1130 x_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1131 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1132 p_data => x_msg_data);
1133
1134 WHEN OTHERS
1135 THEN
1136 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
1137 THEN
1138 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1139 'fun.plsql.fun_bal_utils_grp.Get_Intracompany_Account.unexpected_error_others',
1140 SUBSTR(SQLCODE ||' : ' || SQLERRM,1, 4000));
1141 END IF;
1142
1143 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1144 THEN
1145 FND_MSG_PUB.Add_Exc_Msg(g_package_name, l_api_name);
1146 END IF;
1147
1148 x_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1149 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1150 p_data => x_msg_data);
1151
1152 END get_intracompany_account;
1153
1154 END FUN_BAL_UTILS_GRP;