1 PACKAGE BODY IGC_CC_MC_MAIN_PVT as
2 /* $Header: IGCCMMMB.pls 120.3.12000000.4 2007/10/19 06:42:52 smannava ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_MC_MAIN_PVT';
5 g_debug_flag VARCHAR2(1) := 'N' ;
6
7 /* ================================================================================
8 PROCEDURE Insert_Row => IGC_CC_MC_HEADERS
9 ===============================================================================*/
10
11 PROCEDURE get_rsobs_Headers (
12 p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
14 p_commit IN VARCHAR2 := FND_API.G_FALSE,
15 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
16 X_return_status OUT NOCOPY VARCHAR2,
17 X_msg_count OUT NOCOPY NUMBER,
18 X_msg_data OUT NOCOPY VARCHAR2,
19 p_CC_Header_Id IN NUMBER,
20 p_Set_Of_Books_Id IN NUMBER,
21 l_Application_Id IN NUMBER,
22 p_org_id IN NUMBER,
23 l_Conversion_Date IN DATE,
24 l_flag IN VARCHAR2
25 ) IS
26
27 l_sob_list gl_mc_info.r_sob_list := gl_mc_info.r_sob_list();
28 l_row_count NUMBER;
29 l_FROM_CURR varchar2(10);
30 l_TO_CURR varchar2(10);
31 l_Conversion_Rate Number;
32 l_Conversion_Type VARCHAR2(30);
33 l_rsob_id GL_ALC_LEDGER_RSHIPS_V.LEDGER_ID%TYPE;
34 l_rate_exists VARCHAR2(1);
35 l_api_name CONSTANT VARCHAR2(30) := 'get_rsobs_Headers';
36 l_api_version CONSTANT NUMBER := 1.0;
37 l_return_status VARCHAR2(1);
38 l_row_id VARCHAR2(18);
39 /* Commented below query and added below one for r12 MRC uptake for bug#6341012
40 CURSOR c_conversion_type IS
41 SELECT conversion_type
42 FROM gl_mc_reporting_options
43 WHERE primary_set_of_books_id = p_Set_Of_Books_Id
44 AND reporting_set_of_books_id = l_rsob_id
45 AND ORG_ID = p_Org_Id
46 AND application_id = l_Application_Id; */
47 CURSOR c_conversion_type IS
48 SELECT ALC_DEFAULT_CONV_RATE_TYPE
49 FROM GL_ALC_LEDGER_RSHIPS_V
50 WHERE primary_ledger_id = p_Set_Of_Books_Id
51 AND ledger_id = l_rsob_id
52 --AND ORG_ID = p_Org_Id
53 AND application_id = l_Application_Id;
54 BEGIN
55
56 SAVEPOINT get_rsobs_Headers_PT;
57
58 IF NOT FND_API.Compatible_API_Call ( l_api_version,
59 p_api_version,
60 l_api_name,
61 G_PKG_NAME )
62 THEN
63 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
64 END IF;
65
66
67 IF FND_API.to_Boolean (p_init_msg_list ) THEN
68 FND_MSG_PUB.initialize;
69 END IF;
70
71 X_return_status := FND_API.G_RET_STS_SUCCESS;
72
73 -- -------------------------------------------------------------------------
74 -- Obtain all sets of books associated to the Primary set of books ID
75 -- received from the caller.
76 -- -------------------------------------------------------------------------
77 gl_mc_info.get_associated_sobs ( p_Set_Of_Books_Id,
78 l_Application_Id,
79 p_org_id,
80 NULL,
81 l_sob_list);
82
83 l_row_count := l_sob_list.count;
84
85 -- -------------------------------------------------------------------------
86 -- Loop through all sets of books retrived and determine what the Primary
87 -- set of books currency has been defined to be.
88 -- -------------------------------------------------------------------------
89 FOR Rec in 1..l_row_count LOOP
90
91 IF l_sob_list(Rec).r_sob_type = 'P' THEN
92 l_FROM_CURR := l_sob_list(Rec).r_sob_curr;
93 END IF;
94
95 END LOOP;
96
97 -- -------------------------------------------------------------------------
98 -- Loop through all the set of books retrieved if there were any defined.
99 -- -------------------------------------------------------------------------
100 FOR Rec1 in 1..l_row_count LOOP
101
102 -- -------------------------------------------------------------------------
103 -- Check to make sure that the Reporting set of books is being checked and
104 -- not the Primary set of books.
105 -- -------------------------------------------------------------------------
106 IF (l_sob_list(rec1).r_sob_type = 'R') THEN
107
108 l_rsob_id := l_sob_list(rec1).r_sob_id;
109 l_TO_CURR := l_sob_list(rec1).r_sob_curr;
110
111 -- -------------------------------------------------------------------------
112 -- Obtain the conversion type for the reporting set of books.
113 -- -------------------------------------------------------------------------
114 OPEN c_conversion_type;
115 FETCH c_conversion_type
116 INTO l_Conversion_Type;
117 CLOSE c_conversion_type;
118
119 -- -------------------------------------------------------------------------
120 -- Check to see if the conversion rate exists or not.
121 -- -------------------------------------------------------------------------
122 l_rate_exists := gl_currency_api.rate_exists ( l_FROM_CURR,
123 l_TO_CURR,
124 l_Conversion_Date,
125 l_Conversion_Type
126 );
127
128 -- ------------------------------------------------------------------------
129 -- If the rate exists then obtain the rate to be inserted for the
130 -- reporting set of books.
131 -- ------------------------------------------------------------------------
132 IF (l_rate_exists = 'Y') THEN
133
134 l_Conversion_Rate := GL_CURRENCY_API.GET_RATE ( l_FROM_CURR,
135 l_TO_CURR,
136 l_Conversion_Date,
137 l_Conversion_Type
138 );
139
140 -- -----------------------------------------------------------------------
141 -- If insert is being performed then insert the appropriate MRC record
142 -- for the set of books.
143 -- -----------------------------------------------------------------------
144 IF (l_flag = 'I') THEN
145
146 IGC_CC_MC_HEADERS_PKG.Insert_Row (
147 l_api_version,
148 FND_API.G_FALSE,
149 FND_API.G_FALSE,
150 FND_API.G_VALID_LEVEL_FULL,
151 l_return_status,
152 X_msg_count,
153 X_msg_data,
154 l_row_id,
155 p_CC_Header_Id,
156 l_sob_list(rec1).r_sob_id,
157 l_Conversion_Type,
158 l_Conversion_Date,
159 l_conversion_Rate
160 );
161
162 -- ------------------------------------------------------------------
163 -- Make sure that the insertion was a success
164 -- ------------------------------------------------------------------
165 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
166 RAISE FND_API.G_EXC_ERROR;
167 END IF;
168
169 -- ----------------------------------------------------------------------
170 -- If Update is requested then update the appropriate record in the MRC
171 -- table with the correlating information.
172 -- ----------------------------------------------------------------------
173 ELSIF (l_flag = 'U') THEN
174
175 -- ----------------------------------------------------------------------
176 -- Obtain the ROW ID that is to be updated.
177 -- ----------------------------------------------------------------------
178 SELECT rowid
179 INTO l_row_id
180 FROM igc_cc_mc_headers
181 WHERE cc_header_id = p_CC_Header_Id
182 AND set_of_books_id = l_sob_list(rec1).r_sob_id;
183
184 IGC_CC_MC_HEADERS_PKG.Update_Row (
185 l_api_version,
186 FND_API.G_FALSE,
187 FND_API.G_FALSE,
188 FND_API.G_VALID_LEVEL_FULL,
189 l_return_status,
190 X_msg_count,
191 X_msg_data,
192 l_row_id,
193 p_CC_Header_Id,
194 l_sob_list(rec1).r_sob_id,
195 l_Conversion_Type,
196 l_Conversion_Date,
197 l_conversion_Rate
198 );
199
200 -- ------------------------------------------------------------------
201 -- Make sure that the insertion was a success
202 -- ------------------------------------------------------------------
203 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
204 RAISE FND_API.G_EXC_ERROR;
205 END IF;
206
207 ELSE
208
209 RAISE FND_API.G_EXC_ERROR;
210
211 END IF; -- Insert / Update Flag
212
213 END IF; -- Rate exists
214
215 END IF; -- Reporting set of books check
216
217 END LOOP; -- Loop for associated SOBs.
218
219 -- ------------------------------------------------------------------------
220 -- Only commit the information if the caller has requested it to be.
221 -- ------------------------------------------------------------------------
222 IF FND_API.To_Boolean ( p_commit ) THEN
223 COMMIT WORK;
224 END iF;
225
226 -- ------------------------------------------------------------------------
227 -- Make sure that the cursor used is closed upon exit
228 -- ------------------------------------------------------------------------
229 IF (c_conversion_type%ISOPEN) THEN
230 CLOSE c_conversion_type;
231 END IF;
232
233 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
234 p_data => X_msg_data);
235
236 RETURN;
237
238 EXCEPTION
239
240 WHEN FND_API.G_EXC_ERROR THEN
241
242 ROLLBACK TO get_rsobs_Headers_PT;
243 X_return_status := FND_API.G_RET_STS_ERROR;
244 IF (c_conversion_type%ISOPEN) THEN
245 CLOSE c_conversion_type;
246 END IF;
247 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
248 p_data => X_msg_data);
249
250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
251
252 ROLLBACK TO get_rsobs_Headers_PT;
253 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
254 IF (c_conversion_type%ISOPEN) THEN
255 CLOSE c_conversion_type;
256 END IF;
257 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
258 p_data => X_msg_data);
259
260 WHEN OTHERS THEN
261
262 ROLLBACK TO get_rsobs_Headers_PT;
263 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
264 IF (c_conversion_type%ISOPEN) THEN
265 CLOSE c_conversion_type;
266 END IF;
267
268 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
269 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
270 l_api_name);
271 END if;
272
273 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
274 p_data => X_msg_data);
275
276 END get_rsobs_Headers;
277
278 /* ================================================================================
279 PROCEDURE Insert_Row => IGC_CC_MC_ACCT_LINES
280 ===============================================================================*/
281
282 PROCEDURE get_rsobs_Acct_Lines (
283 p_api_version IN NUMBER,
284 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
285 p_commit IN VARCHAR2 := FND_API.G_FALSE,
286 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
287 X_return_status OUT NOCOPY VARCHAR2,
288 X_msg_count OUT NOCOPY NUMBER,
289 X_msg_data OUT NOCOPY VARCHAR2,
290 p_CC_Acct_Line_Id IN NUMBER,
291 p_Set_Of_Books_Id IN NUMBER,
292 l_Application_Id IN NUMBER,
293 p_org_id IN NUMBER,
294 l_Conversion_Date IN DATE,
295 p_CC_Acct_Func_Amt IN NUMBER,
296 p_CC_Acct_Encmbrnc_Amt IN NUMBER,
297 p_CC_Func_Withheld_Amt IN NUMBER,
298 l_flag IN VARCHAR2
299 ) IS
300
301 l_sob_list gl_mc_info.r_sob_list := gl_mc_info.r_sob_list();
302 l_row_count NUMBER;
303 l_FROM_CURR varchar2(10);
304 l_TO_CURR varchar2(10);
305 l_Conversion_Rate Number;
306 l_Conversion_Type VARCHAR2(30);
307 l_CC_Acct_Func_Amt NUMBER;
308 l_CC_Acct_Encmbrnc_Amt NUMBER;
309 l_CC_Func_Withheld_Amt NUMBER;
310 l_rsob_id GL_ALC_LEDGER_RSHIPS_V.LEDGER_ID%TYPE;
311 l_rate_exists VARCHAR2(1);
312 l_api_name CONSTANT VARCHAR2(30) := 'get_rsobs_Acct_Lines';
313 l_api_version CONSTANT NUMBER := 1.0;
314 l_return_status VARCHAR2(1);
315 l_row_id VARCHAR2(18);
316 /* Commented below query and added below one for r12 MRC uptake for bug#6341012
317 CURSOR c_conversion_type IS
318 SELECT conversion_type
319 FROM gl_mc_reporting_options
320 WHERE primary_set_of_books_id = p_Set_Of_Books_Id
321 AND reporting_set_of_books_id = l_rsob_id
322 AND ORG_ID = p_Org_Id
323 AND application_id = l_Application_Id;*/
324 CURSOR c_conversion_type IS
325 SELECT ALC_DEFAULT_CONV_RATE_TYPE
326 FROM GL_ALC_LEDGER_RSHIPS_V
327 WHERE primary_ledger_id = p_Set_Of_Books_Id
328 AND ledger_id = l_rsob_id
329 -- AND ORG_ID = p_Org_Id
330 AND application_id = l_Application_Id;
331 BEGIN
332
333 SAVEPOINT get_rsobs_Acct_Lines_PT;
334
335 IF NOT FND_API.Compatible_API_Call ( l_api_version,
336 p_api_version,
337 l_api_name,
338 G_PKG_NAME )
339 THEN
340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
341 END IF;
342
343
344 IF FND_API.to_Boolean (p_init_msg_list ) THEN
345 FND_MSG_PUB.initialize;
346 END IF;
347
348 X_return_status := FND_API.G_RET_STS_SUCCESS;
349
350 -- -------------------------------------------------------------------------
351 -- Obtain all sets of books associated to the Primary set of books ID
352 -- received from the caller.
353 -- -------------------------------------------------------------------------
354 gl_mc_info.get_associated_sobs ( p_Set_Of_Books_Id,
355 l_Application_Id,
356 p_org_id,
357 NULL,
358 l_sob_list);
359
360 l_row_count := l_sob_list.count;
361
362 -- -------------------------------------------------------------------------
363 -- Loop through all sets of books retrived and determine what the Primary
364 -- set of books currency has been defined to be.
365 -- -------------------------------------------------------------------------
366 FOR Rec in 1..l_row_count LOOP
367
368 IF (l_sob_list(Rec).r_sob_type = 'P') THEN
369 l_FROM_CURR := l_sob_list(Rec).r_sob_curr;
370 END IF;
371
372 END LOOP;
373
374 -- -------------------------------------------------------------------------
375 -- Loop through all the set of books retrieved if there were any defined.
376 -- -------------------------------------------------------------------------
377 FOR Rec1 in 1..l_row_count LOOP
378
379 IF (l_sob_list(rec1).r_sob_type = 'R') THEN
380
381 l_rsob_id := l_sob_list(rec1).r_sob_id;
382 l_TO_CURR := l_sob_list(rec1).r_sob_curr;
383
384 -- -------------------------------------------------------------------------
385 -- Obtain the conversion type for the reporting set of books.
386 -- -------------------------------------------------------------------------
387 OPEN c_conversion_type;
388 FETCH c_conversion_type
389 INTO l_Conversion_Type;
390 CLOSE c_conversion_type;
391
392 -- -------------------------------------------------------------------------
393 -- Check to see if the conversion rate exists or not.
394 -- -------------------------------------------------------------------------
395 l_rate_exists := gl_currency_api.rate_exists (l_FROM_CURR,
396 l_TO_CURR,
397 l_Conversion_Date,
398 l_Conversion_Type
399 );
400
401 -- ------------------------------------------------------------------------
402 -- If the rate exists then obtain the rate to be inserted for the
403 -- reporting set of books.
404 -- ------------------------------------------------------------------------
405 IF (l_rate_exists = 'Y') THEN
406
407 l_Conversion_Rate := GL_CURRENCY_API.GET_RATE (l_FROM_CURR,
408 l_TO_CURR,
409 l_Conversion_Date,
410 l_Conversion_Type
411 );
412
413 l_CC_Acct_Func_Amt := GL_CURRENCY_API.CONVERT_AMOUNT (l_FROM_CURR,
414 l_TO_CURR,
415 l_CONVERSION_DATE,
416 l_CONVERSION_TYPE,
417 p_CC_Acct_Func_Amt
418 );
419
420
421 l_CC_Acct_Encmbrnc_Amt := GL_CURRENCY_API.CONVERT_AMOUNT (l_FROM_CURR,
422 l_TO_CURR,
423 l_CONVERSION_DATE,
424 l_CONVERSION_TYPE,
425 p_CC_Acct_Encmbrnc_Amt
426 );
427
428 l_CC_Func_Withheld_Amt := GL_CURRENCY_API.CONVERT_AMOUNT (l_FROM_CURR,
429 l_TO_CURR,
430 l_CONVERSION_DATE,
431 l_CONVERSION_TYPE,
432 p_cc_Func_Withheld_Amt
433 );
434 -- -----------------------------------------------------------------------
435 -- If insert is being performed then insert the appropriate MRC record
436 -- for the set of books.
437 -- -----------------------------------------------------------------------
438 IF (l_flag = 'I') THEN
439
440 IGC_CC_MC_ACCT_LINES_PKG.Insert_Row (
441 l_api_version,
442 FND_API.G_FALSE,
443 FND_API.G_FALSE,
444 FND_API.G_VALID_LEVEL_FULL,
445 l_return_status,
446 X_msg_count,
447 X_msg_data,
448 l_row_id,
449 p_CC_Acct_Line_Id,
450 l_sob_list(rec1).r_sob_id,
451 l_CC_Acct_Func_Amt,
452 l_CC_Acct_Encmbrnc_Amt,
453 l_Conversion_Type,
454 l_Conversion_Date,
455 l_conversion_Rate,
456 l_cc_func_withheld_amt
457 );
458
459 -- ------------------------------------------------------------------
460 -- Make sure that the insertion was a success
461 -- ------------------------------------------------------------------
462 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
463 RAISE FND_API.G_EXC_ERROR;
464 END IF;
465
466 -- ----------------------------------------------------------------------
467 -- If delete is requested then delete the appropriate record in the MRC
468 -- table with the correlating information.
469 -- ----------------------------------------------------------------------
470 ELSIF (l_flag = 'D') THEN
471
472 -- ----------------------------------------------------------------------
473 -- Obtain the ROW ID that is to be updated.
474 -- ----------------------------------------------------------------------
475 SELECT rowid
476 INTO l_row_id
477 FROM igc_cc_mc_acct_lines
478 WHERE cc_acct_line_id = p_CC_Acct_Line_Id
479 AND set_of_books_id = l_sob_list(rec1).r_sob_id;
480
481 IGC_CC_MC_ACCT_LINES_PKG.Delete_Row (
482 l_api_version,
483 FND_API.G_FALSE,
484 FND_API.G_FALSE,
485 FND_API.G_VALID_LEVEL_FULL,
486 l_return_status,
487 X_msg_count,
488 X_msg_data,
489 l_row_id
490 );
491
492 -- ------------------------------------------------------------------
493 -- Make sure that the insertion was a success
494 -- ------------------------------------------------------------------
495 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
496 RAISE FND_API.G_EXC_ERROR;
497 END IF;
498
499 -- ----------------------------------------------------------------------
500 -- If Update is requested then update the appropriate record in the MRC
501 -- table with the correlating information.
502 -- ----------------------------------------------------------------------
503 ELSIF (l_flag = 'U') THEN
504
505 -- ----------------------------------------------------------------------
506 -- Obtain the ROW ID that is to be updated.
507 -- ----------------------------------------------------------------------
508 SELECT rowid
509 INTO l_row_id
510 FROM igc_cc_mc_acct_lines
511 WHERE cc_acct_line_id = p_CC_Acct_Line_Id
512 AND set_of_books_id = l_sob_list(rec1).r_sob_id;
513
514 IGC_CC_MC_ACCT_LINES_PKG.Update_Row (
515 l_api_version,
516 FND_API.G_FALSE,
517 FND_API.G_FALSE,
518 FND_API.G_VALID_LEVEL_FULL,
519 l_return_status,
520 X_msg_count,
521 X_msg_data,
522 l_row_id,
523 p_CC_Acct_Line_Id,
524 l_sob_list(rec1).r_sob_id,
525 l_CC_Acct_Func_Amt,
526 l_CC_Acct_Encmbrnc_Amt,
527 l_Conversion_Type,
528 l_Conversion_Date,
529 l_conversion_Rate,
530 l_cc_func_withheld_amt
531 );
532
533 -- ------------------------------------------------------------------
534 -- Make sure that the insertion was a success
535 -- ------------------------------------------------------------------
536 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
537 RAISE FND_API.G_EXC_ERROR;
538 END IF;
539
540 ELSE
541
542 RAISE FND_API.G_EXC_ERROR;
543
544 END IF; -- Insert / Update Flag
545
546 END IF; -- Rate exists
547
548 END IF; -- Reporting set of books check
549
550 END LOOP; -- Loop for associated SOBs.
551
552 -- ------------------------------------------------------------------------
553 -- Only commit the information if the caller has requested it to be.
554 -- ------------------------------------------------------------------------
555 IF FND_API.To_Boolean ( p_commit ) THEN
556 COMMIT WORK;
557 END iF;
558
559 -- ------------------------------------------------------------------------
560 -- Make sure that the cursor used is closed upon exit
561 -- ------------------------------------------------------------------------
562 IF (c_conversion_type%ISOPEN) THEN
563 CLOSE c_conversion_type;
564 END IF;
565
566 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
567 p_data => X_msg_data);
568
569 RETURN;
570
571
572 EXCEPTION
573
574 WHEN FND_API.G_EXC_ERROR THEN
575
576 ROLLBACK TO get_rsobs_Acct_Lines_PT;
577 X_return_status := FND_API.G_RET_STS_ERROR;
578 IF (c_conversion_type%ISOPEN) THEN
579 CLOSE c_conversion_type;
580 END IF;
581 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
582 p_data => X_msg_data);
583
584 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
585
586 ROLLBACK TO get_rsobs_Acct_Lines_PT;
587 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
588 IF (c_conversion_type%ISOPEN) THEN
589 CLOSE c_conversion_type;
590 END IF;
591 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
592 p_data => X_msg_data);
593
594 WHEN OTHERS THEN
595
596 ROLLBACK TO get_rsobs_Acct_Lines_PT;
597 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
598 IF (c_conversion_type%ISOPEN) THEN
599 CLOSE c_conversion_type;
600 END IF;
601
602 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
603 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
604 l_api_name);
605 END if;
606
607 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
608 p_data => X_msg_data);
609
610 END get_rsobs_Acct_Lines;
611
612
613 /* ================================================================================
614 PROCEDURE Insert_Row => IGC_CC_MC_DET_PF
615 ===============================================================================*/
616
617 PROCEDURE get_rsobs_DET_PF (
618 p_api_version IN NUMBER,
619 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
620 p_commit IN VARCHAR2 := FND_API.G_FALSE,
621 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
622 X_return_status OUT NOCOPY VARCHAR2,
623 X_msg_count OUT NOCOPY NUMBER,
624 X_msg_data OUT NOCOPY VARCHAR2,
625 p_CC_DET_PF_Line_Id IN NUMBER,
626 p_Set_Of_Books_Id IN NUMBER,
627 l_Application_Id IN NUMBER,
628 p_org_id IN NUMBER,
629 l_Conversion_Date IN DATE,
630 p_CC_Det_Pf_Func_Amt IN NUMBER,
631 p_CC_Det_Pf_ENCMBRNC_AMT IN NUMBER,
632 l_flag IN VARCHAR2
633 ) IS
634
635 l_sob_list gl_mc_info.r_sob_list := gl_mc_info.r_sob_list();
636 l_row_count NUMBER;
637 l_FROM_CURR varchar2(10);
638 l_TO_CURR varchar2(10);
639 l_Conversion_Rate Number;
640 l_Conversion_Type VARCHAR2(30);
641 l_CC_DET_PF_Func_Amt NUMBER;
642 l_CC_DET_PF_ENCMBRNC_AMT NUMBER;
643 l_rsob_id GL_ALC_LEDGER_RSHIPS_V.LEDGER_ID%TYPE;
644 l_rate_exists VARCHAR2(1);
645 l_api_name CONSTANT VARCHAR2(30) := 'get_rsobs_DET_PF';
646 l_api_version CONSTANT NUMBER := 1.0;
647 l_return_status VARCHAR2(1);
648 l_row_id VARCHAR2(18);
649 /* Commented below query and added below one for r12 MRC uptake for bug#6341012
650 CURSOR c_conversion_type IS
651 SELECT conversion_type
652 FROM gl_mc_reporting_options
653 WHERE primary_set_of_books_id = p_Set_Of_Books_Id
654 AND reporting_set_of_books_id = l_rsob_id
655 AND ORG_ID = p_Org_Id
656 AND application_id = l_Application_Id; */
657 CURSOR c_conversion_type IS
658 SELECT ALC_DEFAULT_CONV_RATE_TYPE
659 FROM GL_ALC_LEDGER_RSHIPS_V
660 WHERE primary_ledger_id = p_Set_Of_Books_Id
661 AND ledger_id = l_rsob_id
662 -- AND ORG_ID = p_Org_Id
663 AND application_id = l_Application_Id;
664 BEGIN
665
666 SAVEPOINT get_rsobs_DET_PF_PT;
667
668 IF NOT FND_API.Compatible_API_Call ( l_api_version,
669 p_api_version,
670 l_api_name,
671 G_PKG_NAME )
672 THEN
673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
674 END IF;
675
676
677 IF FND_API.to_Boolean (p_init_msg_list ) THEN
678 FND_MSG_PUB.initialize;
679 END IF;
680
681 X_return_status := FND_API.G_RET_STS_SUCCESS;
682
683 -- -------------------------------------------------------------------------
684 -- Obtain all sets of books associated to the Primary set of books ID
685 -- received from the caller.
686 -- -------------------------------------------------------------------------
687 gl_mc_info.get_associated_sobs (p_Set_Of_Books_Id,
688 l_Application_Id,
689 p_org_id,
690 NULL,
691 l_sob_list
692 );
693
694 l_row_count := l_sob_list.count;
695
696 -- -------------------------------------------------------------------------
697 -- Loop through all sets of books retrived and determine what the Primary
698 -- set of books currency has been defined to be.
699 -- -------------------------------------------------------------------------
700 FOR Rec in 1..l_row_count LOOP
701
702 IF (l_sob_list(Rec).r_sob_type = 'P') THEN
703 l_FROM_CURR := l_sob_list(Rec).r_sob_curr;
704 END IF;
705
706 END LOOP;
707
708 -- -------------------------------------------------------------------------
709 -- Loop through all the set of books retrieved if there were any defined.
710 -- -------------------------------------------------------------------------
711 FOR Rec1 in 1..l_row_count LOOP
712
713 -- -------------------------------------------------------------------------
714 -- Check to make sure that the Reporting set of books is being checked and
715 -- not the Primary set of books.
716 -- -------------------------------------------------------------------------
717 IF (l_sob_list(rec1).r_sob_type = 'R') THEN
718
719 l_rsob_id := l_sob_list(rec1).r_sob_id;
720 l_TO_CURR := l_sob_list(rec1).r_sob_curr;
721
722 -- -------------------------------------------------------------------------
723 -- Obtain the conversion type for the reporting set of books.
724 -- -------------------------------------------------------------------------
725 OPEN c_conversion_type;
726 FETCH c_conversion_type
727 INTO l_Conversion_Type;
728 CLOSE c_conversion_type;
729
730 -- -------------------------------------------------------------------------
731 -- Check to see if the conversion rate exists or not.
732 -- -------------------------------------------------------------------------
733 l_rate_exists := gl_currency_api.rate_exists (l_FROM_CURR,
734 l_TO_CURR,
735 l_Conversion_Date,
736 l_Conversion_Type
737 );
738
739 -- ------------------------------------------------------------------------
740 -- If the rate exists then obtain the rate to be inserted for the
741 -- reporting set of books.
742 -- ------------------------------------------------------------------------
743 IF (l_rate_exists = 'Y') THEN
744
745 l_Conversion_Rate := GL_CURRENCY_API.GET_RATE (l_FROM_CURR,
746 l_TO_CURR,
747 l_Conversion_Date,
748 l_Conversion_Type
749 );
750
751 l_CC_DET_PF_Func_Amt := GL_CURRENCY_API.CONVERT_AMOUNT (l_FROM_CURR,
752 l_TO_CURR,
753 l_CONVERSION_DATE,
754 l_CONVERSION_TYPE,
755 p_CC_DET_PF_Func_AMT
756 );
757
758 l_CC_DET_PF_ENCMBRNC_AMT := GL_CURRENCY_API.CONVERT_AMOUNT (l_FROM_CURR,
759 l_TO_CURR,
760 l_CONVERSION_DATE,
761 l_CONVERSION_TYPE,
762 p_CC_DET_PF_ENCMBRNC_AMT
763 );
764
765 -- ------------------------------------------------------------------------
766 -- If the rate exists then obtain the rate to be inserted for the
767 -- reporting set of books.
768 -- ------------------------------------------------------------------------
769 IF (l_flag = 'I') THEN
770
771 IGC_CC_MC_DET_PF_PKG.Insert_Row (
772 l_api_version,
773 FND_API.G_FALSE,
774 FND_API.G_FALSE,
775 FND_API.G_VALID_LEVEL_FULL,
776 l_return_status,
777 X_msg_count,
778 X_msg_data,
779 l_row_id,
780 p_CC_Det_PF_Line_Id,
781 l_sob_list(rec1).r_sob_id,
782 l_CC_Det_PF_Func_Amt,
783 l_CC_Det_PF_Encmbrnc_Amt,
784 l_Conversion_Type,
785 l_Conversion_Date,
786 l_conversion_Rate
787 );
788
789 -- ------------------------------------------------------------------
790 -- Make sure that the insertion was a success
791 -- ------------------------------------------------------------------
792 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
793 RAISE FND_API.G_EXC_ERROR;
794 END IF;
795
796 -- ----------------------------------------------------------------------
797 -- If delete is requested then delete the appropriate record in the MRC
798 -- table with the correlating information.
799 -- ----------------------------------------------------------------------
800 ELSIF (l_flag = 'D') THEN
801
802 -- ----------------------------------------------------------------------
803 -- Obtain the ROW ID that is to be updated.
804 -- ----------------------------------------------------------------------
805 SELECT rowid
806 INTO l_row_id
807 FROM igc_cc_mc_det_pf
808 WHERE cc_det_pf_line_id = p_CC_Det_PF_Line_Id
809 AND set_of_books_id = l_sob_list(rec1).r_sob_id;
810
811 IGC_CC_MC_DET_PF_PKG.Delete_Row (
812 l_api_version,
813 FND_API.G_FALSE,
814 FND_API.G_FALSE,
815 FND_API.G_VALID_LEVEL_FULL,
816 l_return_status,
817 X_msg_count,
818 X_msg_data,
819 l_row_id
820 );
821
822 -- ------------------------------------------------------------------
823 -- Make sure that the insertion was a success
824 -- ------------------------------------------------------------------
825 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
826 RAISE FND_API.G_EXC_ERROR;
827 END IF;
828
829 -- ----------------------------------------------------------------------
830 -- If Update is requested then update the appropriate record in the MRC
831 -- table with the correlating information.
832 -- ----------------------------------------------------------------------
833 ELSIF (l_flag = 'U') THEN
834
835 -- ----------------------------------------------------------------------
836 -- Obtain the ROW ID that is to be updated.
837 -- ----------------------------------------------------------------------
838 SELECT rowid
839 INTO l_row_id
840 FROM igc_cc_mc_det_pf
841 WHERE cc_det_pf_line_id = p_CC_Det_PF_Line_Id
842 AND set_of_books_id = l_sob_list(rec1).r_sob_id;
843
844 IGC_CC_MC_DET_PF_PKG.Update_Row (
845 l_api_version,
846 FND_API.G_FALSE,
847 FND_API.G_FALSE,
848 FND_API.G_VALID_LEVEL_FULL,
849 l_return_status,
850 X_msg_count,
851 X_msg_data,
852 l_row_id,
853 p_CC_Det_PF_Line_Id,
854 l_sob_list(rec1).r_sob_id,
855 l_CC_Det_PF_Func_Amt,
856 l_CC_Det_PF_Encmbrnc_Amt,
857 l_Conversion_Type,
858 l_Conversion_Date,
859 l_conversion_Rate
860 );
861
862 -- ------------------------------------------------------------------
863 -- Make sure that the insertion was a success
864 -- ------------------------------------------------------------------
865 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
866 RAISE FND_API.G_EXC_ERROR;
867 END IF;
868
869 ELSE
870
871 RAISE FND_API.G_EXC_ERROR;
872
873 END IF; -- Insert / Update Flag
874
875 END IF; -- Rate exists
876
877 END IF; -- Reporting set of books check
878
879 END LOOP; -- Loop for associated SOBs.
880
881 -- ------------------------------------------------------------------------
882 -- Only commit the information if the caller has requested it to be.
883 -- ------------------------------------------------------------------------
884 IF FND_API.To_Boolean ( p_commit ) THEN
885 COMMIT WORK;
886 END iF;
887
888 -- ------------------------------------------------------------------------
889 -- Make sure that the cursor used is closed upon exit
890 -- ------------------------------------------------------------------------
891 IF (c_conversion_type%ISOPEN) THEN
892 CLOSE c_conversion_type;
893 END IF;
894
895 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
896 p_data => X_msg_data);
897
898 RETURN;
899
900 EXCEPTION
901
902 WHEN FND_API.G_EXC_ERROR THEN
903
904 ROLLBACK TO get_rsobs_Headers_PT;
905 X_return_status := FND_API.G_RET_STS_ERROR;
906 IF (c_conversion_type%ISOPEN) THEN
907 CLOSE c_conversion_type;
908 END IF;
909 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
910 p_data => X_msg_data);
911
912 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
913
914 ROLLBACK TO get_rsobs_Headers_PT;
915 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
916 IF (c_conversion_type%ISOPEN) THEN
917 CLOSE c_conversion_type;
918 END IF;
919 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
920 p_data => X_msg_data);
921
922 WHEN OTHERS THEN
923
924 ROLLBACK TO get_rsobs_Headers_PT;
925 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
926 IF (c_conversion_type%ISOPEN) THEN
927 CLOSE c_conversion_type;
928 END IF;
929
930 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
931 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
932 l_api_name);
933 END if;
934
935 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
936 p_data => X_msg_data);
937
938 END get_rsobs_DET_PF;
939
940 END IGC_CC_MC_MAIN_PVT;
[Home] [Help]