[Home] [Help]
PACKAGE BODY: APPS.GL_HISTORICAL_RATES_PKG
Source
1 PACKAGE BODY GL_HISTORICAL_RATES_PKG as
2 /* $Header: glirthtb.pls 120.5.12000000.2 2007/08/28 15:59:35 dthakker ship $ */
3
4 PROCEDURE check_unique( x_rowid VARCHAR2,
5 x_ledger_id NUMBER,
6 x_code_combination_id NUMBER,
7 x_period_name VARCHAR2,
8 x_target_currency VARCHAR2,
9 x_usage_code VARCHAR2) IS
10 CURSOR c_dup IS
11 SELECT 'Duplicate'
12 FROM gl_historical_rates hist
13 WHERE hist.ledger_id = x_ledger_id
14 AND hist.code_combination_id = x_code_combination_id
15 AND hist.period_name = x_period_name
16 AND hist.target_currency = x_target_currency
17 AND hist.usage_code = x_usage_code
18 AND ( x_rowid is NULL
19 OR
20 hist.rowid <> x_rowid );
21 dummy VARCHAR2(100);
22
23 BEGIN
24 OPEN c_dup;
25 FETCH c_dup INTO dummy;
26
27 IF c_dup%FOUND THEN
28 CLOSE c_dup;
29 fnd_message.set_name( 'SQLGL', 'GL_DUPLICATE_HISTORICAL_RATE' );
30 app_exception.raise_exception;
31 END IF;
32
33 CLOSE c_dup;
34
35 EXCEPTION
36 WHEN app_exceptions.application_exception THEN
37 RAISE;
38 WHEN OTHERS THEN
39 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
40 fnd_message.set_token('PROCEDURE',
41 'GL_HISTORICAL_RATES_PKG.check_unique');
42 RAISE;
43
44 END check_unique;
45
46 -- **********************************************************************
47
48 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
49 X_Ledger_Id NUMBER,
50 X_Period_Name VARCHAR2,
51 X_Period_Num NUMBER,
52 X_Period_Year NUMBER,
53 X_Code_Combination_Id NUMBER,
54 X_Target_Currency VARCHAR2,
55 X_Update_Flag VARCHAR2,
56 X_Last_Update_Date DATE,
57 X_Last_Updated_By NUMBER,
58 X_Creation_Date DATE,
59 X_Created_By NUMBER,
60 X_Last_Update_Login NUMBER,
61 X_Rate_Type VARCHAR2,
62 X_Translated_Rate NUMBER,
63 X_Translated_Amount NUMBER,
64 X_Account_Type VARCHAR2,
65 X_Attribute1 VARCHAR2,
66 X_Attribute2 VARCHAR2,
67 X_Attribute3 VARCHAR2,
68 X_Attribute4 VARCHAR2,
69 X_Attribute5 VARCHAR2,
70 X_Context VARCHAR2,
71 X_Usage_Code VARCHAR2,
72 X_Chart_of_Accounts_Id NUMBER
73 ) IS
74 CURSOR C IS SELECT rowid FROM gl_historical_rates
75 WHERE ledger_id = X_Ledger_Id
76 AND code_combination_id = X_Code_Combination_Id
77 AND period_name = X_Period_Name
78 AND target_currency = X_Target_Currency
79 AND usage_code = X_Usage_Code;
80 BEGIN
81 INSERT INTO gl_historical_rates(
82 ledger_id,
83 period_name,
84 period_num,
85 period_year,
86 code_combination_id,
87 target_currency,
88 update_flag,
89 last_update_date,
90 last_updated_by,
91 creation_date,
92 created_by,
93 last_update_login,
94 rate_type,
95 translated_rate,
96 translated_amount,
97 account_type,
98 attribute1,
99 attribute2,
100 attribute3,
101 attribute4,
102 attribute5,
103 context,
104 usage_code
105 ) VALUES (
106 X_Ledger_Id,
107 X_Period_Name,
108 X_Period_Num,
109 X_Period_Year,
110 X_Code_Combination_Id,
111 X_Target_Currency,
112 X_Update_Flag,
113 X_Last_Update_Date,
114 X_Last_Updated_By,
115 X_Creation_Date,
116 X_Created_By,
117 X_Last_Update_Login,
118 X_Rate_Type,
119 X_Translated_Rate,
120 X_Translated_Amount,
121 X_Account_Type,
122 X_Attribute1,
123 X_Attribute2,
124 X_Attribute3,
125 X_Attribute4,
126 X_Attribute5,
127 X_Context,
128 X_Usage_Code
129
130 );
131
132 OPEN C;
133 FETCH C INTO X_Rowid;
134 if (C%NOTFOUND) then
135 CLOSE C;
136 RAISE NO_DATA_FOUND;
137 end if;
138 CLOSE C;
139
140 IF (X_Usage_Code = 'A') THEN
141 GL_DAILY_BALANCES_PKG.set_translated_flag (
142 X_Ledger_Id,
143 X_Code_Combination_Id,
144 X_Target_Currency,
145 X_Period_Year,
146 X_Period_Num,
147 X_Last_Updated_By,
148 X_Chart_of_Accounts_id,
149 X_Period_Name,
150 X_Usage_Code
151 );
152 ELSE
153 GL_BALANCES_PKG.set_translated_flag(
154 X_Ledger_Id,
155 X_Code_Combination_Id,
156 X_Target_Currency,
157 X_Period_Year,
158 X_Period_Num,
159 X_Last_Updated_By,
160 X_Chart_of_Accounts_Id,
161 X_Period_Name,
162 X_Usage_Code
163 );
164 END IF;
165
166 END Insert_Row;
167
168
169 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
170 X_Ledger_Id NUMBER,
171 X_Period_Name VARCHAR2,
172 X_Period_Num NUMBER,
173 X_Period_Year NUMBER,
174 X_Code_Combination_Id NUMBER,
175 X_Target_Currency VARCHAR2,
176 X_Update_Flag VARCHAR2,
177 X_Rate_Type VARCHAR2,
178 X_Translated_Rate NUMBER,
179 X_Translated_Amount NUMBER,
180 X_Account_Type VARCHAR2,
181 X_Attribute1 VARCHAR2,
182 X_Attribute2 VARCHAR2,
183 X_Attribute3 VARCHAR2,
184 X_Attribute4 VARCHAR2,
185 X_Attribute5 VARCHAR2,
186 X_Context VARCHAR2,
187 X_Usage_Code VARCHAR2
188 ) IS
189 CURSOR C IS
190 SELECT *
191 FROM gl_historical_rates
192 WHERE rowid = X_Rowid
193 FOR UPDATE of Ledger_Id NOWAIT;
194 Recinfo C%ROWTYPE;
195 BEGIN
196 OPEN C;
197 FETCH C INTO Recinfo;
198 if (C%NOTFOUND) then
199 CLOSE C;
200 RAISE NO_DATA_FOUND;
201 end if;
202 CLOSE C;
203 if (
204 ( (Recinfo.ledger_id = X_Ledger_Id)
205 OR ( (Recinfo.ledger_id IS NULL)
206 AND (X_Ledger_Id IS NULL)))
207 AND ( (Recinfo.period_name = X_Period_Name)
208 OR ( (Recinfo.period_name IS NULL)
209 AND (X_Period_Name IS NULL)))
210 AND ( (Recinfo.period_num = X_Period_Num)
211 OR ( (Recinfo.period_num IS NULL)
212 AND (X_Period_Num IS NULL)))
213 AND ( (Recinfo.period_year = X_Period_Year)
214 OR ( (Recinfo.period_year IS NULL)
215 AND (X_Period_Year IS NULL)))
216 AND ( (Recinfo.code_combination_id = X_Code_Combination_Id)
217 OR ( (Recinfo.code_combination_id IS NULL)
218 AND (X_Code_Combination_Id IS NULL)))
219 AND ( (Recinfo.target_currency = X_Target_Currency)
220 OR ( (Recinfo.target_currency IS NULL)
221 AND (X_Target_Currency IS NULL)))
222 AND ( (Recinfo.update_flag = X_Update_Flag)
223 OR ( (Recinfo.update_flag IS NULL)
224 AND (X_Update_Flag IS NULL)))
225 AND ( (Recinfo.rate_type = X_Rate_Type)
226 OR ( (Recinfo.rate_type IS NULL)
227 AND (X_Rate_Type IS NULL)))
228 AND ( (Recinfo.translated_rate = X_Translated_Rate)
229 OR ( (Recinfo.translated_rate IS NULL)
230 AND (X_Translated_Rate IS NULL)))
231 AND ( (Recinfo.translated_amount = X_Translated_Amount)
232 OR ( (Recinfo.translated_amount IS NULL)
233 AND (X_Translated_Amount IS NULL)))
234 AND ( (Recinfo.account_type = X_Account_Type)
235 OR ( (Recinfo.account_type IS NULL)
236 AND (X_Account_Type IS NULL)))
237 AND ( (Recinfo.attribute1 = X_Attribute1)
238 OR ( (Recinfo.attribute1 IS NULL)
239 AND (X_Attribute1 IS NULL)))
240 AND ( (Recinfo.attribute2 = X_Attribute2)
241 OR ( (Recinfo.attribute2 IS NULL)
242 AND (X_Attribute2 IS NULL)))
243 AND ( (Recinfo.attribute3 = X_Attribute3)
244 OR ( (Recinfo.attribute3 IS NULL)
245 AND (X_Attribute3 IS NULL)))
246 AND ( (Recinfo.attribute4 = X_Attribute4)
247 OR ( (Recinfo.attribute4 IS NULL)
248 AND (X_Attribute4 IS NULL)))
249 AND ( (Recinfo.attribute5 = X_Attribute5)
250 OR ( (Recinfo.attribute5 IS NULL)
251 AND (X_Attribute5 IS NULL)))
252 AND ( (Recinfo.context = X_Context)
253 OR ( (Recinfo.context IS NULL)
254 AND (X_Context IS NULL)))
255 AND ( (Recinfo.usage_code = X_Usage_Code)
256 OR ( (Recinfo.usage_code IS NULL)
257 AND (X_Usage_Code IS NULL)))
258 ) then
259 return;
260 else
261 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
262 APP_EXCEPTION.RAISE_EXCEPTION;
263 end if;
264 END Lock_Row;
265
266
267 PROCEDURE Update_Row(X_Rowid VARCHAR2,
268 X_Ledger_Id NUMBER,
269 X_Period_Name VARCHAR2,
270 X_Period_Num NUMBER,
271 X_Period_Year NUMBER,
272 X_Code_Combination_Id NUMBER,
273 X_Target_Currency VARCHAR2,
274 X_Update_Flag VARCHAR2,
275 X_Last_Update_Date DATE,
276 X_Last_Updated_By NUMBER,
277 X_Last_Update_Login NUMBER,
278 X_Rate_Type VARCHAR2,
279 X_Translated_Rate NUMBER,
280 X_Translated_Amount NUMBER,
281 X_Account_Type VARCHAR2,
282 X_Attribute1 VARCHAR2,
283 X_Attribute2 VARCHAR2,
284 X_Attribute3 VARCHAR2,
285 X_Attribute4 VARCHAR2,
286 X_Attribute5 VARCHAR2,
287 X_Context VARCHAR2,
288 X_Usage_Code VARCHAR2,
289 X_Chart_of_Accounts_Id NUMBER
290 ) IS
291 BEGIN
292 UPDATE gl_historical_rates
293 SET
294
295 ledger_id = X_Ledger_Id,
296 period_name = X_Period_Name,
297 period_num = X_Period_Num,
298 period_year = X_Period_Year,
299 code_combination_id = X_Code_Combination_Id,
300 target_currency = X_Target_Currency,
301 update_flag = X_Update_Flag,
302 last_update_date = X_Last_Update_Date,
303 last_updated_by = X_Last_Updated_By,
304 last_update_login = X_Last_Update_Login,
305 rate_type = X_Rate_Type,
306 translated_rate = X_Translated_Rate,
307 translated_amount = X_Translated_Amount,
308 account_type = X_Account_Type,
309 attribute1 = X_Attribute1,
310 attribute2 = X_Attribute2,
311 attribute3 = X_Attribute3,
312 attribute4 = X_Attribute4,
313 attribute5 = X_Attribute5,
314 context = X_Context,
315 usage_code = X_Usage_Code
316 WHERE rowid = X_rowid;
317
318 if (SQL%NOTFOUND) then
319 RAISE NO_DATA_FOUND;
320 end if;
321
322 IF (X_Usage_Code = 'A') THEN
323 GL_DAILY_BALANCES_PKG.set_translated_flag (
324 X_Ledger_Id,
325 X_Code_Combination_Id,
326 X_Target_Currency,
327 X_Period_Year,
328 X_Period_Num,
329 X_Last_Updated_By,
330 X_Chart_of_Accounts_id,
331 X_Period_Name,
332 X_Usage_Code
333 );
334 ELSE
335 GL_BALANCES_PKG.set_translated_flag(
336 X_Ledger_Id,
337 X_Code_Combination_Id,
338 X_Target_Currency,
339 X_Period_Year,
340 X_Period_Num,
341 X_Last_Updated_By,
342 X_Chart_of_Accounts_id,
343 X_Period_Name,
344 X_Usage_Code
345 );
346 END IF;
347
348 END Update_Row;
349
350
351 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
352 BEGIN
353 DELETE FROM gl_historical_rates
354 WHERE rowid = X_Rowid;
355
356 if (SQL%NOTFOUND) then
357 RAISE NO_DATA_FOUND;
358 end if;
359
360 END Delete_Row;
361
362
363 FUNCTION valdiate_seg_2(
364 x_chart_of_accounts_id NUMBER,
365 x_concat_segments VARCHAR2)
366 RETURN NUMBER IS
367 BEGIN
368 IF fnd_flex_keyval.validate_segs('CHECK_SEGMENTS', 'SQLGL', 'GL#',
369 x_chart_of_accounts_id,
370 x_concat_segments, 'V', SYSDATE, NULL,
371 NULL, NULL, NULL, NULL, TRUE, TRUE,
372 fnd_global.resp_appl_id,
373 fnd_global.resp_id,
374 fnd_global.user_id, NULL, NULL, NULL) THEN
375 RETURN 0;
376 ELSE
377 RETURN 1;
378 END IF;
379 END valdiate_seg_2;
380
381 -- **********************************************************************
382 FUNCTION valdiate_seg(
383 x_chart_of_accounts_id NUMBER,
384 x_combination_id NUMBER)
385 RETURN NUMBER IS
386 BEGIN
387 IF fnd_flex_keyval.validate_ccid('SQLGL', 'GL#',
388 x_chart_of_accounts_id,
389 x_combination_id, NULL, NULL, NULL,
390 'ENFORCE', NULL,
391 fnd_global.resp_appl_id,
392 fnd_global.resp_id,
393 fnd_global.user_id, NULL) THEN
394 RETURN 0;
395 ELSE
396 RETURN 1;
397 END IF;
398 END valdiate_seg;
399
400 -- **********************************************************************
401 FUNCTION get_bal_seg(
402 x_chart_of_accounts_id NUMBER)
403 RETURN NUMBER IS
404 CURSOR c_get_bal_seg_column_name IS
405 SELECT s.application_column_name, s.segment_num
406 FROM fnd_id_flex_segments s, fnd_segment_attribute_values v
407 WHERE s.application_id = v.application_id
408 AND s.id_flex_code = v.id_flex_code
409 AND s.id_flex_num = v.id_flex_num
410 AND s.application_column_name = v.application_column_name
411 AND v.application_id = 101
412 AND v.id_flex_code = 'GL#'
413 AND v.id_flex_num = x_chart_of_accounts_id
414 AND v.segment_attribute_type = 'GL_BALANCING'
415 AND v.attribute_value = 'Y';
416
417 v_bal_seg_column_name VARCHAR2(30);
418 v_bal_seg_number NUMBER(3, 0);
419 BEGIN
420 OPEN c_get_bal_seg_column_name;
421
422 FETCH c_get_bal_seg_column_name
423 INTO v_bal_seg_column_name, v_bal_seg_number;
424
425 IF c_get_bal_seg_column_name%FOUND THEN
426 CLOSE c_get_bal_seg_column_name;
427
428 RETURN v_bal_seg_number;
429 ELSE
430 CLOSE c_get_bal_seg_column_name;
431
432 v_bal_seg_number := 0;
433 RETURN v_bal_seg_number;
434 END IF;
435 EXCEPTION
436 WHEN app_exceptions.application_exception THEN
437 RAISE;
438 WHEN OTHERS THEN
439 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
440 fnd_message.set_token('PROCEDURE',
441 'GL_HISTORICAL_RATES_PKG.get_bal_seg');
442 RAISE;
443 END get_bal_seg;
444
445 -- **********************************************************************
446 PROCEDURE Insert_Row_WebADI_Wrapper(
447 X_Ledger IN VARCHAR2,
448 X_Functional_Currency IN VARCHAR2,
449 X_Target_Currency IN VARCHAR2,
450 X_PERIOD_NAME IN VARCHAR2,
451 X_Value_Type IN VARCHAR2,
452 X_Value IN NUMBER,
453 X_Rate_Type IN VARCHAR2,
454 X_Usage_Code IN VARCHAR2,
455 X_Segment1 IN VARCHAR2,
456 X_Segment2 IN VARCHAR2,
457 X_Segment3 IN VARCHAR2,
458 X_Segment4 IN VARCHAR2,
459 X_Segment5 IN VARCHAR2,
460 X_Segment6 IN VARCHAR2,
461 X_Segment7 IN VARCHAR2,
462 X_Segment8 IN VARCHAR2,
463 X_Segment9 IN VARCHAR2,
464 X_Segment10 IN VARCHAR2,
465 X_Segment11 IN VARCHAR2,
466 X_Segment12 IN VARCHAR2,
467 X_Segment13 IN VARCHAR2,
468 X_Segment14 IN VARCHAR2,
469 X_Segment15 IN VARCHAR2,
470 X_Segment16 IN VARCHAR2,
471 X_Segment17 IN VARCHAR2,
472 X_Segment18 IN VARCHAR2,
473 X_Segment19 IN VARCHAR2,
474 X_Segment20 IN VARCHAR2,
475 X_Segment21 IN VARCHAR2,
476 X_Segment22 IN VARCHAR2,
477 X_Segment23 IN VARCHAR2,
478 X_Segment24 IN VARCHAR2,
479 X_Segment25 IN VARCHAR2,
480 X_Segment26 IN VARCHAR2,
481 X_Segment27 IN VARCHAR2,
482 X_Segment28 IN VARCHAR2,
483 X_Segment29 IN VARCHAR2,
484 X_Segment30 IN VARCHAR2) IS
485 X_Rowid VARCHAR2(30);
486
487 l_ledger_id NUMBER;
488 l_access_set_id NUMBER;
489 l_access_count NUMBER;
490 V_sysdate_str VARCHAR2(30);
491 X_Translated_Rate NUMBER;
492 X_Translated_Amount NUMBER;
493 V_Rate_Type VARCHAR2(1);
494 V_Usage_Code VARCHAR2(1);
495 X_Period_Num NUMBER(15);
496 X_Period_Year NUMBER(15);
497 X_Update_Flag VARCHAR2(1);
498 X_Account_Type VARCHAR2(30);
499 X_Last_Update_Date DATE;
500 X_Last_Updated_By NUMBER;
501 X_Creation_Date DATE;
502 X_Created_By NUMBER;
503 X_Last_Update_Login NUMBER;
504 X_Attribute1 VARCHAR2(150);
505 X_Attribute2 VARCHAR2(150);
506 X_Attribute3 VARCHAR2(150);
507 X_Attribute4 VARCHAR2(150);
508 X_Attribute5 VARCHAR2(150);
509 X_Context VARCHAR2(150);
510 X_Chart_of_Accounts_Id NUMBER;
511 dummy NUMBER;
512 v_delimiter VARCHAR2(1);
513 v_segments_array SegmentArray;
514 v_segments_num NUMBER;
515 v_conc_segmetns VARCHAR2(2000);
516 X_Code_Combination_Id NUMBER;
517 str FND_FLEX_SERVER1.StringArray;
518
519 -- 6058949 Added below cursor to retrieve segment order for
520 -- the structure
521 Cursor seg_order(flex_num number) is
522 select
523 s.segment_name segment,
524 s.application_column_name app_col,
525 s.segment_num num
526 from
527 fnd_id_flex_segments_vl s
528 where
529 s.id_flex_num = flex_num
530 and s.application_id = 101
531 and s.id_flex_code = 'GL#'
532 order by s.segment_num;
533
534 j NUMBER := 1;
535
536 BEGIN
537 SELECT ledger_id
538 INTO l_ledger_id
539 FROM gl_ledgers
540 WHERE NAME = X_ledger;
541
542 fnd_profile.get('GL_ACCESS_SET_ID', l_access_set_id);
543
544 --
545 -- Get count from access sets
546 --
547 select count(*)
548 into l_access_count
549 from gl_access_set_ledgers_v
550 where access_set_id = l_access_set_id
551 and object_type_code = 'L'
552 and access_privilege_code = 'F';
553
554
555
556 IF l_access_count = 0 THEN
557 app_exception.raise_exception;
558 END IF;
559
560 SELECT TO_CHAR(SYSDATE, 'DD-Mon-YYYY')
561 INTO V_sysdate_str
562 FROM DUAL;
563
564 IF (X_Value_Type = 'Rate') THEN
565 X_Translated_Rate := X_Value;
566 ELSE
567 X_Translated_Amount :=
568 gl_mc_currency_pkg.CurrRound(X_Value, X_Target_Currency);
569 END IF;
570
571 /*IF X_Translated_Rate < 0 THEN
572 app_exception.raise_exception;
573 END IF;
574 Per GL team, the rate could be negative now.
575 */
576 SELECT rate_type
577 INTO V_Rate_Type
578 FROM gl_lookups_rate_type_v
579 WHERE X_Rate_Type = show_rate_type;
580
581 SELECT DECODE(lookup_code, 'Average', 'A', 'S')
582 INTO V_Usage_Code
583 FROM gl_lookups
584 WHERE lookup_type = 'GL_HIST_RATES_USAGE' AND meaning = X_Usage_Code;
585
586 SELECT period_year, period_num
587 INTO X_Period_Year, X_Period_Num
588 FROM GL_PERIOD_STATUSES
589 WHERE application_id = 101
590 AND ledger_id = l_ledger_id
591 AND period_name = X_PERIOD_NAME;
592
593 X_Update_Flag := 'N';
594 X_Last_Update_Date := SYSDATE;
595 X_Last_Updated_By := fnd_global.user_id;
596 X_Creation_Date := SYSDATE;
597 X_Created_By := fnd_global.user_id;
598 X_Last_Update_Login := fnd_global.login_id;
599 X_Attribute1 := NULL;
600 X_Attribute2 := NULL;
601 X_Attribute3 := NULL;
602 X_Attribute4 := NULL;
603 X_Attribute5 := NULL;
604 X_Context := NULL;
605
606 SELECT chart_of_accounts_id
607 INTO X_Chart_of_Accounts_Id
608 FROM gl_ledgers
609 WHERE ledger_id = l_ledger_id;
610
611 -- get COA ID
612 v_delimiter :=
613 fnd_flex_ext.get_delimiter('SQLGL', 'GL#', X_Chart_of_Accounts_Id);
614 -- get delimiter
615 v_segments_array(1) := X_Segment1;
616 v_segments_array(2) := X_Segment2;
617 v_segments_array(3) := X_Segment3;
618 v_segments_array(4) := X_Segment4;
619 v_segments_array(5) := X_Segment5;
620 v_segments_array(6) := X_Segment6;
621 v_segments_array(7) := X_Segment7;
622 v_segments_array(8) := X_Segment8;
623 v_segments_array(9) := X_Segment9;
624 v_segments_array(10) := X_Segment10;
625 v_segments_array(11) := X_Segment11;
626 v_segments_array(12) := X_Segment12;
627 v_segments_array(13) := X_Segment13;
628 v_segments_array(14) := X_Segment14;
629 v_segments_array(15) := X_Segment15;
630 v_segments_array(16) := X_Segment16;
631 v_segments_array(17) := X_Segment17;
632 v_segments_array(18) := X_Segment18;
633 v_segments_array(19) := X_Segment19;
634 v_segments_array(20) := X_Segment20;
635 v_segments_array(21) := X_Segment21;
636 v_segments_array(22) := X_Segment22;
637 v_segments_array(23) := X_Segment23;
638 v_segments_array(24) := X_Segment24;
639 v_segments_array(25) := X_Segment25;
640 v_segments_array(26) := X_Segment26;
641 v_segments_array(27) := X_Segment27;
642 v_segments_array(28) := X_Segment28;
643 v_segments_array(29) := X_Segment29;
644 v_segments_array(30) := X_Segment30;
645
646 SELECT COUNT(segment_num)
647 INTO v_segments_num
648 FROM fnd_id_flex_segments
649 WHERE application_id = 101
650 AND id_flex_code = 'GL#'
651 AND id_flex_num = X_Chart_of_Accounts_Id;
652
653 IF (v_segments_num = 1) THEN
654 v_conc_segmetns := v_segments_array(1);
655 ELSE
656
657 -- 6058949 added below logic to populate str array in
658 -- segment array
659 FOR seg_order_rec in seg_order(X_Chart_of_Accounts_Id)
660 LOOP
661 IF seg_order_rec.app_col = 'SEGMENT1' THEN
662 str(j) := v_segments_array(1);
663 ELSIF seg_order_rec.app_col = 'SEGMENT2' THEN
664 str(j) := v_segments_array(2);
665 ELSIF seg_order_rec.app_col = 'SEGMENT3' THEN
666 str(j) := v_segments_array(3);
667 ELSIF seg_order_rec.app_col = 'SEGMENT4' THEN
668 str(j) := v_segments_array(4);
669 ELSIF seg_order_rec.app_col = 'SEGMENT5' THEN
670 str(j) := v_segments_array(5);
671 ELSIF seg_order_rec.app_col = 'SEGMENT6' THEN
672 str(j) := v_segments_array(6);
673 ELSIF seg_order_rec.app_col = 'SEGMENT7' THEN
674 str(j) := v_segments_array(7);
675 ELSIF seg_order_rec.app_col = 'SEGMENT8' THEN
676 str(j) := v_segments_array(8);
677 ELSIF seg_order_rec.app_col = 'SEGMENT9' THEN
678 str(j) := v_segments_array(9);
679 ELSIF seg_order_rec.app_col = 'SEGMENT10' THEN
680 str(j) := v_segments_array(10);
681 ELSIF seg_order_rec.app_col = 'SEGMENT11' THEN
682 str(j) := v_segments_array(11);
683 ELSIF seg_order_rec.app_col = 'SEGMENT12' THEN
684 str(j) := v_segments_array(12);
685 ELSIF seg_order_rec.app_col = 'SEGMENT13' THEN
686 str(j) := v_segments_array(13);
687 ELSIF seg_order_rec.app_col = 'SEGMENT14' THEN
688 str(j) := v_segments_array(14);
689 ELSIF seg_order_rec.app_col = 'SEGMENT15' THEN
690 str(j) := v_segments_array(15);
691 ELSIF seg_order_rec.app_col = 'SEGMENT16' THEN
692 str(j) := v_segments_array(16);
693 ELSIF seg_order_rec.app_col = 'SEGMENT17' THEN
694 str(j) := v_segments_array(17);
695 ELSIF seg_order_rec.app_col = 'SEGMENT18' THEN
696 str(j) := v_segments_array(18);
697 ELSIF seg_order_rec.app_col = 'SEGMENT19' THEN
698 str(j) := v_segments_array(19);
699 ELSIF seg_order_rec.app_col = 'SEGMENT20' THEN
700 str(j) := v_segments_array(20);
701 ELSIF seg_order_rec.app_col = 'SEGMENT21' THEN
702 str(j) := v_segments_array(21);
703 ELSIF seg_order_rec.app_col = 'SEGMENT22' THEN
704 str(j) := v_segments_array(22);
705 ELSIF seg_order_rec.app_col = 'SEGMENT23' THEN
706 str(j) := v_segments_array(23);
707 ELSIF seg_order_rec.app_col = 'SEGMENT24' THEN
708 str(j) := v_segments_array(24);
709 ELSIF seg_order_rec.app_col = 'SEGMENT25' THEN
710 str(j) := v_segments_array(25);
711 ELSIF seg_order_rec.app_col = 'SEGMENT26' THEN
712 str(j) := v_segments_array(26);
713 ELSIF seg_order_rec.app_col = 'SEGMENT27' THEN
714 str(j) := v_segments_array(27);
715 ELSIF seg_order_rec.app_col = 'SEGMENT28' THEN
716 str(j) := v_segments_array(28);
717 ELSIF seg_order_rec.app_col = 'SEGMENT29' THEN
718 str(j) := v_segments_array(29);
719 ELSIF seg_order_rec.app_col = 'SEGMENT30' THEN
720 str(j) := v_segments_array(30);
721 ELSE
722 app_exception.raise_exception;
723 END IF;
724
725 j := j + 1;
726
727 END LOOP;
728
729 -- Commented below logic which was creating the str
730 -- array without considering the segment order
731 /*
732 FOR i IN 1 .. v_segments_num LOOP
733 IF v_segments_array(i) IS NOT NULL THEN
734 str(i) := v_segments_array(i);
735 ELSE
736 app_exception.raise_exception;
737 END IF;
738 END LOOP;
739 */
740
741 -- 6058949 end
742
743 v_conc_segmetns :=
744 FND_FLEX_SERVER1.from_stringarray(v_segments_num, str,
745 v_delimiter);
746 END IF;
747
748 -- get CCID
749 X_Code_Combination_Id :=
750 fnd_flex_ext.get_ccid('SQLGL', 'GL#', X_Chart_of_Accounts_Id,
751 V_sysdate_str, v_conc_segmetns);
752 GL_CODE_COMBINATIONS_PKG.select_columns(X_Code_Combination_Id,
753 X_Account_Type, dummy);
754
755 DELETE FROM gl_historical_rates
756 WHERE ledger_id = l_ledger_id
757 AND code_combination_id = X_Code_Combination_Id
758 AND period_name = X_PERIOD_NAME
759 AND target_currency = X_Target_Currency
760 AND usage_code = V_Usage_Code;
761
762 GL_HISTORICAL_RATES_PKG.Insert_Row(X_Rowid, l_ledger_id,
763 X_PERIOD_NAME, X_Period_Num,
764 X_Period_Year, X_Code_Combination_Id,
765 X_Target_Currency, X_Update_Flag,
766 X_Last_Update_Date,
767 X_Last_Updated_By, X_Creation_Date,
768 X_Created_By, X_Last_Update_Login,
769 V_Rate_Type, X_Translated_Rate,
770 X_Translated_Amount, X_Account_Type,
771 X_Attribute1, X_Attribute2,
772 X_Attribute3, X_Attribute4,
773 X_Attribute5, X_Context,
774 V_Usage_Code, X_Chart_of_Accounts_Id);
775 END Insert_Row_WebADI_Wrapper;
776
777
778 END GL_HISTORICAL_RATES_PKG;