[Home] [Help]
PACKAGE BODY: APPS.JA_CN_UTILITY
Source
1 PACKAGE BODY Ja_Cn_Utility AS
2 --$Header: JACNCUYB.pls 120.2.12010000.3 2008/11/04 02:41:42 shyan ship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================
8 --| FILENAME
9 --| JACNCUYB.pls
10 --|
11 --| DESCRIPTION
12 --|
13 --| This package is to provide share procedures for CNAO programs
14 --|
15 --| PROCEDURE LIST
16 --|
17 --| FUNCTION Check_Profile
18 --| PROCEDURE Get_SOB_And_COA
19 --| FUNCTION Get_SOB
20 --| FUNCTION Get_COA
21 --| FUNCTION Get_Lookup_Meaning
22 --| FUNCTION Check_Nat_Number
23 --| PROCEDURE OUtput_Conc
24 --| FUNCTION Check_Account_Level
25 --| PROCEDURE Change_Output_Filename
26 --| PROCEDURE Submit_Charset_Conversion
27 --| FUNCTION Get_Lookup_Code
28 --| HISTORY
29 --| 02-Mar-2006 Donghai Wang Created
30 --| 09-Mar-2006 Joseph Wang added the function Get_Chart_Of_Account_ID
31 --| 21-Mar-2006 Joseph Wang replace the function Get_Chart_Of_Account_ID by
32 --| procedure Get_SOB_And_COA to make it return both Set Of Book
33 --| and Chart Of Account ID
34 --| 31-Mar-2006 Joseph Wang added functions Get_SOB and Get_COA
35 --| 11-Apr-2006 Jackey Li added functions Get_Lookup_Meaning and Check_Nat_Number
36 --| 27-Apr-2006 Andrew Liu added Procedure Output_Conc
37 --| 18-May-2006 Andrew Liu added function Check_Account_Level
38 --| 30-May-2006 Donghai Wang added the new function Check_Cash_Related_Account
39 --| 20-Jun-2006 Shujuan Yan added the new procedure Change_Output_Filename,
40 --| Get_Lookup_Code and Submit_Charset_Conversion
41 --| 04-July-2006 Joseph Wang added the function Check_Accounting_Period_Range
42 --| 29-Aug-2008 Chaoqun Wu added the function Get_Balancing_Segment_Value
43 --| 1-Sep-2008 Chaoqun Wu added the function Get_Balancing_Segment_Value
44 --| 31-OCT-2008: Yao Zhang Fix bug 7524912 changed
45 --+======================================================================*//
46
47 l_Module_Prefix VARCHAR2(100) := 'JA_CN_UTILITY';
48
49 --==========================================================================
50 -- FUNCTION NAME:
51 --
52 -- Check_Profile Public
53 --
54 -- DESCRIPTION:
55 --
56 -- This function is used to check if all required profiles has been properly set
57 -- for current responsibility. If No, the function will return FALSE to caller and .
58 -- raise error message. Those required profiles include ' JG: Product', which should
59 -- be set to 'Asia/Pacific Localizations','JG: Territory', which should be set
60 -- to 'China' and 'JA: CNAO Legal Entity', which should be NOT NULL
61 --
62 --
63 -- PARAMETERS:
64 -- In:
65 --
66 --
67 -- DESIGN REFERENCES:
68 --
69 --
70 -- CHANGE HISTORY:
71 --
72 -- 02-Mar-2006 Donghai Wang Created
73 --
74 --===========================================================================
75
76 FUNCTION Check_Profile RETURN BOOLEAN IS
77 l_False_Flag VARCHAR2(1) := 'N';
78 l_Error_Msg VARCHAR2(2000);
79 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
80 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
81 l_Proc_Name VARCHAR2(100) := 'Check_Profile';
82 BEGIN
83
84 --log for debug
85 IF (l_Proc_Level >= l_Dbg_Level)
86 THEN
87 Fnd_Log.STRING(l_Proc_Level,
88 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
89 'Enter procedure');
90 END IF; --(l_proc_level >= l_dbg_level)
91
92 --To check if the profile JG: Product' is set to 'Asia/Pacific Localizations'
93 --and the profile 'JG: Territory' is set to 'China'
94 IF (Fnd_Profile.VALUE(NAME => 'JGZZ_PRODUCT_CODE') <> 'JA' OR
95 Fnd_Profile.VALUE(NAME => 'JGZZ_PRODUCT_CODE') IS NULL)
96 OR (Fnd_Profile.VALUE(NAME => 'JGZZ_COUNTRY_CODE') <> 'CN' OR
97 Fnd_Profile.VALUE(NAME => 'JGZZ_COUNTRY_CODE') IS NULL)
98 THEN
99 --Raise error message for caller
100 l_False_Flag := 'Y';
101 Fnd_Message.Set_Name(Application => 'JA',
102 NAME => 'JA_CN_PROFILE_NOT_ENABLE');
103 l_Error_Msg := Fnd_Message.Get;
104
105 --Output error message
106 Fnd_File.Put_Line(Fnd_File.Output, l_Error_Msg);
107 END IF;
108
109 --To check if current responsibility has profile 'JA: CNAO Legal Entity'
110 IF Fnd_Profile.VALUE(NAME => 'JA_CN_LEGAL_ENTITY') IS NULL
111 THEN
112 --Raise error message for caller
113 l_False_Flag := 'Y';
114 Fnd_Message.Set_Name(Application => 'JA',
115 NAME => 'JA_CN_NO_LEGAL_ENTITY');
116 l_Error_Msg := Fnd_Message.Get;
117
118 --Output error message
119 Fnd_File.Put_Line(Fnd_File.Output, l_Error_Msg);
120 END IF; -- FND_PROFILE.Value(NAME => 'JA_CN_LEGAL_ENTITY')IS NULL
121
122 IF l_False_Flag = 'N'
123 THEN
124 --log for debug
125 IF (l_Proc_Level >= l_Dbg_Level)
126 THEN
127 Fnd_Log.STRING(l_Proc_Level,
128 l_Module_Prefix || '.' || l_Proc_Name || '.end',
129 'Exit procedure');
130 END IF; --( l_proc_level >= l_dbg_level )
131 RETURN TRUE;
132 ELSE
133 --log for debug
134 IF (l_Proc_Level >= l_Dbg_Level)
135 THEN
136 Fnd_Log.STRING(l_Proc_Level,
137 l_Module_Prefix || '.' || l_Proc_Name || '.end',
138 'Exit procedure');
139 END IF; --( l_proc_level >= l_dbg_level )
140 RETURN FALSE;
141 END IF; --l_false_flage='N'
142
143 EXCEPTION
144 WHEN OTHERS THEN
145 IF (l_Proc_Level >= l_Dbg_Level)
146 THEN
147 Fnd_Log.STRING(l_Proc_Level,
148 l_Module_Prefix || '.' || l_Proc_Name ||
149 '. Other_Exception ',
150 SQLCODE || ':' || SQLERRM);
151 END IF; --(l_proc_level >= l_dbg_level)
152 END Check_Profile;
153 --==========================================================================
154 -- PROCEDURE NAME:
155 --
156 -- Get_SOB_And_COA Public
157 --
158 -- DESCRIPTION:
159 --
160 -- This function is used to get chart of account id and set of book id
161 -- by legal entity, if no data found or exception occurs, x_flag will be
162 -- returned with -1
163 --
164 --
165 --
166 -- PARAMETERS:
167 -- In: p_legal_entity_id Legal entity ID
168 -- Out: x_sob_id Set of book ID
169 -- Out: x_coa_id Chart of account ID
170 -- Out: x_flag Return flag
171 --
172 -- RETURN:
173 -- Flag, -1 for abnormal cases.
174 --
175 -- DESIGN REFERENCES:
176 --
177 --
178 -- CHANGE HISTORY:
179 --
180 -- 09-Mar-2006 Joseph Wang Created
181 --
182 --===========================================================================
183
184 PROCEDURE Get_Sob_And_Coa(p_Legal_Entity_Id NUMBER,
185 x_Sob_Id OUT NOCOPY NUMBER,
186 x_Coa_Id OUT NOCOPY NUMBER,
187 x_Flag OUT NOCOPY NUMBER) IS
188
189 l_Module_Name CONSTANT VARCHAR2(100) := l_Module_Prefix ||
190 '.Get_SOB_And_COA';
191 l_Runtime_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
192 l_Procedure_Level NUMBER := Fnd_Log.Level_Procedure;
193 l_Statement_Level NUMBER := Fnd_Log.Level_Statement;
194 l_Exception_Level NUMBER := Fnd_Log.Level_Exception;
195 l_Message VARCHAR2(300);
196
197 l_Chart_Of_Accounts_Id Gl_Sets_Of_Books.Chart_Of_Accounts_Id%TYPE;
198 l_Ledger_Id Gl_Ledgers.Ledger_Id%TYPE;
199 BEGIN
200
201 --log the parameters
202 IF (l_Procedure_Level >= l_Runtime_Level)
203 THEN
204
205 Fnd_Log.STRING(l_Procedure_Level,
206 l_Module_Name,
207 'Start to run ' || l_Module_Name ||
208 'with parameter: p_legal_entity_id=' ||
209 Nvl(To_Char(p_Legal_Entity_Id), 'null'));
210
211 END IF; --l_procedure_level >= l_runtime_level
212
213 BEGIN
214 /* SELECT l_ledger_id
215 INTO l_ledger_id
216 FROM ja_cn_system_parameters_all
217 WHERE legal_entity_id = p_legal_entity_id;
218 */ --log the SOB
219 IF (l_Statement_Level >= l_Runtime_Level)
220 THEN
221 Fnd_Log.STRING(l_Statement_Level,
222 l_Module_Name,
223 'Fetched: l_set_of_books_id=' ||
224 Nvl(To_Char(l_Ledger_Id), 'null'));
225 END IF; --l_statement_level >= l_runtime_level
226
227 EXCEPTION
228 WHEN No_Data_Found THEN
229 Fnd_Message.Set_Name('JA', 'JA_CN_MISSING_BOOK_INFO');
230 l_Message := Fnd_Message.Get();
231 Fnd_File.Put_Line(Fnd_File.Output, l_Message);
232 IF (l_Exception_Level >= l_Runtime_Level)
233 THEN
234 Fnd_Log.STRING(l_Exception_Level, l_Module_Name, l_Message);
235 END IF; --l_exception_level >= l_runtime_level
236 x_Flag := -1;
237 RETURN;
238 END;
239 --fetch chart_of_accounts_id
240 SELECT Chart_Of_Accounts_Id
241 INTO l_Chart_Of_Accounts_Id
242 FROM Gl_Sets_Of_Books
243 WHERE Set_Of_Books_Id = l_Ledger_Id;
244 IF (l_Statement_Level >= l_Runtime_Level)
245 THEN
246 Fnd_Log.STRING(l_Statement_Level,
247 l_Module_Name,
248 'Fetched: l_chart_of_accounts_id=' ||
249 Nvl(To_Char(l_Chart_Of_Accounts_Id), 'null'));
250 END IF; --l_statement_level >= l_runtime_level
251 x_Sob_Id := l_Ledger_Id;
252 x_Coa_Id := l_Chart_Of_Accounts_Id;
253 x_Flag := 0;
254 IF (l_Procedure_Level >= l_Runtime_Level)
255 THEN
256 Fnd_Log.STRING(l_Procedure_Level,
257 l_Module_Name,
258 'Stop running ' || l_Module_Name);
259 END IF; --l_procedure_level >= l_runtime_level
260
261 EXCEPTION
262 WHEN OTHERS THEN
263 x_Flag := -1;
264 RETURN;
265 END;
266 --==========================================================================
267 -- FUNCTION NAME:
268 --
269 -- Get_SOB Public
270 --
271 -- DESCRIPTION:
272 --
273 -- This function is used to get set of book id within SQL statements
274 -- by legal entity. Actually it invokes the procedure Get_SOB_And_COA
275 -- to get return value. If no data found or exception occurs, -9 will be
276 -- returned.
277 --
278 --
279 -- PARAMETERS:
280 -- In: p_legal_entity_id Legal entity ID
281 --
282 -- RETURN:
283 -- Set of book ID, -9 for abnormal cases.
284 --
285 -- DESIGN REFERENCES:
286 --
287 --
288 -- CHANGE HISTORY:
289 --
290 -- 31-Mar-2006 Joseph Wang Created
291 --
292 --===========================================================================
293 FUNCTION Get_Sob(p_Legal_Entity_Id NUMBER) RETURN NUMBER IS
294 l_Sob_Id NUMBER;
295 l_Coa_Id NUMBER;
296 l_Flag NUMBER;
297 BEGIN
298 Get_Sob_And_Coa(p_Legal_Entity_Id => p_Legal_Entity_Id,
299 x_Sob_Id => l_Sob_Id,
300 x_Coa_Id => l_Coa_Id,
301 x_Flag => l_Flag);
302 IF l_Flag = -1
303 THEN
304 RETURN - 9;
305 ELSE
306 RETURN l_Sob_Id;
307 END IF;
308 END;
309 --==========================================================================
310 -- FUNCTION NAME:
311 --
312 -- Get_COA Public
313 --
314 -- DESCRIPTION:
315 --
316 -- This function is used to get chart of account id within SQL statements
317 -- by legal entity. Actually it invokes the procedure Get_SOB_And_COA
318 -- to get return value. If no data found or exception occurs, -9 will be
319 -- returned.
320 --
321 --
322 -- PARAMETERS:
323 -- In: p_legal_entity_id Legal entity ID
324 --
325 -- RETURN:
326 -- Chart of account ID, -9 for abnormal cases.
327 --
328 -- DESIGN REFERENCES:
329 --
330 --
331 -- CHANGE HISTORY:
332 --
333 -- 31-Mar-2006 Joseph Wang Created
334 --
335 --===========================================================================
336 /*
337 FUNCTION Get_Coa(p_Legal_Entity_Id NUMBER) RETURN NUMBER IS
338 l_Sob_Id NUMBER;
339 l_Coa_Id NUMBER;
340 l_Flag NUMBER;
341 BEGIN
342 Get_Sob_And_Coa(p_Legal_Entity_Id => p_Legal_Entity_Id,
343 x_Sob_Id => l_Sob_Id,
344 x_Coa_Id => l_Coa_Id,
345 x_Flag => l_Flag);
346 IF l_Flag = -1
347 THEN
348 RETURN - 9;
349 ELSE
350 RETURN l_Coa_Id;
351 END IF;
352 END;
353 */
354 --added by lyb, Get chart_of_accounts_id by current_access_id
355 FUNCTION Get_Coa(p_Access_Set_Id NUMBER) RETURN NUMBER IS
356 l_Ret_Coa NUMBER;
357
358 CURSOR c_Get_Coa IS
359 SELECT Chart_Of_Accounts_Id
360 FROM Gl_Access_Sets
361 WHERE Access_Set_Id = p_Access_Set_Id;
362
363 BEGIN
364 --Get Chart of Accounts Id
365 OPEN c_Get_Coa;
366 FETCH c_Get_Coa
367 INTO l_Ret_Coa;
368 CLOSE c_Get_Coa;
369
370 RETURN l_Ret_Coa;
371
372 END Get_Coa;
373 --==========================================================================
374 -- FUNCTION NAME:
375 --
376 -- Get_Lookup_Meaning Public
377 --
378 -- DESCRIPTION:
379 --
380 -- This function is used to get lookup meaning under one lookup code
381 -- according to lookup type.
382 --
383 --
384 -- PARAMETERS:
385 -- In: p_lookup_code lookup code
386 --
387 -- RETURN:
388 -- Lookup_meaning Varchar2
389 --
390 -- DESIGN REFERENCES:
391 --
392 --
393 -- CHANGE HISTORY:
394 --
395 -- 24-Mar-2006 Jackey Li Created
396 --
397 --===========================================================================
398 FUNCTION Get_Lookup_Meaning(p_Lookup_Code IN VARCHAR2) RETURN VARCHAR2 IS
399
400 l_Procedure_Name VARCHAR2(30) := 'Get_Lookup_Meaning';
401 l_Lookup_Meaning Fnd_Lookup_Values.Meaning%TYPE := NULL;
402 l_Runtime_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
403 l_Procedure_Level NUMBER := Fnd_Log.Level_Procedure;
404 l_Statement_Level NUMBER := Fnd_Log.Level_Statement;
405 l_Exception_Level NUMBER := Fnd_Log.Level_Exception;
406
407 -- this cursor is to get looup_meaning under some lookup_code
408 CURSOR c_Lookup IS
409 SELECT Flv.Meaning
410 FROM Fnd_Lookup_Values Flv
411 WHERE Flv.LANGUAGE = Userenv('LANG')
412 AND Flv.Lookup_Type = 'JA_CN_DUPOBJECTS_TOKENS'
413 AND Flv.View_Application_Id = 0
414 AND Flv.Security_Group_Id = 0
415 AND Flv.Lookup_Code = p_Lookup_Code;
416
417 BEGIN
418 --log
419 IF (l_Procedure_Level >= l_Runtime_Level)
420 THEN
421 Fnd_Log.STRING(l_Procedure_Level,
422 l_Module_Prefix || '.' || l_Procedure_Name || '.begin',
423 'begin procedure');
424 END IF; --l_procedure_level >= l_runtime_level
425
426 IF p_Lookup_Code IS NULL
427 THEN
428 l_Lookup_Meaning := NULL;
429 ELSE
430 OPEN c_Lookup;
431 FETCH c_Lookup
432 INTO l_Lookup_Meaning;
433 IF c_Lookup%NOTFOUND
434 THEN
435 l_Lookup_Meaning := NULL;
436 END IF;
437 CLOSE c_Lookup;
438 END IF; --IF p_lookup_code IS NULL
439
440 --log
441 IF (l_Procedure_Level >= l_Runtime_Level)
442 THEN
443 Fnd_Log.STRING(l_Procedure_Level,
444 l_Module_Prefix || '.' || l_Procedure_Name || '.end',
445 'end procedure');
446 END IF; --l_procedure_level >= l_runtime_level
447
448 RETURN l_Lookup_Meaning;
449
450 END Get_Lookup_Meaning;
451
452 --==========================================================================
453 -- FUNCTION NAME:
454 --
455 -- Check_Nat_Number Public
456 --
457 -- DESCRIPTION:
458 --
459 -- This function is used to check if the given string is a natual number.
460 --
461 --
462 -- PARAMETERS:
463 -- In: p_subject the string need to check
464 --
465 -- RETURN:
466 -- BOOLEAN
467 --
468 -- DESIGN REFERENCES:
469 --
470 --
471 -- CHANGE HISTORY:
472 -- 11-Apr-2006 Jackey Li Created
473 --
474 --===========================================================================
475 FUNCTION Check_Nat_Number(p_Subject IN VARCHAR2) RETURN BOOLEAN IS
476 l_Tmp VARCHAR2(100);
477 l_Number NUMBER;
478 l_Mod NUMBER;
479 BEGIN
480 l_Tmp := p_Subject;
481 l_Number := To_Number(l_Tmp);
482 IF l_Number < 0
483 THEN
484 RETURN FALSE;
485 END IF;
486 l_Mod := MOD(l_Number, 2);
487 IF l_Mod = 1
488 OR l_Mod = 0
489 THEN
490 RETURN TRUE;
491 ELSE
492 RETURN FALSE;
493 END IF;
494
495 EXCEPTION
496 WHEN OTHERS THEN
497 RETURN FALSE;
498
499 END Check_Nat_Number;
500
501 --==========================================================================
502 -- PROCEDURE NAME:
503 --
504 -- Output_Conc Public
505 --
506 -- DESCRIPTION:
507 --
508 -- This procedure write data to concurrent output file
509 -- the data can be longer than 4000
510 --
511 -- PARAMETERS:
512 -- In: p_clob the content which need output to concurrent output
513 --
514 --
515 -- DESIGN REFERENCES:
516 --
517 --
518 -- CHANGE HISTORY:
519 --
520 -- 30-APR-2005: qugen.hu Created.
521 -- 27-APR-2005: Andrew.liu imported.
522 -- 31-OCT-2008: Yao Zhang Fix bug 7524912 changed
523 --===========================================================================
524 PROCEDURE Output_Conc(p_Clob IN CLOB) IS
525 Max_Linesize NUMBER := 254;
526 l_Pos_Tag NUMBER;
527 l_Pos NUMBER;
528 l_Len NUMBER;
529 l_Tmp NUMBER;
530 l_Tmp1 NUMBER;
531 l_Substr CLOB;
532 BEGIN
533 NULL;
534 --initalize
535 l_Pos := 1;
536 l_Len := Length(p_Clob);
537
538 WHILE l_Pos <= l_Len
539 LOOP
540 --get the XML tag from reverse direction
541 l_Tmp := l_Pos + Max_Linesize - 2 - l_Len;
542 --l_Pos_Tag := Instr(p_Clob, '>', l_Tmp);--fix bug 7524912 delete
543 l_Pos_Tag := Instr(p_Clob, '><', l_Tmp);--fix bug 7524912 add
544 --the pos didnot touch the end of string
545 l_Tmp1 := l_Pos - 1;
546
547 IF (l_Pos_Tag > l_Tmp1)
548 AND (l_Tmp < 0)
549 THEN
550 l_Tmp := l_Pos_Tag - l_Pos + 1;
551 --Fnd_File.Put(Fnd_File.Output, Substr(p_Clob, l_Pos, l_Tmp));--fix bug 7524912 delete
552 Fnd_File.PUT_LINE(Fnd_File.Output, Substr(p_Clob, l_Pos, l_Tmp));--fix bug 7524912 add
553 l_Pos := l_Pos_Tag + 1;
554 ELSE
555 l_Substr := Substr(p_Clob, l_Pos);
556 --Fnd_File.Put(Fnd_File.Output, l_Substr);--fix bug 7524912 delete
557 Fnd_File.PUT_LINE(Fnd_File.Output, l_Substr);--fix bug 7524912 add
558 l_Pos := l_Len + 1;
559
560 END IF;
561
562 END LOOP;
563 EXCEPTION
564 WHEN OTHERS THEN
565 NULL;
566 END Output_Conc;
567
568 --==========================================================================
569 -- FUNCTION NAME:
570 --
571 -- Check_Account_Level Public
572 --
573 -- DESCRIPTION:
574 --
575 -- This procedure check the account level of an account. If the account
576 -- level is not null, and is a natural number and less than 16 than return
577 -- TRUE, else FALSE.
578 --
579 -- PARAMETERS:
580 -- In: P_LEVEL the account level
581 --
582 --
583 -- DESIGN REFERENCES:
584 --
585 --
586 -- CHANGE HISTORY:
587 --
588 -- 18-MAY-2005: Andrew.liu Created.
589 --
590 --===========================================================================
591 FUNCTION Check_Account_Level(p_Level IN VARCHAR2) RETURN BOOLEAN IS
592 l_Na_Level VARCHAR2(100) := p_Level;
593 l_Number NUMBER;
594 BEGIN
595 l_Number := To_Number(l_Na_Level);
596 IF Instr(l_Na_Level, '.', 1, 1) > 0 --not a integer
597 OR Instr(To_Char(l_Number), '.', 1, 1) > 0 --not a integer
598 OR l_Number < 1 --less than 1
599 THEN
600 RETURN FALSE;
601 END IF;
602
603 IF l_Na_Level IS NOT NULL
604 AND l_Number < 16
605 THEN
606 RETURN TRUE;
607 ELSE
608 RETURN FALSE;
609 END IF;
610 EXCEPTION
611 WHEN OTHERS THEN
612 RETURN FALSE;
613 END Check_Account_Level;
614
615 --==========================================================================
616 -- FUNCTION NAME:
617 -- get_lookup_code Public
618 --
619 -- DESCRIPTION:
620 -- This function is used to get lookup code of lookup meaning,
621 -- PARAMETERS:
622 -- In: p_lookup_meaning lookup meaning
623 -- p_lookup_type lookup code
624 -- p_view_application_id view application, DEFAULT 0
625 -- p_security_group_id security group
626 --
627 --
628 -- DESIGN REFERENCES:
629 -- None
630 --
631 -- CHANGE HISTORY:
632 -- 06/03/2006 Shujuan Yan Created
633 --==========================================================================
634 FUNCTION Get_Lookup_Code(p_Lookup_Meaning IN VARCHAR2,
635 p_Lookup_Type IN VARCHAR2,
636 p_View_Application_Id IN NUMBER DEFAULT 0,
637 p_Security_Group_Id IN NUMBER DEFAULT 0)
638 RETURN VARCHAR2 IS
639
640 l_Procedure_Name VARCHAR2(30) := 'Get_Lookup_Code';
641 l_Lookup_Code Fnd_Lookup_Values.Lookup_Code%TYPE := NULL;
642 l_Runtime_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
643 l_Procedure_Level NUMBER := Fnd_Log.Level_Procedure;
644 --l_statement_level NUMBER := fnd_log.level_statement;
645 --l_exception_level NUMBER := fnd_log.level_exception;
646
647 -- this cursor is to get looup_meaning under some lookup_code
648 CURSOR c_Lookup IS
649 SELECT Flv.Lookup_Code
650 FROM Fnd_Lookup_Values Flv
651 WHERE Flv.LANGUAGE = Userenv('LANG')
652 AND Flv.Lookup_Type = p_Lookup_Type
653 AND Flv.Meaning = p_Lookup_Meaning
654 AND Flv.View_Application_Id = p_View_Application_Id
655 AND Flv.Security_Group_Id = p_Security_Group_Id;
656
657 BEGIN
658 --log for debug
659 IF (l_Procedure_Level >= l_Runtime_Level)
660 THEN
661 Fnd_Log.STRING(l_Procedure_Level,
662 l_Module_Prefix || '.' || l_Procedure_Name || '.begin',
663 'begin procedure');
664 END IF; --l_procedure_level >= l_runtime_level
665
666 IF p_Lookup_Meaning IS NULL
667 THEN
668 l_Lookup_Code := NULL;
669 ELSE
670 OPEN c_Lookup;
671 FETCH c_Lookup
672 INTO l_Lookup_Code;
673 IF c_Lookup%NOTFOUND
674 THEN
675 l_Lookup_Code := NULL;
676 END IF;
677 CLOSE c_Lookup;
678 END IF; --IF p_lookup_code IS NULL
679
680 --log for debug
681 IF (l_Procedure_Level >= l_Runtime_Level)
682 THEN
683 Fnd_Log.STRING(l_Procedure_Level,
684 l_Module_Prefix || '.' || l_Procedure_Name || '.end',
685 'end procedure');
686 END IF; --l_procedure_level >= l_runtime_level
687
688 RETURN l_Lookup_Code;
689
690 END Get_Lookup_Code;
691 --==========================================================================
692 -- PROCEDURE NAME:
693 -- Submit_Charset_Conversion Public
694 --
695 -- DESCRIPTION:
696 -- This function is used to submit charset conversion concurrent.
697 -- PARAMETERS:
698 -- In: p_xml_request_id xml publisher concurrent request id
699 -- p_source_charset source charset
700 -- p_destination_charset destination charset
701 -- p_source_separator source separator
702 -- Out: x_charset_request_id charset conversion request id
703 -- x_result_flag result flag
704 --
705 --
706 -- DESIGN REFERENCES:
707 -- None
708 --
709 -- CHANGE HISTORY:
710 -- 06/03/2006 Shujuan Yan Created
711 --==========================================================================
712 PROCEDURE Submit_Charset_Conversion(p_Xml_Request_Id IN NUMBER,
713 p_Source_Charset IN VARCHAR2,
714 p_Destination_Charset IN VARCHAR2,
715 p_Source_Separator IN VARCHAR2,
716 x_Charset_Request_Id OUT NOCOPY NUMBER,
717 x_Result_Flag OUT NOCOPY VARCHAR2) IS
718
719 l_Procedure_Name VARCHAR2(30) := 'Submit_Charset_Conversion ';
720 l_Runtime_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
721 l_Procedure_Level NUMBER := Fnd_Log.Level_Procedure;
722 --l_statement_level NUMBER := fnd_log.level_statement;
723 --l_exception_level NUMBER := fnd_log.level_exception;
724 l_Complete_Flag BOOLEAN;
725 l_Phase VARCHAR2(100);
726 l_Status VARCHAR2(100);
727 l_Del_Phase VARCHAR2(100);
728 l_Del_Status VARCHAR2(100);
729 l_Message VARCHAR2(1000);
730
731 BEGIN
732 --log for debug
733 IF (l_Procedure_Level >= l_Runtime_Level)
734 THEN
735 Fnd_Log.STRING(l_Procedure_Level,
736 l_Module_Prefix || '.' || l_Procedure_Name || '.begin',
737 'begin procedure');
738 END IF; --l_procedure_level >= l_runtime_level
739 -- submit charset conversion concurrent program
740 x_Charset_Request_Id := Fnd_Request.Submit_Request('JA',
741 'JACNCCCP',
742 NULL,
743 SYSDATE,
744 FALSE,
745 p_Xml_Request_Id,
746 p_Source_Charset,
747 p_Destination_Charset,
748 p_Source_Separator);
749
750 IF (x_Charset_Request_Id <= 0 OR x_Charset_Request_Id IS NULL)
751 THEN
752 x_Result_Flag := 'Error';
753 ELSE
754 COMMIT;
755 --Wait for concurrent complete
756 l_Complete_Flag := Fnd_Concurrent.Wait_For_Request(x_Charset_Request_Id,
757 1,
758 0,
759 l_Phase,
760 l_Status,
761 l_Del_Phase,
762 l_Del_Status,
763 l_Message);
764 IF l_Complete_Flag = FALSE
765 OR Get_Lookup_Code(p_Lookup_Meaning => l_Status,
766 p_Lookup_Type => 'CP_STATUS_CODE') <> 'C'
767 THEN
768 x_Result_Flag := 'Error';
769 ELSE
770 x_Result_Flag := 'Success';
771 END IF; -- l_complete_flag = false
772 END IF; -- (x_xml_request_id <= 0 OR x_xml_request_id IS NULL)
773
774 --log for debug
775 IF (l_Procedure_Level >= l_Runtime_Level)
776 THEN
777 Fnd_Log.STRING(l_Procedure_Level,
778 l_Module_Prefix || '.' || l_Procedure_Name || '.end',
779 'end procedure');
780 END IF; --l_procedure_level >= l_runtime_level
781 EXCEPTION
782 WHEN OTHERS THEN
783 --log for debug
784 IF (Fnd_Log.Level_Unexpected >= Fnd_Log.g_Current_Runtime_Level)
785 THEN
786 Fnd_Log.STRING(Fnd_Log.Level_Unexpected,
787 l_Module_Prefix || l_Procedure_Name ||
788 '. OTHER_EXCEPTION ',
789 SQLCODE || SQLERRM);
790 END IF; -- fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
791 RAISE;
792 END Submit_Charset_Conversion;
793 --==========================================================================
794 -- PROCEDURE NAME:
795 -- Change_Output_Filename Public
796 --
797 -- DESCRIPTION:
798 -- This function is used to submit the concurrent program of change output file name
799 -- PARAMETERS:
800 -- In: p_xml_request_id xml publisher concurrent request id
801 -- p_destination_charset destination charset
802 -- p_destination_filename destination filename
803 -- Out: x_charset_request_id charset conversion request id
804 -- x_result_flag result flag
805 --
806 --
807 -- DESIGN REFERENCES:
808 -- None
809 --
810 -- CHANGE HISTORY:
811 -- 06/03/2006 Shujuan Yan Created
812 --==========================================================================
813 PROCEDURE Change_Output_Filename(p_Xml_Request_Id IN NUMBER,
814 p_Destination_Charset IN VARCHAR2,
815 p_Destination_Filename IN VARCHAR2,
816 x_Filename_Request_Id OUT NOCOPY NUMBER,
817 x_Result_Flag OUT NOCOPY VARCHAR2) IS
818
819 l_Procedure_Name VARCHAR2(30) := 'Change_Output_Filename';
820 l_Runtime_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
821 l_Procedure_Level NUMBER := Fnd_Log.Level_Procedure;
822 --l_statement_level NUMBER := fnd_log.level_statement;
823 --l_exception_level NUMBER := fnd_log.level_exception;
824 l_Complete_Flag BOOLEAN;
825 l_Phase VARCHAR2(100);
826 l_Status VARCHAR2(100);
827 l_Del_Phase VARCHAR2(100);
828 l_Del_Status VARCHAR2(100);
829 l_Message VARCHAR2(1000);
830
831 BEGIN
832 --log for debug
833 IF (l_Procedure_Level >= l_Runtime_Level)
834 THEN
835 Fnd_Log.STRING(l_Procedure_Level,
836 l_Module_Prefix || '.' || l_Procedure_Name || '.begin',
837 'begin procedure');
838 END IF; --l_procedure_level >= l_runtime_level
839 -- submit change file name concurrent program
840 x_Filename_Request_Id := Fnd_Request.Submit_Request('JA',
841 'JACNFNCP',
842 NULL,
843 SYSDATE,
844 FALSE,
845 p_Xml_Request_Id,
846 p_Destination_Charset,
847 p_Destination_Filename);
848
849 IF (x_Filename_Request_Id <= 0 OR x_Filename_Request_Id IS NULL)
850 THEN
851 x_Result_Flag := 'Error';
852 ELSE
853 COMMIT;
854 --Wait for concurrent complete
855 l_Complete_Flag := Fnd_Concurrent.Wait_For_Request(x_Filename_Request_Id,
856 1,
857 0,
858 l_Phase,
859 l_Status,
860 l_Del_Phase,
861 l_Del_Status,
862 l_Message);
863 IF l_Complete_Flag = FALSE
864 OR Get_Lookup_Code(p_Lookup_Meaning => l_Status,
865 p_Lookup_Type => 'CP_STATUS_CODE') <> 'C'
866 THEN
867 x_Result_Flag := 'Error';
868 ELSE
869 x_Result_Flag := 'Success';
870 END IF; -- l_complete_flag = false
871 END IF; -- (x_xml_request_id <= 0 OR x_xml_request_id IS NULL)
872
873 --log for debug
874 IF (l_Procedure_Level >= l_Runtime_Level)
875 THEN
876 Fnd_Log.STRING(l_Procedure_Level,
877 l_Module_Prefix || '.' || l_Procedure_Name || '.end',
878 'end procedure');
879 END IF; --l_procedure_level >= l_runtime_level
880 EXCEPTION
881 WHEN OTHERS THEN
882 --log for debug
883 IF (Fnd_Log.Level_Unexpected >= Fnd_Log.g_Current_Runtime_Level)
884 THEN
885 Fnd_Log.STRING(Fnd_Log.Level_Unexpected,
886 l_Module_Prefix || l_Procedure_Name ||
887 '. OTHER_EXCEPTION ',
888 SQLCODE || SQLERRM);
889 END IF; -- fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
890 RAISE;
891 END Change_Output_Filename;
892
893 --==========================================================================
894 -- FUNCTION NAME:
895 --
896 -- Check_Cash_Related_Account Public
897 --
898 -- DESCRIPTION:
899 --
900 -- This function is used to check if the gl code combination passed in is -- Cash Related.
901 --
902 -- PARAMETERS:
903 -- In: p_set_of_bks_id Identifier of GL set of book
904 -- p_acc_flex GL code combination
905 --
906 --
907 -- DESIGN REFERENCES:
908 --
909 --
910 -- CHANGE HISTORY:
911 --
912 -- 30-MAY-2005: Donghai Wang Created
913 --
914 --===========================================================================
915 FUNCTION Check_Cash_Related_Account(p_Set_Of_Bks_Id IN NUMBER,
916 p_Acc_Flex IN VARCHAR2)
917 RETURN BOOLEAN IS
918 l_Id_Flex_Num Fnd_Id_Flex_Structures.Id_Flex_Num%TYPE;
919 l_Delimiter Fnd_Id_Flex_Structures.Concatenated_Segment_Delimiter%TYPE;
920 l_Seq_Account NUMBER;
921 l_Account_Segment_Flag Fnd_Segment_Attribute_Values.Attribute_Value%TYPE;
922 l_Account_Segment Fnd_Id_Flex_Segments.Segment_Name%TYPE;
923
924 l_Cash_Related_Flag BOOLEAN;
925 l_Cash_Acct_Count NUMBER;
926
927 CURSOR c_Coa_Infor IS
928 SELECT Id_Flex_Num,
929 Concatenated_Segment_Delimiter
930 FROM Fnd_Id_Flex_Structures
931 WHERE Application_Id = '101'
932 AND Id_Flex_Code = 'GL#'
933 AND Id_Flex_Num =
934 (SELECT Chart_Of_Accounts_Id
935 FROM Gl_Sets_Of_Books
936 WHERE Set_Of_Books_Id = p_Set_Of_Bks_Id);
937
938 CURSOR c_Coa_Segments IS
939 SELECT Application_Column_Name
940 FROM Fnd_Id_Flex_Segments
941 WHERE Application_Id = 101
942 AND Id_Flex_Code = 'GL#'
943 AND Id_Flex_Num = l_Id_Flex_Num
944 AND Enabled_Flag = 'Y'
945 AND Display_Flag = 'Y'
946 ORDER BY Segment_Num;
947
948 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
949 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
950 l_Proc_Name VARCHAR2(100) := 'Check_Cash_Related_Account';
951
952 BEGIN
953 --log for debug
954 IF (l_Proc_Level >= l_Dbg_Level)
955 THEN
956 Fnd_Log.STRING(l_Proc_Level,
957 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
958 'Enter procedure');
959 Fnd_Log.STRING(l_Proc_Level,
960 l_Module_Prefix || '.' || l_Proc_Name || '.parameter',
961 'p_set_of_bks_id ' || p_Set_Of_Bks_Id);
962 Fnd_Log.STRING(l_Proc_Level,
963 l_Module_Prefix || '.' || l_Proc_Name || '.parameter',
964 'p_acc_flex ' || p_Acc_Flex);
965
966 END IF; --(l_proc_level >= l_dbg_level)
967
968 --To get coa id of current gl books and delimiter for gl account accordingly.
969 OPEN c_Coa_Infor;
970 FETCH c_Coa_Infor
971 INTO l_Id_Flex_Num, l_Delimiter;
972 CLOSE c_Coa_Infor;
973
974 --To evaluate the sequence of account segment in gl account
975 l_Seq_Account := 0;
976 FOR l_Coa_Segment IN c_Coa_Segments
977 LOOP
978 l_Seq_Account := l_Seq_Account + 1;
979
980 SELECT Attribute_Value
981 INTO l_Account_Segment_Flag
982 FROM Fnd_Segment_Attribute_Values
983 WHERE Application_Id = 101
984 AND Id_Flex_Code = 'GL#'
985 AND Id_Flex_Num = l_Id_Flex_Num
986 AND Application_Column_Name =
987 l_Coa_Segment.Application_Column_Name
988 AND Segment_Attribute_Type = 'GL_ACCOUNT';
989
990 EXIT WHEN l_Account_Segment_Flag = 'Y';
991 END LOOP; --l_coa_segment IN c_coa_segments
992
993 --Extract account segment from GL account
994 SELECT Substr(p_Acc_Flex,
995 Instr(p_Acc_Flex, l_Delimiter, 1, l_Seq_Account - 1) + 1,
996 (Instr(p_Acc_Flex, l_Delimiter, 1, l_Seq_Account) -
997 Instr(p_Acc_Flex, l_Delimiter, 1, l_Seq_Account - 1) - 1))
998 INTO l_Account_Segment
999 FROM Dual;
1000
1001 --To check if current account segment is cash related.
1002 /* SELECT COUNT(account_segment_value)
1003 INTO l_cash_acct_count
1004 FROM ja_cn_cash_accounts_all
1005 WHERE set_of_books_id = p_set_of_bks_id
1006 AND account_segment_value = l_account_segment;*/
1007
1008 IF l_Cash_Acct_Count > 0
1009 THEN
1010 l_Cash_Related_Flag := TRUE;
1011 ELSE
1012 l_Cash_Related_Flag := FALSE;
1013 END IF; --l_cash_acct_count>0
1014
1015 IF (l_Proc_Level >= l_Dbg_Level)
1016 THEN
1017 Fnd_Log.STRING(l_Proc_Level,
1018 l_Module_Prefix || '.' || l_Proc_Name || '.end',
1019 'Exit procedure');
1020 END IF; --( l_proc_level >= l_dbg_level )
1021
1022 RETURN(l_Cash_Related_Flag);
1023
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 IF (l_Proc_Level >= l_Dbg_Level)
1027 THEN
1028 Fnd_Log.STRING(l_Proc_Level,
1029 l_Module_Prefix || '.' || l_Proc_Name ||
1030 '. Other_Exception ',
1031 SQLCODE || ':' || SQLERRM);
1032 END IF; --(l_proc_level >= l_dbg_level)
1033
1034 RETURN(FALSE);
1035
1036 END Check_Cash_Related_Account;
1037
1038 --==========================================================================
1039 -- FUNCTION NAME:
1040 --
1041 -- Check_Accounting_Period_Range Public
1042 --
1043 -- DESCRIPTION:
1044 --
1045 -- This function is used to check whether all the periods' status within
1046 -- the range are 'C' or 'P'
1047 --
1048 --
1049 -- PARAMETERS:
1050 -- In: p_legal_entity_id Legal entity ID
1051 -- In: p_start_period_name Start period name
1052 -- In: p_end_period_name End period name
1053 --
1054 -- RETURN:
1055 -- True for success, otherwise False
1056 --
1057 -- DESIGN REFERENCES:
1058 --
1059 --
1060 -- CHANGE HISTORY:
1061 --
1062 -- 04-July-2006 Joseph Wang Created
1063 --
1064 --===========================================================================
1065 FUNCTION Check_Accounting_Period_Range(p_Start_Period_Name IN VARCHAR2,
1066 p_End_Period_Name IN VARCHAR2,
1067 p_Legal_Entity_Id NUMBER,
1068 p_ledger_id IN NUMBER--added by lyb
1069 )
1070 RETURN BOOLEAN IS
1071 l_Start_Date DATE;
1072 l_End_Date DATE;
1073 l_All_Period_Number INTEGER;
1074 l_Closed_Period_Number INTEGER;
1075
1076 l_Sob_Id NUMBER;
1077 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1078 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1079 l_Proc_Name VARCHAR2(100) := 'Check_Accounting_Period_Range';
1080 BEGIN
1081
1082 -- l_Sob_Id := Ja_Cn_Utility.Get_Sob(p_Legal_Entity_Id);--updated by lyb
1083 SELECT Start_Date
1084 INTO l_Start_Date
1085 FROM Gl_Period_Statuses
1086 WHERE ledger_id=p_ledger_id--Set_Of_Books_Id = l_Sob_Id,--updated by lyb
1087 AND Application_Id = 101
1088 AND Period_Name = p_Start_Period_Name;
1089
1090 SELECT End_Date
1091 INTO l_End_Date
1092 FROM Gl_Period_Statuses
1093 WHERE ledger_id=p_ledger_id--Set_Of_Books_Id = l_Sob_Id--updated by lyb
1094 AND Application_Id = 101
1095 AND Period_Name = p_End_Period_Name;
1096
1097 SELECT COUNT(*)
1098 INTO l_All_Period_Number
1099 FROM Gl_Period_Statuses
1100 WHERE ledger_id=p_ledger_id--Set_Of_Books_Id = l_Sob_Id--updated by lyb
1101 AND Application_Id = 101
1102 AND ((Start_Date BETWEEN l_Start_Date AND l_End_Date) AND
1103 (End_Date BETWEEN l_Start_Date AND l_End_Date));
1104
1105 SELECT COUNT(*)
1106 INTO l_Closed_Period_Number
1107 FROM Gl_Period_Statuses
1108 WHERE ledger_id=p_ledger_id--Set_Of_Books_Id = l_Sob_Id,--updated by lyb
1109 AND Application_Id = 101
1110 AND ((Start_Date BETWEEN l_Start_Date AND l_End_Date) AND
1111 (End_Date BETWEEN l_Start_Date AND l_End_Date))
1112 AND (Closing_Status = 'C' OR Closing_Status = 'P');
1113
1114 IF (l_All_Period_Number <> l_Closed_Period_Number)
1115 THEN
1116 RETURN FALSE;
1117 ELSE
1118 RETURN TRUE;
1119 END IF;
1120
1121 EXCEPTION
1122 WHEN OTHERS THEN
1123 IF (l_Proc_Level >= l_Dbg_Level)
1124 THEN
1125 Fnd_Log.STRING(l_Proc_Level,
1126 l_Module_Prefix || '.' || l_Proc_Name ||
1127 '. Other_Exception ',
1128 SQLCODE || ':' || SQLERRM);
1129 END IF; --(l_proc_level >= l_dbg_level)
1130
1131 RETURN(FALSE);
1132 END Check_Accounting_Period_Range;
1133
1134 FUNCTION Fetch_Account_Structure(p_Le_Id IN NUMBER) RETURN VARCHAR2 IS
1135 l_Acc_Stru_Tablename VARCHAR2(100) := 'JA_CN_ACCOUNT_STRUCTURES_KFV';
1136 l_Sql VARCHAR2(1000);
1137 l_Result VARCHAR2(2000);
1138 BEGIN
1139 l_Sql := 'SELECT nvl(jcask.concatenated_segments, '''')
1140 FROM ' || l_Acc_Stru_Tablename ||
1141 ' jcask
1142 ,ja_cn_system_parameters_all jcsp
1143 WHERE jcask.account_structure_id = jcsp.account_structure_id
1144 AND jcsp.legal_entity_id = :1';
1145 EXECUTE IMMEDIATE l_Sql
1146 INTO l_Result
1147 USING p_Le_Id;
1148 RETURN l_Result;
1149 END Fetch_Account_Structure;
1150 --==========================================================================
1151 -- PROCEDURE NAME:
1152 --
1153 -- Populate_Ledger_Le_Bsv_Gt Public
1154 --
1155 -- DESCRIPTION:
1156 --
1157 -- This procedure is used to populate the balance segment of currenct
1158 -- legal entity and ledger into temporary table ja_cn_ledger_le_bsv_gt
1159 --
1160 --
1161 -- PARAMETERS:
1162 -- In: p_legal_entity_id Legal entity ID
1163 -- In: p_ledger_id Ledger ID
1164 --
1165 --
1166 -- DESIGN REFERENCES:
1167 --
1168 --
1169 -- CHANGE HISTORY:
1170 --
1171 -- 12-Mar-07 Qingjun Zhao Created
1172 --
1173 FUNCTION Populate_Ledger_Le_Bsv_Gt(p_Ledger_Id IN NUMBER,
1174 p_Legal_Entity_Id IN NUMBER)
1175 RETURN VARCHAR2 IS
1176 l_Ledger_Category VARCHAR2(30);
1177 l_Bsv_Option VARCHAR2(1);
1178 l_Bsv_Vset_Id NUMBER;
1179
1180 l_Fv_Table Fnd_Flex_Validation_Tables.Application_Table_Name%TYPE;
1181 l_Fv_Col Fnd_Flex_Validation_Tables.Value_Column_Name%TYPE;
1182 l_Fv_Type Fnd_Flex_Value_Sets.Validation_Type%TYPE;
1183 l_Insertsql Dbms_Sql.Varchar2s;
1184 l_Line_No NUMBER := 0;
1185 l_Cursorid INTEGER;
1186 l_Return_No NUMBER;
1187 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1188 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1189 l_Proc_Name VARCHAR2(100) := 'Populate_Ledger_Le_Bsv_Gt';
1190
1191 BEGIN
1192 --log for debug
1193 IF (l_Proc_Level >= l_Dbg_Level)
1194 THEN
1195 Fnd_Log.STRING(l_Proc_Level,
1196 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
1197 'Enter procedure');
1198
1199 Fnd_Log.STRING(l_Proc_Level,
1200 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
1201 'p_ledger_id ' || p_ledger_Id);
1202
1203 Fnd_Log.STRING(l_Proc_Level,
1204 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
1205 'p_legal_entity_id ' || p_legal_entity_id);
1206
1207 END IF; --(l_proc_level >= l_dbg_level)
1208 IF (p_Ledger_Id IS NULL)
1209 THEN
1210 -- Ledger ID is not passed, so return F (i.e. FAIL)
1211 RETURN 'F1';
1212
1213 END IF;
1214
1215 -- First, get its ledger category code and BSV option code
1216 SELECT Ledger_Category_Code,
1217 Nvl(Bal_Seg_Value_Option_Code, 'A'),
1218 Bal_Seg_Value_Set_Id
1219 INTO l_Ledger_Category,
1220 l_Bsv_Option,
1221 l_Bsv_Vset_Id
1222 FROM Gl_Ledgers
1223 WHERE Ledger_Id = p_Ledger_Id;
1224
1225 IF (l_Ledger_Category <> 'PRIMARY' AND l_Ledger_Category <> 'SECONDARY' AND
1226 l_Ledger_Category <> 'ALC')
1227 THEN
1228 -- We don't handle NONE ledgers, which haven't been set up properly yet.
1229 -- Or, invalid ledger cateogry codes of the passed ledger.
1230 RETURN 'F2';
1231
1232
1233
1234 END IF; -- IF (l_ledger_category <> 'PRIMARY' ...
1235
1236 --
1237 -- Insert segment values from GL_LEDGER_NORM_SEG_VALS if the BSV option is
1238 -- Specific (i.e. I)
1239 --
1240 IF (l_Bsv_Option = 'I')
1241 THEN
1242 -- Insert rows for the passed ledger and its associated ALC Ledgers
1243 INSERT INTO Ja_Cn_Ledger_Le_Bsv_Gt
1244 (Ledger_Id,
1245 Ledger_Category_Code,
1246 Chart_Of_Accounts_Id,
1247 Bal_Seg_Value_Option_Code,
1248 Bal_Seg_Value_Set_Id,
1249 Bal_Seg_Value,
1250 Legal_Entity_Id,
1251 Start_Date,
1252 End_Date)
1253 -- XLE uptake: Changed to get the LE name from the new XLE tables
1254 SELECT Lg.Ledger_Id,
1255 Lg.Ledger_Category_Code,
1256 Lg.Chart_Of_Accounts_Id,
1257 Lg.Bal_Seg_Value_Option_Code,
1258 Lg.Bal_Seg_Value_Set_Id,
1259 Bsv.Segment_Value,
1260 Bsv.Legal_Entity_Id,
1261 Bsv.Start_Date,
1262 Bsv.End_Date
1263 FROM Gl_Ledgers Lg,
1264 Gl_Ledger_Relationships Rs,
1265 Gl_Ledger_Norm_Seg_Vals Bsv,
1266 Gl_Ledgers Lgr_c
1267 WHERE ((Rs.Relationship_Type_Code = 'NONE' AND
1268 Rs.Target_Ledger_Id = p_Ledger_Id) OR
1269 (Rs.Target_Ledger_Category_Code = 'ALC' AND
1270 Rs.Relationship_Type_Code IN ('SUBLEDGER', 'JOURNAL') AND
1271 Rs.Source_Ledger_Id = p_Ledger_Id))
1272 AND Rs.Application_Id = 101
1273 AND Lg.Ledger_Id = Rs.Target_Ledger_Id
1274 AND Bsv.Ledger_Id = p_Ledger_Id
1275 AND Rs.Target_Ledger_Id = Lgr_c.Ledger_Id
1276 AND Nvl(Lgr_c.Complete_Flag, 'Y') = 'Y'
1277 AND Bsv.Segment_Type_Code = 'B'
1278 -- We should exclude segment values with status code = 'D' since they
1279 -- will be deleted by the flatten program when config is confirmed
1280 -- AND bsv.status_code IS NULL
1281 AND Nvl(Bsv.Status_Code, 'I') <> 'D'
1282 AND Bsv.Legal_Entity_Id = p_Legal_Entity_Id;
1283
1284
1285
1286 ELSIF (l_Bsv_Option = 'A')
1287 THEN
1288 --
1289 -- Insert segment values from the balancing flex value set if the BSV option is
1290 -- All (i.e. A)
1291 --
1292 SELECT Nvl(Fvt.Application_Table_Name, 'FND_FLEX_VALUES'),
1293 Nvl(Fvt.Value_Column_Name, 'FLEX_VALUE'),
1294 Fvs.Validation_Type
1295 INTO l_Fv_Table,
1296 l_Fv_Col,
1297 l_Fv_Type
1298 FROM Fnd_Flex_Value_Sets Fvs,
1299 Fnd_Flex_Validation_Tables Fvt
1300 WHERE Fvs.Flex_Value_Set_Id = l_Bsv_Vset_Id
1301 AND Fvt.Flex_Value_Set_Id(+) = Fvs.Flex_Value_Set_Id;
1302
1303 -- Build INSERT statement of the dynamic INSERT SQL
1304 l_Line_No := l_Line_No + 1;
1305 l_Insertsql(l_Line_No) := 'INSERT INTO JA_CN_LEDGER_LE_BSV_GT';
1306 l_Line_No := l_Line_No + 1;
1307 l_Insertsql(l_Line_No) := '(LEDGER_ID, LEDGER_CATEGORY_CODE, ';
1308 l_Line_No := l_Line_No + 1;
1309 l_Insertsql(l_Line_No) := ' CHART_OF_ACCOUNTS_ID, BAL_SEG_VALUE_OPTION_CODE, BAL_SEG_VALUE_SET_ID, ';
1310 l_Line_No := l_Line_No + 1;
1311 l_Insertsql(l_Line_No) := ' BAL_SEG_VALUE, LEGAL_ENTITY_ID, ';
1312 l_Line_No := l_Line_No + 1;
1313 l_Insertsql(l_Line_No) := ' START_DATE, END_DATE) ';
1314
1315 -- Build SELECT statement of the dynamic INSERT SQL
1316
1317 -- Columns: LEDGER_ID, LEDGER_CATEGORY_CODE
1318 l_Line_No := l_Line_No + 1;
1319 l_Insertsql(l_Line_No) := 'SELECT lg.LEDGER_ID, lg.LEDGER_CATEGORY_CODE, ';
1320
1321 -- Columns: CHART_OF_ACCOUNTS_ID, BAL_SEG_VALUE_OPTION_CODE
1322 l_Line_No := l_Line_No + 1;
1323 l_Insertsql(l_Line_No) := ' lg.CHART_OF_ACCOUNTS_ID, lg.BAL_SEG_VALUE_OPTION_CODE, ';
1324
1325 -- Columns: BAL_SEG_VALUE_SET_ID, BAL_SEG_COLUMNE_NAME, BAL_SEG_VALUE
1326 l_Line_No := l_Line_No + 1;
1327 l_Insertsql(l_Line_No) := ' lg.BAL_SEG_VALUE_SET_ID, bsv.' ||
1328 l_Fv_Col || ', ';
1329
1330 -- Columns: LEGAL_ENTITY_ID, LEGAL_ENTITY_NAME, START_DATE, END_DATE
1331 -- Note: LE ID and Name are always NULL for ALL BSV option.
1332 l_Line_No := l_Line_No + 1;
1333 IF (l_Fv_Type <> 'F')
1334 THEN
1335 l_Insertsql(l_Line_No) := p_Legal_Entity_Id ||
1336 ', bsv.START_DATE_ACTIVE, bsv.END_DATE_ACTIVE ';
1337 ELSE
1338 l_Insertsql(l_Line_No) := p_Legal_Entity_Id ||
1339 ', NULL, NULL ';
1340 END IF;
1341
1342 -- Column: RELATIONSHIP_ENABLED_FLAG
1343 --l_Line_No := l_Line_No + 1;
1344 --l_Insertsql(l_Line_No) := ' DECODE(lg.LEDGER_CATEGORY_CODE, ''PRIMARY'', ''Y'', ''N'') ';
1345
1346 -- Build FROM statement of the dynamic INSERT SQL
1347 l_Line_No := l_Line_No + 1;
1348 l_Insertsql(l_Line_No) := 'FROM GL_LEDGERS lg, ' || l_Fv_Table ||
1349 ' bsv ';
1350
1351 -- Build WHERE statement of the dynamic INSERT SQL
1352 l_Line_No := l_Line_No + 1;
1353 l_Insertsql(l_Line_No) := 'WHERE (lg.ledger_id = :lg_id1 ';
1354 l_Line_No := l_Line_No + 1;
1355 l_Insertsql(l_Line_No) := ' OR lg.ledger_id IN ( ';
1356 l_Line_No := l_Line_No + 1;
1357 l_Insertsql(l_Line_No) := ' SELECT ledger_id FROM GL_ALC_LEDGER_RSHIPS_V ';
1358 l_Line_No := l_Line_No + 1;
1359 l_Insertsql(l_Line_No) := ' WHERE application_id = 101 ';
1360 l_Line_No := l_Line_No + 1;
1361 l_Insertsql(l_Line_No) := ' AND source_ledger_id = :lg_id2)) ';
1362
1363 IF (l_Fv_Type <> 'F')
1364 THEN
1365 l_Line_No := l_Line_No + 1;
1366 l_Insertsql(l_Line_No) := 'AND bsv.flex_value_set_id = lg.bal_seg_value_set_id ';
1367 l_Line_No := l_Line_No + 1;
1368 l_Insertsql(l_Line_No) := 'AND bsv.summary_flag = ''N'' ';
1369 END IF;
1370
1371 -- Open cursor
1372 l_Cursorid := Dbms_Sql.Open_Cursor;
1373 Dbms_Sql.Parse(l_Cursorid,
1374 l_Insertsql,
1375 1,
1376 l_Line_No,
1377 TRUE,
1378 Dbms_Sql.Native);
1379
1380 -- Bind variables
1381 Dbms_Sql.Bind_Variable(l_Cursorid, ':lg_id1', p_Ledger_Id);
1382 Dbms_Sql.Bind_Variable(l_Cursorid, ':lg_id2', p_Ledger_Id);
1383
1384 -- Execute INSERT SQL
1385 l_Return_No := Dbms_Sql.EXECUTE(l_Cursorid);
1386
1387 -- Close cursor
1388 Dbms_Sql.Close_Cursor(l_Cursorid);
1389
1390 ELSE
1391 -- Invalid BSV option code for the passed ledger
1392 RETURN 'S';
1393
1394 END IF; -- IF (l_bsv_option = 'I')
1395 RETURN 'S';
1396 EXCEPTION
1397 WHEN OTHERS THEN
1398 IF (l_Proc_Level >= l_Dbg_Level)
1399 THEN
1400 Fnd_Log.STRING(l_Proc_Level,
1401 l_Module_Prefix || '.' || l_Proc_Name ||
1402 '. Other_Exception ',
1403 SQLCODE || ':' || SQLERRM);
1404 END IF; --(l_proc_level >= l_dbg_level)
1405 END Populate_Ledger_Le_Bsv_Gt;
1406
1407 --==========================================================================
1408 -- FUNCTION NAME:
1409 --
1410 -- Get_Balancing_Segment_Value Public
1411 --
1412 -- DESCRIPTION:
1413 --
1414 -- This function is used to get balancing segment value for the specified key flexfield segments.
1415 --
1416 --
1417 -- PARAMETERS:
1418 -- In: p_coa_id Chart of account ID
1419 -- In: p_concatenated_segments Concatenated segments
1420 --
1421 -- RETURN:
1422 -- Balancing segment value, NULL for abnormal cases.
1423 --
1424 -- DESIGN REFERENCES:
1425 --
1426 --
1427 -- CHANGE HISTORY:
1428 --
1429 -- 29-Aug-2008 Chaoqun Wu Created
1430 --
1431 --===========================================================================
1432 FUNCTION Get_Balancing_Segment_Value(
1433 p_coa_id IN NUMBER,
1434 p_concatenated_segments IN VARCHAR2)
1435 RETURN VARCHAR2 IS
1436 l_delimiter VARCHAR2(1);
1437 l_segments FND_FLEX_EXT.SEGMENTARRAY;
1438 l_total_num NUMBER;
1439 l_num NUMBER;
1440 BEGIN
1441 l_delimiter := FND_FLEX_EXT.GET_DELIMITER('SQLGL', 'GL#', p_coa_id);
1442 l_total_num := FND_FLEX_EXT.BREAKUP_SEGMENTS(p_concatenated_segments,
1443 l_delimiter,
1444 l_segments);
1445 SELECT NUM+1 INTO l_num
1446 FROM FND_SEGMENT_ATTRIBUTE_VALUES FSAV,
1447 (SELECT ROWNUM NUM, APPLICATION_COLUMN_NAME
1448 FROM (SELECT APPLICATION_COLUMN_NAME, SEGMENT_NUM
1449 FROM FND_ID_FLEX_SEGMENTS
1450 WHERE ID_FLEX_NUM = p_coa_id
1451 AND ID_FLEX_CODE = 'GL#'
1452 AND APPLICATION_ID = 101
1453 AND ENABLED_FLAG = 'Y'
1454 ORDER BY SEGMENT_NUM)) FIFS
1455 WHERE FSAV.APPLICATION_ID = 101
1456 AND FSAV.ID_FLEX_NUM = p_coa_id
1457 AND ID_FLEX_CODE = 'GL#'
1458 AND FSAV.ATTRIBUTE_VALUE = 'Y'
1459 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
1460 AND FSAV.APPLICATION_COLUMN_NAME = FIFS.APPLICATION_COLUMN_NAME;
1461
1462 IF l_num <= l_total_num THEN
1463 RETURN l_segments(l_num);
1464 ELSE
1465 RETURN NULL;
1466 END IF;
1467
1468 END Get_Balancing_Segment_Value;
1469
1470
1471 END Ja_Cn_Utility;