[Home] [Help]
PACKAGE BODY: APPS.FUN_BAL_UTILS_GRP
Source
1 PACKAGE BODY FUN_BAL_UTILS_GRP AS
2 /* $Header: fungbalutilb.pls 120.20.12020000.2 2012/07/26 23:26:10 shnaraya ship $ */
3
4
5 g_debug_level NUMBER;
6 g_package_name VARCHAR2(30) := 'FUN_BAL_UTILS_GRP';
7
8 CURSOR c_get_le_id (p_ledger_id NUMBER,
9 p_bsv VARCHAR2,
10 p_gl_date DATE)
11 IS
12 SELECT vals.legal_entity_id
13 FROM gl_ledger_le_bsv_specific_v vals
14 WHERE vals.segment_value = p_bsv
15 AND vals.ledger_id = p_ledger_id
16 AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(vals.start_date, p_gl_date)) AND
17 TRUNC(NVL(vals.end_date, p_gl_date)));
18
19 CURSOR c_get_seg_num(p_ledger_id NUMBER,
20 p_segment_type VARCHAR2)
21 IS
22 SELECT fun_bal_pkg.get_segment_index(ledgers.chart_of_accounts_id,
23 p_segment_type),
24 ledgers.chart_of_accounts_id
25 FROM gl_ledgers ledgers
26 WHERE ledgers.ledger_id = p_ledger_id;
27
28 /* ----------------------------------------------------------------------------
29 -- API name : FUN_BAL_UTILS_GRP.get_inter_intra_account
30 -- Type : Group
31 -- Pre-reqs : None.
32 -- Function : Given a transacting and trading Balancing segment value, the
33 -- the procedure determines what type of account is required
34 -- ie inter or intra company accounts and returns the same
35 -- Parameters :
36 -- IN :
37 -- p_api_version IN NUMBER Required
38 -- p_init_msg_list IN VARCHAR2 Optional
39 -- p_ledger_id IN NUMBER Required
40 -- p_from_bsv IN VARCHAR2 Required
41 -- p_to_bsv IN VARCHAR2 Required
42 -- p_source IN VARCHAR2 Optional
43 -- p_category IN VARCHAR2 Optional
44 -- p_gl_date IN DATE Required
45 -- p_acct_type IN VARCHAR2 Required
46 -- Account type would be 'D'ebit(Receivables)
47 -- Or 'C'redit' (Payables)
48 --
49 -- OUT :
50 -- x_status VARCHAR2
51 -- x_msg_count NUMBER
52 -- x_msg_data VARCHAR2
53 -- x_ccid VARCHAR2 CCID requested
54 -- x_reciprocal_ccid VARCHAR2 Reciprocal CCID
55 -- Eg. If receivable account ccid is requested for BSV1 => BSV2
56 -- x_reciprocal_ccid will contain the payable account for
57 -- BSV2 => BSV1
58 --
59 -- Version : Current version 1.0
60 -- Previous version 1.0
61 -- Initial version 1.0
62 ------------------------------------------------------------------------------*/
63 PROCEDURE get_inter_intra_account (p_api_version IN NUMBER,
64 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
65 p_ledger_id IN NUMBER,
66 p_to_ledger_id IN NUMBER,
67 p_from_bsv IN VARCHAR2,
68 p_to_bsv IN VARCHAR2,
69 p_source IN VARCHAR2,
70 p_category IN VARCHAR2,
71 p_gl_date IN DATE,
72 p_acct_type IN VARCHAR2,
73 x_status IN OUT NOCOPY VARCHAR2,
74 x_msg_count IN OUT NOCOPY NUMBER,
75 x_msg_data IN OUT NOCOPY VARCHAR2,
76 x_ccid IN OUT NOCOPY NUMBER ,
77 x_reciprocal_ccid IN OUT NOCOPY NUMBER)
78 IS
79
80
81 l_api_name CONSTANT VARCHAR2(30) := 'Get_Inter_Intra_Account';
82 l_api_version CONSTANT NUMBER := 1.0;
83 l_return_status VARCHAR2(1);
84 l_from_le_id NUMBER ;
85 l_to_le_id NUMBER;
86 l_intra_txn BOOLEAN := FALSE;
87 l_inter_txn BOOLEAN := FALSE;
88 l_acct_type VARCHAR2(1);
89
90 BEGIN
91
92 -- variable p_validation_level is not used .
93 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
94
95 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
96 THEN
97 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
98 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.begin',
99 'begin');
100 END IF;
101
102 -- Standard call to check for call compatibility.
103 IF NOT FND_API.Compatible_API_Call ( l_api_version,
104 p_api_version,
105 l_api_name,
106 g_package_name )
107 THEN
108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
109 END IF;
110
111 -- Initialize message list if p_init_msg_list is set to TRUE.
112 IF FND_API.to_Boolean( p_init_msg_list )
113 THEN
114 FND_MSG_PUB.initialize;
115 END IF;
116
117 -- Initialize API return status to success
118 x_status := FND_API.G_RET_STS_SUCCESS;
119
120 OPEN c_get_le_id (p_ledger_id => p_ledger_id,
121 p_bsv => p_from_bsv,
122 p_gl_date => p_gl_date);
123 FETCH c_get_le_id INTO l_from_le_id;
124 CLOSE c_get_le_id;
125
126 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
127 THEN
128 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
129 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.frm_le',
130 'Fetched From LE Id : ' || l_from_le_id);
131 END IF;
132
133
134 OPEN c_get_le_id (p_ledger_id => p_to_ledger_id,
135 p_bsv => p_to_bsv,
136 p_gl_date => p_gl_date);
137 FETCH c_get_le_id INTO l_to_le_id;
138 CLOSE c_get_le_id;
139
140 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
141 THEN
142 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
143 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.to_le',
144 'Fetching To LE Id : ' ||l_to_le_id);
145 END IF;
146
147 IF Nvl(l_from_le_id,-99) = Nvl(l_to_le_id, -99)
148 THEN
149 -- This is an intracompany transaction
150 l_intra_txn := TRUE;
151 END IF;
152
153 IF l_from_le_id <> l_to_le_id
154 THEN
155 -- This is an intercompany transaction
156 l_inter_txn := TRUE;
157 END IF;
158
159 IF NOT(l_inter_txn) AND NOT(l_intra_txn)
160 THEN
161 -- This is an error situation
162 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTER_BSV_NOT_ASSIGNED');
163 FND_MSG_PUB.Add;
164 x_status := FND_API.G_RET_STS_ERROR;
165 END IF;
166
167 IF l_inter_txn
168 THEN
169 IF p_acct_type = 'D'
170 THEN
171 l_acct_type := 'R';
172 ELSE
173 l_acct_type := 'P';
174 END IF;
175
176 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
177 THEN
178 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
179 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.get',
180 'Fetching intercompany account ');
181 END IF;
182 --ER: 8588074.
183 get_intercompany_account (p_api_version => p_api_version,
184 p_init_msg_list => p_init_msg_list,
185 p_ledger_id => p_ledger_id,
186 p_from_le => l_from_le_id,
187 p_source => p_source,
188 p_category => p_category,
189 p_from_bsv => p_from_bsv,
190 p_to_ledger_id => p_to_ledger_id,
191 p_to_le => l_to_le_id,
192 p_to_bsv => p_to_bsv,
193 p_gl_date => p_gl_date,
194 p_acct_type => l_acct_type,
195 x_status => x_status,
196 x_msg_count => x_msg_count,
197 x_msg_data => x_msg_data,
198 x_ccid => x_ccid,
199 x_reciprocal_ccid => x_reciprocal_ccid);
200
201 END IF;
202
203 IF l_intra_txn
204 THEN
205
206 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
207 THEN
208 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
209 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.get',
210 'Fetching the intracompany ccid');
211 END IF;
212
213 get_intracompany_account (p_api_version => p_api_version,
214 p_init_msg_list => p_init_msg_list,
215 p_ledger_id => p_ledger_id,
216 p_from_le => l_from_le_id,
217 p_source => p_source,
218 p_category => p_category,
219 p_dr_bsv => p_from_bsv,
220 p_cr_bsv => p_to_bsv,
221 p_gl_date => p_gl_date,
222 p_acct_type => p_acct_type,
223 x_status => x_status,
224 x_msg_count => x_msg_count,
225 x_msg_data => x_msg_data,
226 x_ccid => x_ccid,
227 x_reciprocal_ccid => x_reciprocal_ccid);
228
229 END IF;
230
231 -- Standard call to get message count and if count is 1, get message info.
232 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
233 p_data => x_msg_data);
234
235 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
236 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.end', 'end');
237 END IF;
238
239 EXCEPTION
240 WHEN FND_API.G_EXC_ERROR
241 THEN
242 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
243 THEN
244 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
245 'fun.plsql.fun_bal_pkg.get_inter_intra_account.error',
246 SUBSTR(SQLERRM,1, 4000));
247 END IF;
248
249 x_status := FND_API.G_RET_STS_ERROR ;
250 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
251 p_data => x_msg_data);
252
253 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
254 THEN
255 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
256 THEN
257 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
258 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.unexpected_error_norm',
259 SUBSTR(SQLCODE ||' : ' || SQLERRM,1, 4000));
260 END IF;
261
262 x_status := FND_API.G_RET_STS_UNEXP_ERROR ;
263 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
264 p_data => x_msg_data);
265
266 WHEN OTHERS
267 THEN
268 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
269 THEN
270 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
271 'fun.plsql.fun_bal_utils_grp.get_inter_intra_account.unexpected_error_others',
272 SUBSTR(SQLCODE ||' : ' || SQLERRM,1, 4000));
273 END IF;
274
275 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
276 THEN
277 FND_MSG_PUB.Add_Exc_Msg(g_package_name, l_api_name);
278 END IF;
279
280 x_status := FND_API.G_RET_STS_UNEXP_ERROR ;
281 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
282 p_data => x_msg_data);
283
284 END get_inter_intra_account;
285
286 /* ----------------------------------------------------------------------------
287 -- API name : FUN_BAL_UTILS_GRP.get_intercompany_account
288 -- Type : Group
289 -- Pre-reqs : None.
290 -- Function : Given a transacting and trading Balancing segment value,
291 -- the procedure returns the intercompany receivables and
292 -- payables account
293 -- Parameters :
294 -- IN : p_api_version IN NUMBER Required
295 -- p_init_msg_list IN VARCHAR2 Optional
296 -- p_ledger_id IN NUMBER Required
297 -- p_from_le IN NUMBER Required
298 -- ER: 8588074
299 -- p_source IN VARCHAR2 Required
300 -- p_category IN VARCHAR2 Required
301 -- p_from_bsv IN VARCHAR2 Required
302 -- p_to_le IN NUMBER Required
303 -- p_to_bsv IN VARCHAR2 Required
304 -- p_gl_date IN DATE Required
305 -- p_acct_type IN VARCHAR2 Required
306 -- Account type would be 'R'eceivables or 'P'ayables
307 --
308 -- OUT : x_status VARCHAR2
309 -- x_msg_count NUMBER
310 -- x_msg_data VARCHAR2
311 -- x_ccid VARCHAR2 CCID requested
312 -- x_reciprocal_ccid VARCHAR2 Reciprocal CCID
313 -- Eg. If receivable account ccid is requested for BSV1 => BSV2
314 -- x_reciprocal_ccid will contain the payable account for
315 -- BSV2 => BSV1
316 --
317 -- Version : Current version 1.0
318 -- Previous version 1.0
319 -- Initial version 1.0
320 ------------------------------------------------------------------------------*/
321
322
323 PROCEDURE get_intercompany_account (p_api_version IN NUMBER,
324 p_init_msg_list IN VARCHAR2,
325 p_ledger_id IN NUMBER,
326 p_from_le IN NUMBER,
327 p_source IN VARCHAR2,
328 p_category IN VARCHAR2,
329 p_from_bsv IN VARCHAR2,
330 p_to_ledger_id IN NUMBER,
331 p_to_le IN NUMBER,
332 p_to_bsv IN VARCHAR2,
333 p_gl_date IN DATE,
334 p_acct_type IN VARCHAR2,
335 x_status IN OUT NOCOPY VARCHAR2,
336 x_msg_count IN OUT NOCOPY NUMBER,
337 x_msg_data IN OUT NOCOPY VARCHAR2,
338 x_ccid IN OUT NOCOPY NUMBER ,
339 x_reciprocal_ccid IN OUT NOCOPY NUMBER)
340 IS
341 --Bug: 9337184
342 CURSOR c_get_ccid (p_ledger_id NUMBER,
343 p_from_le_id NUMBER,
344 p_to_le_id NUMBER,
345 p_from_bsv VARCHAR2,
346 p_to_bsv VARCHAR2,
347 p_acct_type VARCHAR2,
348 p_gl_date DATE)
349 IS
350 SELECT 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 = p_to_bsv
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 = p_from_bsv
367 AND accts.tp_bsv = 'OTHER1234567890123456789012345'
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 = p_to_bsv
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 NVL((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 = p_to_le_id
388 AND accts.trans_bsv = 'OTHER1234567890123456789012345'
389 AND accts.tp_bsv = 'OTHER1234567890123456789012345'
390 AND accts.type = p_acct_type
391 AND accts.default_flag = 'Y'
392 AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(accts.start_date, p_gl_date))
393 AND TRUNC(NVL(accts.end_date, p_gl_date)))),
394 NVL((SELECT ccid
395 FROM fun_inter_accounts accts
396 WHERE accts.ledger_id = p_ledger_id
397 AND accts.from_le_id = p_from_le_id
398 AND accts.to_le_id = -99
399 AND accts.trans_bsv = p_from_bsv
400 AND accts.tp_bsv = 'OTHER1234567890123456789012345'
401 AND accts.type = p_acct_type
402 AND accts.default_flag = 'Y'
403 AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(accts.start_date, p_gl_date))
404 AND TRUNC(NVL(accts.end_date, p_gl_date)))),
405 (SELECT ccid
406 FROM fun_inter_accounts accts
407 WHERE accts.ledger_id = p_ledger_id
408 AND accts.from_le_id = p_from_le_id
409 AND accts.to_le_id = -99
410 AND accts.trans_bsv = 'OTHER1234567890123456789012345'
411 AND accts.tp_bsv = 'OTHER1234567890123456789012345'
412 AND accts.type = p_acct_type
413 AND accts.default_flag = 'Y'
414 AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(accts.start_date, p_gl_date))
415 AND TRUNC(NVL(accts.end_date, p_gl_date))))))))) ccid
416 From Dual;
417
418 -- ER: 8588074
419 CURSOR c_get_template (p_ledger_id NUMBER,
420 p_le_id NUMBER,
421 p_source_name VARCHAR2,
422 p_category_name VARCHAR2)
423 IS
424 SELECT NVL((SELECT opts.template_id
425 FROM fun_balance_options opts
426 WHERE opts.ledger_id = p_ledger_id
427 AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
428 AND opts.je_source_name = p_source_name
429 AND opts.je_category_name = p_category_name
430 AND opts.status_flag = 'Y'),
431 NVL((SELECT opts.template_id
432 FROM fun_balance_options opts
433 WHERE opts.ledger_id = p_ledger_id
434 AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
435 AND opts.je_source_name = p_source_name
436 AND opts.je_category_name = 'Other'
437 AND opts.status_flag = 'Y'),
438 NVL((SELECT opts.template_id
439 FROM fun_balance_options opts
440 WHERE opts.ledger_id = p_ledger_id
441 AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
442 AND opts.je_source_name = 'Other'
443 AND opts.je_category_name = p_category_name
444 AND opts.status_flag = 'Y'),
445 (SELECT opts.template_id
446 FROM fun_balance_options opts
447 WHERE opts.ledger_id = p_ledger_id
448 AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
449 AND opts.je_source_name = 'Other'
450 AND opts.je_category_name = 'Other'
451 AND opts.status_flag = 'Y')))) template_id
452 From Dual where fun_trx_pvt.get_inter_intra()='Y';
453
454 CURSOR c_get_intra_ccid (p_template_id NUMBER,
455 p_acct_type VARCHAR2)
456 IS
457 SELECT DECODE (p_acct_type, 'D', accts.dr_ccid, 'C', cr_ccid) ccid
458 FROM fun_balance_accounts accts
459 WHERE accts.template_id = p_template_id
460 AND accts.dr_bsv = 'OTHER1234567890123456789012345'
461 AND accts.cr_bsv = 'OTHER1234567890123456789012345' ;
462
463
464 l_api_name CONSTANT VARCHAR2(30) := 'Get_Intercompany_Account';
465 l_api_version CONSTANT NUMBER := 1.0;
466 l_return_status VARCHAR2(1);
467 l_from_le_id NUMBER ;
468 l_to_le_id NUMBER;
469 l_recip_acct_type VARCHAR2(1);
470
471 l_setup_ccid NUMBER;
472 l_setup_recp_ccid NUMBER;
473 l_coa NUMBER;
474 l_dummy NUMBER;
475 l_ic_seg_num NUMBER;
476 l_bal_seg_num NUMBER;
477 l_recp_coa NUMBER;
478 l_recp_ic_seg_num NUMBER;
479 l_recp_bal_seg_num NUMBER;
480 l_insert_flag VARCHAR2(1) := 'N'; --8200511
481 l_check_ccid NUMBER;
482 l_template_id NUMBER;
483 l_source gl_je_sources.je_source_name%TYPE;
484 l_category gl_je_categories.je_category_name%TYPE;
485 l_acct_type VARCHAR2(1);
486
487 l_from_le_name xle_entity_profiles.name%TYPE;
488 l_to_le_name xle_entity_profiles.name%TYPE;
489
490 BEGIN
491
492 l_check_ccid := 0;
493 -- variable p_validation_level is not used .
494 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
495
496 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
497 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.begin', 'begin');
498 END IF;
499
500 -- Standard call to check for call compatibility.
501 IF NOT FND_API.Compatible_API_Call ( l_api_version,
502 p_api_version,
503 l_api_name,
504 g_package_name )
505 THEN
506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
507 END IF;
508
509 -- Initialize message list if p_init_msg_list is set to TRUE.
510 IF FND_API.to_Boolean( p_init_msg_list )
511 THEN
512 FND_MSG_PUB.initialize;
513 END IF;
514
515 -- Initialize API return status to success
516 x_status := FND_API.G_RET_STS_SUCCESS;
517
518 IF p_from_le IS NULL OR p_to_le IS NULL
519 THEN
520 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTER_BSV_NOT_ASSIGNED');
521 FND_MSG_PUB.Add;
522 x_status := FND_API.G_RET_STS_ERROR;
523 END IF;
524
525 IF x_status = FND_API.G_RET_STS_SUCCESS
526 THEN
527 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
528 THEN
529 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
530 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.get_ccid',
531 'Fetching the ccid');
532 END IF;
533
534 -- Rules of precedence is to find matching records for the IC Accts using
535 -- 1) From LE, From BSV => To LE, To BSV
536 -- 2) From LE, From BSV => To LE
537 -- 3) From LE => To LE, To BSV
538 -- 4) From LE => To LE
539 -- 5) From LE => To All Others
540
541 OPEN c_get_ccid (p_ledger_id => p_ledger_id,
542 p_from_le_id => p_from_le,
543 p_to_le_id => p_to_le,
544 p_from_bsv => p_from_bsv,
545 p_to_bsv => p_to_bsv,
546 p_acct_type => p_acct_type,
547 p_gl_date => p_gl_date);
548 FETCH c_get_ccid INTO l_setup_ccid;
549 CLOSE c_get_ccid;
550
551 --ER: 8588074
552 IF l_setup_ccid is NULL THEN
553
554 IF p_acct_type = 'R'
555 THEN
556 l_acct_type := 'D';
557 ELSE
558 l_acct_type :='C';
559 END IF;
560 l_source := Nvl(p_source, 'Other');
561 l_category := Nvl(p_category, 'Other');
562
563 -- Rules of precedence to find template id. Look for ..
564 -- 1) Source , Category
565 -- 2) Source , 'Other'
566 -- 3) 'Other', Category
567 -- 4) 'Other', 'Other'
568
569 OPEN c_get_template (p_ledger_id => p_ledger_id,
570 p_le_id => p_from_le,
571 p_source_name => l_source,
572 p_category_name => l_category);
573 FETCH c_get_template INTO l_template_id;
574 CLOSE c_get_template;
575
576 IF l_template_id IS NOT NULL
577 THEN
578 -- Now get the debit or credit account.
579 -- Rules of precedence to find ccid. Look for ..
580 -- 1) 'Other', 'Other'
581 OPEN c_get_intra_ccid (p_template_id => l_template_id,
582 p_acct_type => l_acct_type);
583 FETCH c_get_intra_ccid INTO l_setup_ccid;
584 CLOSE c_get_intra_ccid ;
585 END IF;
586
587 END IF;
588 -- Bug: 13642672
589 IF Nvl(l_setup_ccid,0) <= 0
590 THEN
591 SELECT name
592 INTO l_from_le_name
593 FROM xle_firstparty_information_v
594 WHERE legal_entity_id = p_from_le;
595
596 SELECT name
597 INTO l_to_le_name
598 FROM xle_firstparty_information_v
599 WHERE legal_entity_id = p_to_le;
600
601 IF p_acct_type = 'R'
602 THEN
603 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTER_REC_NOT_ASSIGNED');
604 ELSE
605 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTER_PAY_NOT_ASSIGNED');
606 END IF;
607 FND_MESSAGE.SET_TOKEN('FROM_LE_ID', l_from_le_name);
608 FND_MESSAGE.SET_TOKEN('TO_LE_ID', l_to_le_name);
609
610 FND_MSG_PUB.Add;
611 x_status := FND_API.G_RET_STS_ERROR;
612 END IF;
613
614 IF (x_status = FND_API.G_RET_STS_SUCCESS) THEN
615
616 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
617 THEN
618 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
619 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.got_ccid',
620 'Fetched the ccid : ' || l_setup_ccid);
621 END IF;
622
623 -- Now get the reciprocal account
624
625 IF p_acct_type = 'R'
626 THEN
627 l_recip_acct_type := 'P';
628 l_acct_type := 'C';
629 ELSE
630 l_recip_acct_type := 'R';
631 l_acct_type :='D';
632 END IF;
633
634 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
635 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
636 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.get_recip_ccid',
637 'Fetching the reciprocal ccid');
638 END IF;
639
640 OPEN c_get_ccid (p_ledger_id => p_to_ledger_id,
641 p_from_le_id => p_to_le,
642 p_to_le_id => p_from_le,
643 p_from_bsv => p_to_bsv,
644 p_to_bsv => p_from_bsv,
645 p_acct_type => l_recip_acct_type,
646 p_gl_date => p_gl_date);
647 FETCH c_get_ccid INTO l_setup_recp_ccid ;
648 CLOSE c_get_ccid;
649 IF l_setup_recp_ccid is NULL THEN
650
651 --ER: 8588074
652 l_source := Nvl(p_source, 'Other');
653 l_category := Nvl(p_category, 'Other');
654
655 -- Rules of precedence to find template id. Look for ..
656 -- 1) Source , Category
657 -- 2) Source , 'Other'
658 -- 3) 'Other', Category
659 -- 4) 'Other', 'Other'
660
661 OPEN c_get_template (p_ledger_id => p_to_ledger_id,
662 p_le_id => p_to_le,
663 p_source_name => l_source,
664 p_category_name => l_category);
665 FETCH c_get_template INTO l_template_id;
666 CLOSE c_get_template;
667 IF l_template_id IS NOT NULL THEN
668 -- Now get the debit or credit account.
669 -- Rules of precedence to find ccid. Look for ..
670 -- 1) 'Other', 'Other'
671 OPEN c_get_intra_ccid (p_template_id => l_template_id,
672 p_acct_type => l_acct_type);
673 FETCH c_get_intra_ccid INTO l_setup_recp_ccid;
674 CLOSE c_get_intra_ccid ;
675
676
677 END IF;
678 END IF;
679
680 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
681 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
682 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.got_recip_ccid',
683 'Fetched the reciprocal ccid : ' || l_setup_recp_ccid);
684 END IF;
685
686 -- Enhancement 7520196 Start
687 -- If the Legal entities belong to the same cahrt of accounts, then the intercompany
688 -- and balancing segment value of the intercompany accounts are switched with the
689 -- participating balancing segment values.
690
691 OPEN c_get_seg_num(p_ledger_id,
692 'GL_BALANCING');
693 FETCH c_get_seg_num INTO l_bal_seg_num,
694 l_coa;
695 CLOSE c_get_seg_num;
696
697 -- Next find out the segment numbers for the Intercompany segment
698 OPEN c_get_seg_num(p_ledger_id,
699 'GL_INTERCOMPANY');
700 FETCH c_get_seg_num INTO l_ic_seg_num,
701 l_dummy;
702 CLOSE c_get_seg_num;
703
704 OPEN c_get_seg_num(p_to_ledger_id,
705 'GL_BALANCING');
706 FETCH c_get_seg_num INTO l_recp_bal_seg_num,
707 l_recp_coa;
708 CLOSE c_get_seg_num;
709
710 -- Next find out the segment numbers for the Intercompany segment
711 OPEN c_get_seg_num(p_to_ledger_id,
712 'GL_INTERCOMPANY');
713 FETCH c_get_seg_num INTO l_recp_ic_seg_num,
714 l_dummy;
715 CLOSE c_get_seg_num;
716
717 IF l_coa = l_recp_coa
718 THEN
719 -- Now we need to generate the new accounts by replacing the intercompany
720 -- segments and the balancing segments
721 -- First find out the segment numbers for the Balancing segment
722
723 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
724 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
725 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.get_new_ccid',
726 'Generating new acc using ccid : '||l_setup_ccid ||
727 'Bal BSV :'||p_from_bsv||'IC BSV :' ||p_to_bsv);
728 END IF;
729
730
731
732 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
733 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
734 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.get_new_ccid',
735 'Global ledger id :'||gl_global.context_ledger_id);
736 end if;
737
738
739
740 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
741 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
742 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.get_new_ccid',
743 'p_ledger_id:'||p_ledger_id||','||'p_to_ledger_id:'||p_to_ledger_id);
744 end if;
745
746
747 -- Call the procedure to generate the new account
748 -- For eg if from the setup table we get back account 99-00-4350-00-99
749 -- and if the from bsv is 01 and to bsv is 02, this api should return ccid
750 -- for account 01-00-4350-00-02
751
752 --Bug: 13590367
753
754
755
756
757 gl_global.set_aff_validation ('XX',null);
758
759 x_ccid := fun_bal_pkg.get_ccid (
760 ccid => l_setup_ccid,
761 chart_of_accounts_id => l_coa,
762 bal_seg_val => p_from_bsv,
763 intercompany_seg_val => p_to_bsv,
764 bal_seg_column_number => l_bal_seg_num,
765 intercompany_column_number => l_ic_seg_num,
766 gl_date => p_gl_date);
767
768 SELECT count(1)
769 INTO l_check_ccid
770 FROM gl_code_combinations cc
771 WHERE x_ccid = cc.code_combination_id
772 AND cc.detail_posting_allowed_flag = 'Y'
773 AND cc.enabled_flag = 'Y'
774 AND cc.summary_flag = 'N'
775 AND Nvl(cc.reference3,'N') = 'N'
776 AND cc.template_id IS NULL
777 AND (Trunc(p_gl_date) BETWEEN
778 Trunc(Nvl(cc.start_date_active,p_gl_date)) AND
779 Trunc(Nvl(cc.end_date_active,p_gl_date)));
780
781 IF (l_check_ccid = 0)
782 THEN
783 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTRA_CC_NOT_VALID');
784 FND_MSG_PUB.Add;
785 x_status := FND_API.G_RET_STS_ERROR;
786
787 ELSE
788 l_check_ccid := 0;
789 END IF;
790
791
792 --bug: 8200511
793
794
795 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
796 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
797 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.get_new_recp_ccid',
798 'Generating new reciprocal acc using ccid : '||l_setup_recp_ccid ||
799 'Bal BSV :'||p_to_bsv||'IC BSV :' ||p_from_bsv);
800 END IF;
801
802
803 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
804 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
805 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.get_new_recp_ccid',
806 'Global ledger id :'||gl_global.context_ledger_id);
807 end if;
808
809 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
810 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
811 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.get_new_recp_ccid',
812 'p_ledger_id:'||p_ledger_id||','||'p_to_ledger_id:'||p_to_ledger_id);
813 end if;
814 -- Call the procedure to generate the new account
815 -- For eg if from the setup table we get back account 99-00-5670-00-99
816 -- and if the from bsv is 01 and to bsv is 02, this api should return ccid
817 -- for account 02-00-5670-00-01
818
819 --Bug: 13590367
820 gl_global.set_aff_validation ('XX',null);
821
822 x_reciprocal_ccid := fun_bal_pkg.get_ccid (
823 ccid => l_setup_recp_ccid,
824 chart_of_accounts_id => l_recp_coa,
825 bal_seg_val => p_to_bsv,
826 intercompany_seg_val => p_from_bsv,
827 bal_seg_column_number => l_recp_bal_seg_num,
828 intercompany_column_number => l_recp_ic_seg_num,
829 gl_date => p_gl_date);
830
831 SELECT count(1)
832 INTO l_check_ccid
833 FROM gl_code_combinations cc
834 WHERE x_reciprocal_ccid = cc.code_combination_id
835 AND cc.detail_posting_allowed_flag = 'Y'
836 AND cc.enabled_flag = 'Y'
837 AND cc.summary_flag = 'N'
838 AND Nvl(cc.reference3,'N') = 'N'
839 AND cc.template_id IS NULL
840 AND (Trunc(p_gl_date) BETWEEN
841 Trunc(Nvl(cc.start_date_active,p_gl_date)) AND
842 Trunc(Nvl(cc.end_date_active,p_gl_date)));
843
844 IF (l_check_ccid = 0)
845 THEN
846 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTRA_CC_NOT_VALID');
847 FND_MSG_PUB.Add;
848 x_status := FND_API.G_RET_STS_ERROR;
849
850 ELSE
851 l_check_ccid := 0;
852 END IF;
853
854 --bug: 8200511
855
856 ELSE
857 -- If the Legal Entities are from different Ledgers, then the intercompany
858 -- and balancing segment values are not switched.
859
860 x_ccid := l_setup_ccid;
861 x_reciprocal_ccid := l_setup_recp_ccid;
862 END IF;
863 BEGIN
864 SELECT 'Y' INTO l_insert_flag
865 FROM FUN_INTER_ACCOUNTS_V
866 WHERE FROM_LE_ID = p_from_le
867 AND LEDGER_ID = p_ledger_id
868 AND TO_LE_ID = p_to_le
869 AND CCID = x_ccid
870 AND TYPE = p_acct_type
871 AND TRANS_BSV = p_from_bsv
872 AND TP_BSV = p_to_bsv;
873 EXCEPTION
874 WHEN NO_DATA_FOUND then
875 l_insert_flag := 'N';
876 WHEN OTHERS THEN
877 l_insert_flag := 'Y';
878 END;
879 IF (l_insert_flag = 'N') THEN
880 INSERT INTO FUN_INTER_ACCOUNTS_ADDL(FROM_LE_ID
881 , LEDGER_ID
882 , TO_LE_ID
883 , CCID
884 , TYPE
885 , START_DATE
886 , DEFAULT_FLAG
887 , OBJECT_VERSION_NUMBER
888 , CREATED_BY
889 , CREATION_DATE
890 , LAST_UPDATED_BY
891 , LAST_UPDATE_DATE
892 , LAST_UPDATE_LOGIN
893 , TRANS_BSV
894 , TP_BSV
895 )
896 VALUES(p_from_le
897 , p_ledger_id
898 , p_to_le
899 , x_ccid
900 , p_acct_type
901 , SYSDATE
902 , NULL
903 , 1
904 , FND_GLOBAL.USER_ID
905 , SYSDATE
906 , FND_GLOBAL.USER_ID
907 , SYSDATE
908 , fnd_global.login_id
909 , p_from_bsv
910 , p_to_bsv
911 );
912 END IF;
913
914 BEGIN
915 SELECT 'Y' INTO l_insert_flag
916 FROM FUN_INTER_ACCOUNTS_V
917 WHERE FROM_LE_ID = p_to_le
918 AND LEDGER_ID = p_to_ledger_id
919 AND TO_LE_ID = p_from_le
920 AND CCID = x_reciprocal_ccid
921 AND TYPE = l_recip_acct_type
922 AND TRANS_BSV = p_to_bsv
923 AND TP_BSV = p_from_bsv;
924 EXCEPTION
925 WHEN NO_DATA_FOUND then
926 l_insert_flag := 'N';
927 WHEN OTHERS THEN
928 l_insert_flag := 'Y';
929 END;
930 IF (l_insert_flag = 'N' AND nvl(x_reciprocal_ccid, 0) > 0) THEN
931 INSERT INTO FUN_INTER_ACCOUNTS_ADDL(FROM_LE_ID
932 , LEDGER_ID
933 , TO_LE_ID
934 , CCID
935 , TYPE
936 , START_DATE
937 , DEFAULT_FLAG
938 , OBJECT_VERSION_NUMBER
939 , CREATED_BY
940 , CREATION_DATE
941 , LAST_UPDATED_BY
942 , LAST_UPDATE_DATE
943 , LAST_UPDATE_LOGIN
944 , TRANS_BSV
945 , TP_BSV
946 )
947 VALUES(p_to_le
948 , p_to_ledger_id
949 , p_from_le
950 , x_reciprocal_ccid
951 , l_recip_acct_type
952 , SYSDATE
953 , NULL
954 , 1
955 , FND_GLOBAL.USER_ID
956 , SYSDATE
957 , FND_GLOBAL.USER_ID
958 , SYSDATE
959 , fnd_global.login_id
960 , p_to_bsv
961 , p_from_bsv
962 );
963 END IF;
964
965 -- Enhancement 7520196 End
966
967 END IF;
968
969 IF Nvl(x_ccid,0) <= 0
970 THEN
971 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTRA_CC_NOT_CREATED');
972 FND_MSG_PUB.Add;
973 x_status := FND_API.G_RET_STS_ERROR;
974
975 END IF;
976
977 SELECT count(1)
978 INTO l_check_ccid
979 FROM gl_code_combinations cc
980 WHERE x_ccid = cc.code_combination_id
981 AND cc.detail_posting_allowed_flag = 'Y'
982 AND cc.enabled_flag = 'Y'
983 AND cc.summary_flag = 'N'
984 AND Nvl(cc.reference3,'N') = 'N'
985 AND cc.template_id IS NULL
986 AND (Trunc(p_gl_date) BETWEEN
987 Trunc(Nvl(cc.start_date_active,p_gl_date)) AND
988 Trunc(Nvl(cc.end_date_active,p_gl_date)));
989
990 IF (l_check_ccid = 0)
991 THEN
992 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTRA_CC_NOT_VALID');
993 FND_MSG_PUB.Add;
994 x_status := FND_API.G_RET_STS_ERROR;
995
996 ELSE
997 l_check_ccid := 0;
998 END IF;
999
1000 SELECT count(1)
1001 INTO l_check_ccid
1002 FROM gl_code_combinations cc
1003 WHERE x_reciprocal_ccid = cc.code_combination_id
1004 AND cc.detail_posting_allowed_flag = 'Y'
1005 AND cc.enabled_flag = 'Y'
1006 AND cc.summary_flag = 'N'
1007 AND Nvl(cc.reference3,'N') = 'N'
1008 AND cc.template_id IS NULL
1009 AND (Trunc(p_gl_date) BETWEEN
1010 Trunc(Nvl(cc.start_date_active,p_gl_date)) AND
1011 Trunc(Nvl(cc.end_date_active,p_gl_date)));
1012
1013 IF (l_check_ccid = 0)
1014 THEN
1015 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTRA_CC_NOT_VALID');
1016 FND_MSG_PUB.Add;
1017 x_status := FND_API.G_RET_STS_ERROR;
1018
1019 ELSE
1020 l_check_ccid := 0;
1021 END IF;
1022 END IF;
1023
1024 -- Standard call to get message count and if count is 1, get message info.
1025 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1026 p_data => x_msg_data);
1027
1028 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1029 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.end', 'end');
1030 END IF;
1031
1032 EXCEPTION
1033 WHEN FND_API.G_EXC_ERROR
1034 THEN
1035 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
1036 THEN
1037 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1038 'fun.plsql.fun_bal_pkg.get_intercompany_account.error',
1039 SUBSTR(SQLERRM,1, 4000));
1040 END IF;
1041
1042 x_status := FND_API.G_RET_STS_ERROR ;
1043
1044 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1045 p_data => x_msg_data);
1046
1047 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1048 THEN
1049 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
1050 THEN
1051 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1052 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.unexpected_error_norm',
1053 SUBSTR(SQLCODE ||' : ' || SQLERRM,1, 4000));
1054 END IF;
1055
1056 x_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1057 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1058 p_data => x_msg_data);
1059
1060 WHEN OTHERS
1061 THEN
1062 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
1063 THEN
1064 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1065 'fun.plsql.fun_bal_utils_grp.get_intercompany_account.unexpected_error_others',
1066 SUBSTR(SQLCODE ||' : ' || SQLERRM,1, 4000));
1067 END IF;
1068
1069 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1070 THEN
1071 FND_MSG_PUB.Add_Exc_Msg(g_package_name, l_api_name);
1072 END IF;
1073
1074 x_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1075
1076 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1077 p_data => x_msg_data);
1078
1079 END get_intercompany_account;
1080
1081 /* ----------------------------------------------------------------------------
1082 -- API name : FUN_BAL_UTILS_GRP.get_intracompany_account
1083 -- Type : Group
1084 -- Pre-reqs : None.
1085 -- Function : Given a transacting and trading Balancing segment value, the
1086 -- the procedure returns the intracompany credit and debit
1087 -- account
1088 -- Parameters :
1089 -- IN : p_api_version IN NUMBER Required
1090 -- p_init_msg_list IN VARCHAR2 Optional
1091 -- p_ledger_id IN NUMBER Required
1092 -- p_from_le IN NUMBER Optional
1093 -- p_source IN VARCHAR2 Optional
1094 -- If not provided, source of 'Other' will be used to derive
1095 -- the account
1096 -- p_category IN VARCHAR2 Optional
1097 -- If not provided, category of 'Other' will be used to derive
1098 -- the account
1099 -- p_from_bsv IN VARCHAR2 Required
1100 -- p_to_bsv IN VARCHAR2 Required
1101 -- p_gl_date IN DATE Required
1102 -- p_acct_type IN VARCHAR2 Required
1103 -- Account type would be 'D'ebit or 'C'redit
1104 --
1105 -- OUT : x_status VARCHAR2
1106 -- x_msg_count NUMBER
1107 -- x_msg_data VARCHAR2
1108 -- x_ccid VARCHAR2 CCID requested
1109 -- x_reciprocal_ccid VARCHAR2 Reciprocal CCID
1110 -- Eg. If debit account ccid is requested for BSV1 => BSV2
1111 -- x_reciprocal_ccid will contain the credit account for
1112 -- BSV2 => BSV1
1113 --
1114 -- Version : Current version 1.0
1115 -- Previous version 1.0
1116 -- Initial version 1.0
1117 ------------------------------------------------------------------------------*/
1118 PROCEDURE get_intracompany_account (p_api_version IN NUMBER,
1119 p_init_msg_list IN VARCHAR2,
1120 p_ledger_id IN NUMBER,
1121 p_from_le IN NUMBER,
1122 p_source IN VARCHAR2,
1123 p_category IN VARCHAR2,
1124 p_dr_bsv IN VARCHAR2,
1125 p_cr_bsv IN VARCHAR2,
1126 p_gl_date IN DATE,
1127 p_acct_type IN VARCHAR2,
1128 x_status IN OUT NOCOPY VARCHAR2,
1129 x_msg_count IN OUT NOCOPY NUMBER,
1130 x_msg_data IN OUT NOCOPY VARCHAR2,
1131 x_ccid IN OUT NOCOPY NUMBER ,
1132 x_reciprocal_ccid IN OUT NOCOPY NUMBER)
1133 IS
1134
1135 CURSOR c_get_template (p_ledger_id NUMBER,
1136 p_le_id NUMBER,
1137 p_source_name VARCHAR2,
1138 p_category_name VARCHAR2)
1139 IS
1140 SELECT NVL((SELECT opts.template_id
1141 FROM fun_balance_options opts
1142 WHERE opts.ledger_id = p_ledger_id
1143 AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
1144 AND opts.je_source_name = p_source_name
1145 AND opts.je_category_name = p_category_name
1146 AND opts.status_flag = 'Y'),
1147 NVL((SELECT opts.template_id
1148 FROM fun_balance_options opts
1149 WHERE opts.ledger_id = p_ledger_id
1150 AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
1151 AND opts.je_source_name = p_source_name
1152 AND opts.je_category_name = 'Other'
1153 AND opts.status_flag = 'Y'),
1154 NVL((SELECT opts.template_id
1155 FROM fun_balance_options opts
1156 WHERE opts.ledger_id = p_ledger_id
1157 AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
1158 AND opts.je_source_name = 'Other'
1159 AND opts.je_category_name = p_category_name
1160 AND opts.status_flag = 'Y'),
1161 (SELECT opts.template_id
1162 FROM fun_balance_options opts
1163 WHERE opts.ledger_id = p_ledger_id
1164 AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
1165 AND opts.je_source_name = 'Other'
1166 AND opts.je_category_name = 'Other'
1167 AND opts.status_flag = 'Y')))) template_id
1168 From Dual;
1169
1170 CURSOR c_get_ccid (p_template_id NUMBER,
1171 p_dr_bsv VARCHAR2,
1172 p_cr_bsv VARCHAR2,
1173 p_acct_type VARCHAR2)
1174 IS
1175 SELECT NVL((SELECT DECODE (p_acct_type, 'D', accts.dr_ccid, 'C', cr_ccid)
1176 FROM fun_balance_accounts accts
1177 WHERE accts.template_id = p_template_id
1178 AND accts.dr_bsv = p_dr_bsv
1179 AND accts.cr_bsv = p_cr_bsv),
1180 NVL((SELECT DECODE (p_acct_type, 'D', accts.dr_ccid, 'C', cr_ccid)
1181 FROM fun_balance_accounts accts
1182 WHERE accts.template_id = p_template_id
1183 AND accts.dr_bsv = p_dr_bsv
1184 AND accts.cr_bsv = 'OTHER1234567890123456789012345'),
1185 NVL((SELECT DECODE (p_acct_type, 'D', accts.dr_ccid, 'C', cr_ccid)
1186 FROM fun_balance_accounts accts
1187 WHERE accts.template_id = p_template_id
1188 AND accts.dr_bsv = 'OTHER1234567890123456789012345'
1189 AND accts.cr_bsv = p_cr_bsv),
1190 (SELECT DECODE (p_acct_type, 'D', accts.dr_ccid, 'C', cr_ccid)
1191 FROM fun_balance_accounts accts
1192 WHERE accts.template_id = p_template_id
1193 AND accts.dr_bsv = 'OTHER1234567890123456789012345'
1194 AND accts.cr_bsv = 'OTHER1234567890123456789012345')))) ccid
1195 From Dual;
1196
1197 l_api_name CONSTANT VARCHAR2(30) := 'Get_Intracompany_Account';
1198 l_api_version CONSTANT NUMBER := 1.0;
1199 l_return_status VARCHAR2(1);
1200 l_from_le_id NUMBER ;
1201 l_recip_acct_type VARCHAR2(1);
1202 l_template_id NUMBER;
1203 l_source gl_je_sources.je_source_name%TYPE;
1204 l_category gl_je_categories.je_category_name%TYPE;
1205
1206 l_setup_ccid NUMBER;
1207 l_setup_recp_ccid NUMBER;
1208 l_coa NUMBER;
1209 l_dummy NUMBER;
1210 l_ic_seg_num NUMBER;
1211 l_bal_seg_num NUMBER;
1212 -- Bug: 7368523
1213 l_dr_bsv FUN_BALANCE_ACCOUNTS.DR_BSV%TYPE;
1214 l_cr_bsv FUN_BALANCE_ACCOUNTS.CR_BSV%TYPE;
1215 l_check_ccid NUMBER;
1216 BEGIN
1217
1218 l_check_ccid := 0;
1219 -- Bug 7193385 Start
1220 IF (p_acct_type = 'C')
1221 THEN
1222 l_dr_bsv := p_cr_bsv;
1223 l_cr_bsv := p_dr_bsv;
1224 ELSE
1225 l_dr_bsv := p_dr_bsv;
1226 l_cr_bsv := p_cr_bsv;
1227 END IF;
1228 -- Bug 7193385 End
1229
1230 -- variable p_validation_level is not used .
1231 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1232
1233 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1234 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_utils_grp.Get_Intracompany_Account.begin', 'begin');
1235 END IF;
1236
1237 -- Standard call to check for call compatibility.
1238 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1239 p_api_version,
1240 l_api_name,
1241 g_package_name )
1242 THEN
1243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1244 END IF;
1245
1246 -- Initialize message list if p_init_msg_list is set to TRUE.
1247 IF FND_API.to_Boolean( p_init_msg_list )
1248 THEN
1249 FND_MSG_PUB.initialize;
1250 END IF;
1251
1252 -- Initialize API return status to success
1253 x_status := FND_API.G_RET_STS_SUCCESS;
1254
1255 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1256 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1257 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.get_template',
1258 'Fetching the template id');
1259
1260 END IF;
1261
1262 l_source := Nvl(p_source, 'Other');
1263 l_category := Nvl(p_category, 'Other');
1264
1265 -- Rules of precedence to find template id. Look for ..
1266 -- 1) Source , Category
1267 -- 2) Source , 'Other'
1268 -- 3) 'Other', Category
1269 -- 4) 'Other', 'Other'
1270
1271 OPEN c_get_template (p_ledger_id => p_ledger_id,
1272 p_le_id => p_from_le,
1273 p_source_name => l_source,
1274 p_category_name => l_category);
1275 FETCH c_get_template INTO l_template_id;
1276 CLOSE c_get_template;
1277
1278 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1279 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1280 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.got_template',
1281 'Template id is '||l_template_id );
1282 END IF;
1283
1284 IF l_template_id IS NULL
1285 THEN
1286 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTRA_RULE_NOT_ASSIGNED');
1287 FND_MSG_PUB.Add;
1288 x_status := FND_API.G_RET_STS_ERROR;
1289 END IF;
1290
1291 IF x_status = FND_API.G_RET_STS_SUCCESS
1292 THEN
1293 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1294 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1295 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.get_ccid',
1296 'Fetching the ccid');
1297 END IF;
1298
1299 -- Now get the debit or credit account.
1300 -- Rules of precedence to find ccid. Look for ..
1301 -- 1) Dr BSV , Cr BSV
1302 -- 2) Dr BSV , 'Other'
1303 -- 3) 'Other', Cr BSV
1304 -- 4) 'Other', 'Other'
1305 OPEN c_get_ccid (p_template_id => l_template_id,
1306 p_dr_bsv => l_dr_bsv,
1307 p_cr_bsv => l_cr_bsv,
1308 p_acct_type => p_acct_type);
1309 FETCH c_get_ccid INTO l_setup_ccid;
1310 CLOSE c_get_ccid ;
1311
1312 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1313 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1314 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.got_ccid',
1315 'Fetched the ccid : '|| l_setup_ccid);
1316 END IF;
1317
1318 -- Now get the reciprocal account
1319 IF p_acct_type = 'D'
1320 THEN
1321 l_recip_acct_type := 'C';
1322 ELSE
1323 l_recip_acct_type := 'D';
1324 END IF;
1325
1326 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1327 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1328 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.get_recip_ccid',
1329 'Fetching the reciprocal ccid');
1330 END IF;
1331
1332 -- Bug 7409706: For Intracompany Rule
1333 -- Debit BSV Credit BSV Debit Accoun Credit Account
1334 -- 01 02 01.XXX.02 02.XXX.01
1335 -- 02 01 02.YYY.01 01.YYY.02
1336 -- Id ccid = 01.XXX.02, then reciprocal_ccid = 02.XXX.01 and not 02.YYY.01
1337 OPEN c_get_ccid (p_template_id => l_template_id,
1338 p_dr_bsv => l_dr_bsv,
1339 p_cr_bsv => l_cr_bsv,
1340 p_acct_type => l_recip_acct_type);
1341 FETCH c_get_ccid INTO l_setup_recp_ccid;
1342 CLOSE c_get_ccid ;
1343
1344 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1345 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1346 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.got_recip_ccid',
1347 'Fetched the reciprocal ccid : ' || l_setup_recp_ccid);
1348 END IF;
1349
1350 -- Now we need to generate the new accounts by replacing the intercompany
1351 -- segments and the balancing segments
1352 -- First find out the segment numbers for the Balancing segment
1353 OPEN c_get_seg_num(p_ledger_id,
1354 'GL_BALANCING');
1355 FETCH c_get_seg_num INTO l_bal_seg_num,
1356 l_coa;
1357 CLOSE c_get_seg_num;
1358
1359 -- Next find out the segment numbers for the Intercompany segment
1360 OPEN c_get_seg_num(p_ledger_id,
1361 'GL_INTERCOMPANY');
1362 FETCH c_get_seg_num INTO l_ic_seg_num,
1363 l_dummy;
1364 CLOSE c_get_seg_num;
1365
1366 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1367 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1368 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.get_new_ccid',
1369 'Generating new acc using ccid : '||l_setup_ccid ||
1370 'Bal BSV :'||p_dr_bsv||'IC BSV :' ||p_cr_bsv);
1371 END IF;
1372
1373
1374 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1375 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1376 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.get_new_ccid',
1377 'Global ledger id :'||gl_global.context_ledger_id);
1378 end if;
1379
1380 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1381 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1382 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.get_new_ccid',
1383 'p_ledger_id:'||p_ledger_id);
1384 end if;
1385
1386 -- Call the procedure to generate the new account
1387 -- For eg if from the setup table we get back account 99-00-4350-00-99
1388 -- and if the dr bsv is 01 and cr bsv is 02, this api should return ccid
1389 -- for account 01-00-4350-00-02
1390
1391 --Bug: 13590367
1392 gl_global.set_aff_validation ('XX',null);
1393
1394 x_ccid := fun_bal_pkg.get_ccid (
1395 ccid => l_setup_ccid,
1396 chart_of_accounts_id => l_coa,
1397 bal_seg_val => p_dr_bsv,
1398 intercompany_seg_val => p_cr_bsv,
1399 bal_seg_column_number => l_bal_seg_num,
1400 intercompany_column_number => l_ic_seg_num,
1401 gl_date => p_gl_date);
1402
1403 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1404 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1405 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.get_new_recp_ccid',
1406 'Generating new reciprocal acc using ccid : '||l_setup_recp_ccid ||
1407 'Bal BSV :'||p_cr_bsv||'IC BSV :' ||p_dr_bsv);
1408 END IF;
1409
1410
1411 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1412 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1413 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.get_new_recp_ccid',
1414 'Global ledger id :'||gl_global.context_ledger_id);
1415 end if;
1416
1417 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1418 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1419 'fun.plsql.fun_bal_utils_grp.get_intracompany_account.get_new_recp_ccid',
1420 'p_ledger_id:'||p_ledger_id);
1421 end if;
1422 -- Call the procedure to generate the new account
1423 -- For eg if from the setup table we get back account 99-00-5670-00-99
1424 -- and if the dr bsv is 01 and cr bsv is 02, this api should return ccid
1425 -- for account 02-00-5670-00-01
1426
1427 --Bug: 13590367
1428 gl_global.set_aff_validation ('XX',null);
1429
1430 x_reciprocal_ccid := fun_bal_pkg.get_ccid (
1431 ccid => l_setup_recp_ccid,
1432 chart_of_accounts_id => l_coa,
1433 bal_seg_val => p_cr_bsv,
1434 intercompany_seg_val => p_dr_bsv,
1435 bal_seg_column_number => l_bal_seg_num,
1436 intercompany_column_number => l_ic_seg_num,
1437 gl_date => p_gl_date);
1438
1439 END IF;
1440
1441 IF Nvl(x_ccid,0) <= 0
1442 THEN
1443 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTRA_CC_NOT_CREATED');
1444 FND_MSG_PUB.Add;
1445 x_status := FND_API.G_RET_STS_ERROR;
1446 END IF;
1447
1448 SELECT count(1)
1449 INTO l_check_ccid
1450 FROM gl_code_combinations cc
1451 WHERE x_ccid = cc.code_combination_id
1452 AND cc.detail_posting_allowed_flag = 'Y'
1453 AND cc.enabled_flag = 'Y'
1454 AND cc.summary_flag = 'N'
1455 AND Nvl(cc.reference3,'N') = 'N'
1456 AND cc.template_id IS NULL
1457 AND (Trunc(p_gl_date) BETWEEN
1458 Trunc(Nvl(cc.start_date_active,p_gl_date)) AND
1459 Trunc(Nvl(cc.end_date_active,p_gl_date)));
1460
1461 IF (l_check_ccid = 0)
1462 THEN
1463 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTRA_CC_NOT_VALID');
1464 FND_MSG_PUB.Add;
1465 x_status := FND_API.G_RET_STS_ERROR;
1466 ELSE
1467 l_check_ccid := 0;
1468 END IF;
1469
1470 SELECT count(1)
1471 INTO l_check_ccid
1472 FROM gl_code_combinations cc
1473 WHERE x_reciprocal_ccid = cc.code_combination_id
1474 AND cc.detail_posting_allowed_flag = 'Y'
1475 AND cc.enabled_flag = 'Y'
1476 AND cc.summary_flag = 'N'
1477 AND Nvl(cc.reference3,'N') = 'N'
1478 AND cc.template_id IS NULL
1479 AND (Trunc(p_gl_date) BETWEEN
1480 Trunc(Nvl(cc.start_date_active,p_gl_date)) AND
1481 Trunc(Nvl(cc.end_date_active,p_gl_date)));
1482
1483 IF (l_check_ccid = 0)
1484 THEN
1485 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTRA_CC_NOT_VALID');
1486 FND_MSG_PUB.Add;
1487 x_status := FND_API.G_RET_STS_ERROR;
1488 ELSE
1489 l_check_ccid := 0;
1490 END IF;
1491
1492 -- Standard call to get message count and if count is 1, get message info.
1493 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1494 p_data => x_msg_data);
1495
1496 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1497 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_utils_grp.Get_Intracompany_Account.end', 'end');
1498 END IF;
1499
1500 EXCEPTION
1501 WHEN FND_API.G_EXC_ERROR
1502 THEN
1503 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
1504 THEN
1505 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1506 'fun.plsql.fun_bal_pkg.Get_Intracompany_Account.error',
1507 SUBSTR(SQLERRM,1, 4000));
1508 END IF;
1509
1510 x_status := FND_API.G_RET_STS_ERROR ;
1511 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1512 p_data => x_msg_data);
1513
1514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1515 THEN
1516 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
1517 THEN
1518 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1519 'fun.plsql.fun_bal_utils_grp.Get_Intracompany_Account.unexpected_error_norm',
1520 SUBSTR(SQLCODE ||' : ' || SQLERRM,1, 4000));
1521 END IF;
1522
1523 x_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1524 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1525 p_data => x_msg_data);
1526
1527 WHEN OTHERS
1528 THEN
1529 IF (FND_LOG.LEVEL_ERROR>= g_debug_level)
1530 THEN
1531 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1532 'fun.plsql.fun_bal_utils_grp.Get_Intracompany_Account.unexpected_error_others',
1533 SUBSTR(SQLCODE ||' : ' || SQLERRM,1, 4000));
1534 END IF;
1535
1536 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1537 THEN
1538 FND_MSG_PUB.Add_Exc_Msg(g_package_name, l_api_name);
1539 END IF;
1540
1541 x_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1542 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1543 p_data => x_msg_data);
1544
1545 END get_intracompany_account;
1546
1547 END FUN_BAL_UTILS_GRP;
1548