[Home] [Help]
PACKAGE BODY: APPS.JA_CN_UTILITY
Source
1 PACKAGE BODY Ja_Cn_Utility AS
2 --$Header: JACNCUYB.pls 120.15.12020000.2 2013/02/21 09:41:58 chongwan 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 --| The following added for China Accounting Software Data Interface Standard V2
46 --| 20-JAN-2010: Jason Liu Added the procdure Add_Sub_Root_Node and Add_Child_Node
47 --| Added the function Get_XML_Tag
48 --| Added the procuder Add_Fixed_Child_Node
49 --| Added the procuder Replace_Special_Characters
50 --| 1-Mar-2010 Micron Huang Added the function Process_Subsidiary_Item
51 --| 15-Mar-2010 Chongwu Li Added the function Check_System_Option
52 --| 18-Mar-2010 Qingyi Wang Added the function Generate_File_name
53 --| 11-May-2010 Qingyi Wang Added the function Get_Rounding_Value
54 --| 12-Dec-2012 Jar Wang Added for banking
55 --+======================================================================*//
56
57 l_Module_Prefix VARCHAR2(100) := 'JA_CN_UTILITY';
58
59 --==========================================================================
60 -- FUNCTION NAME:
61 --
62 -- Check_Profile Public
63 --
64 -- DESCRIPTION:
65 --
66 -- This function is used to check if all required profiles has been properly set
67 -- for current responsibility. If No, the function will return FALSE to caller and .
68 -- raise error message. Those required profiles include ' JG: Product', which should
69 -- be set to 'Asia/Pacific Localizations','JG: Territory', which should be set
70 -- to 'China' and 'JA: CNAO Legal Entity', which should be NOT NULL
71 --
72 --
73 -- PARAMETERS:
74 -- In:
75 --
76 --
77 -- DESIGN REFERENCES:
78 --
79 --
80 -- CHANGE HISTORY:
81 --
82 -- 02-Mar-2006 Donghai Wang Created
83 --
84 --===========================================================================
85
86 FUNCTION Check_Profile RETURN BOOLEAN IS
87 l_False_Flag VARCHAR2(1) := 'N';
88 l_Error_Msg VARCHAR2(2000);
89 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
90 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
91 l_Proc_Name VARCHAR2(100) := 'Check_Profile';
92 BEGIN
93
94 --log for debug
95 IF (l_Proc_Level >= l_Dbg_Level)
96 THEN
97 Fnd_Log.STRING(l_Proc_Level,
98 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
99 'Enter procedure');
100 END IF; --(l_proc_level >= l_dbg_level)
101
102 --To check if the profile JG: Product' is set to 'Asia/Pacific Localizations'
103 --and the profile 'JG: Territory' is set to 'China'
104 IF (Fnd_Profile.VALUE(NAME => 'JGZZ_PRODUCT_CODE') <> 'JA' OR
105 Fnd_Profile.VALUE(NAME => 'JGZZ_PRODUCT_CODE') IS NULL)
106 OR (Fnd_Profile.VALUE(NAME => 'JGZZ_COUNTRY_CODE') <> 'CN' OR
107 Fnd_Profile.VALUE(NAME => 'JGZZ_COUNTRY_CODE') IS NULL)
108 THEN
109 --Raise error message for caller
110 l_False_Flag := 'Y';
111 Fnd_Message.Set_Name(Application => 'JA',
112 NAME => 'JA_CN_PROFILE_NOT_ENABLE');
113 l_Error_Msg := Fnd_Message.Get;
114
115 --Output error message
116 Fnd_File.Put_Line(Fnd_File.Output, l_Error_Msg);
117 END IF;
118
119 --To check if current responsibility has profile 'JA: CNAO Legal Entity'
120 IF Fnd_Profile.VALUE(NAME => 'JA_CN_LEGAL_ENTITY') IS NULL
121 THEN
122 --Raise error message for caller
123 l_False_Flag := 'Y';
124 Fnd_Message.Set_Name(Application => 'JA',
125 NAME => 'JA_CN_NO_LEGAL_ENTITY');
126 l_Error_Msg := Fnd_Message.Get;
127
128 --Output error message
129 Fnd_File.Put_Line(Fnd_File.Output, l_Error_Msg);
130 END IF; -- FND_PROFILE.Value(NAME => 'JA_CN_LEGAL_ENTITY')IS NULL
131
132 IF l_False_Flag = 'N'
133 THEN
134 --log for debug
135 IF (l_Proc_Level >= l_Dbg_Level)
136 THEN
137 Fnd_Log.STRING(l_Proc_Level,
138 l_Module_Prefix || '.' || l_Proc_Name || '.end',
139 'Exit procedure');
140 END IF; --( l_proc_level >= l_dbg_level )
141 RETURN TRUE;
142 ELSE
143 --log for debug
144 IF (l_Proc_Level >= l_Dbg_Level)
145 THEN
146 Fnd_Log.STRING(l_Proc_Level,
147 l_Module_Prefix || '.' || l_Proc_Name || '.end',
148 'Exit procedure');
149 END IF; --( l_proc_level >= l_dbg_level )
150 RETURN FALSE;
151 END IF; --l_false_flage='N'
152
153 EXCEPTION
154 WHEN OTHERS THEN
155 IF (l_Proc_Level >= l_Dbg_Level)
156 THEN
157 Fnd_Log.STRING(l_Proc_Level,
158 l_Module_Prefix || '.' || l_Proc_Name ||
159 '. Other_Exception ',
160 SQLCODE || ':' || SQLERRM);
161 END IF; --(l_proc_level >= l_dbg_level)
162 END Check_Profile;
163 --==========================================================================
164 -- PROCEDURE NAME:
165 --
166 -- Get_SOB_And_COA Public
167 --
168 -- DESCRIPTION:
169 --
170 -- This function is used to get chart of account id and set of book id
171 -- by legal entity, if no data found or exception occurs, x_flag will be
172 -- returned with -1
173 --
174 --
175 --
176 -- PARAMETERS:
177 -- In: p_legal_entity_id Legal entity ID
178 -- Out: x_sob_id Set of book ID
179 -- Out: x_coa_id Chart of account ID
180 -- Out: x_flag Return flag
181 --
182 -- RETURN:
183 -- Flag, -1 for abnormal cases.
184 --
185 -- DESIGN REFERENCES:
186 --
187 --
188 -- CHANGE HISTORY:
189 --
190 -- 09-Mar-2006 Joseph Wang Created
191 --
192 --===========================================================================
193
194 PROCEDURE Get_Sob_And_Coa(p_Legal_Entity_Id NUMBER,
195 x_Sob_Id OUT NOCOPY NUMBER,
196 x_Coa_Id OUT NOCOPY NUMBER,
197 x_Flag OUT NOCOPY NUMBER) IS
198
199 l_Module_Name CONSTANT VARCHAR2(100) := l_Module_Prefix ||
200 '.Get_SOB_And_COA';
201 l_Runtime_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
202 l_Procedure_Level NUMBER := Fnd_Log.Level_Procedure;
203 l_Statement_Level NUMBER := Fnd_Log.Level_Statement;
204 l_Exception_Level NUMBER := Fnd_Log.Level_Exception;
205 l_Message VARCHAR2(300);
206
207 l_Chart_Of_Accounts_Id Gl_Sets_Of_Books.Chart_Of_Accounts_Id%TYPE;
208 l_Ledger_Id Gl_Ledgers.Ledger_Id%TYPE;
209 BEGIN
210
211 --log the parameters
212 IF (l_Procedure_Level >= l_Runtime_Level)
213 THEN
214
215 Fnd_Log.STRING(l_Procedure_Level,
216 l_Module_Name,
217 'Start to run ' || l_Module_Name ||
218 'with parameter: p_legal_entity_id=' ||
219 Nvl(To_Char(p_Legal_Entity_Id), 'null'));
220
221 END IF; --l_procedure_level >= l_runtime_level
222
223 BEGIN
224 /* SELECT l_ledger_id
225 INTO l_ledger_id
226 FROM ja_cn_system_parameters_all
227 WHERE legal_entity_id = p_legal_entity_id;
228 */ --log the SOB
229 IF (l_Statement_Level >= l_Runtime_Level)
230 THEN
231 Fnd_Log.STRING(l_Statement_Level,
232 l_Module_Name,
233 'Fetched: l_set_of_books_id=' ||
234 Nvl(To_Char(l_Ledger_Id), 'null'));
235 END IF; --l_statement_level >= l_runtime_level
236
237 EXCEPTION
238 WHEN No_Data_Found THEN
239 Fnd_Message.Set_Name('JA', 'JA_CN_MISSING_BOOK_INFO');
240 l_Message := Fnd_Message.Get();
241 Fnd_File.Put_Line(Fnd_File.Output, l_Message);
242 IF (l_Exception_Level >= l_Runtime_Level)
243 THEN
244 Fnd_Log.STRING(l_Exception_Level, l_Module_Name, l_Message);
245 END IF; --l_exception_level >= l_runtime_level
246 x_Flag := -1;
247 RETURN;
248 END;
249 --fetch chart_of_accounts_id
250 SELECT Chart_Of_Accounts_Id
251 INTO l_Chart_Of_Accounts_Id
252 FROM Gl_Sets_Of_Books
253 WHERE Set_Of_Books_Id = l_Ledger_Id;
254 IF (l_Statement_Level >= l_Runtime_Level)
255 THEN
256 Fnd_Log.STRING(l_Statement_Level,
257 l_Module_Name,
258 'Fetched: l_chart_of_accounts_id=' ||
259 Nvl(To_Char(l_Chart_Of_Accounts_Id), 'null'));
260 END IF; --l_statement_level >= l_runtime_level
261 x_Sob_Id := l_Ledger_Id;
262 x_Coa_Id := l_Chart_Of_Accounts_Id;
263 x_Flag := 0;
264 IF (l_Procedure_Level >= l_Runtime_Level)
265 THEN
266 Fnd_Log.STRING(l_Procedure_Level,
267 l_Module_Name,
268 'Stop running ' || l_Module_Name);
269 END IF; --l_procedure_level >= l_runtime_level
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 x_Flag := -1;
274 RETURN;
275 END;
276 --==========================================================================
277 -- FUNCTION NAME:
278 --
279 -- Get_SOB Public
280 --
281 -- DESCRIPTION:
282 --
283 -- This function is used to get set of book id within SQL statements
284 -- by legal entity. Actually it invokes the procedure Get_SOB_And_COA
285 -- to get return value. If no data found or exception occurs, -9 will be
286 -- returned.
287 --
288 --
289 -- PARAMETERS:
290 -- In: p_legal_entity_id Legal entity ID
291 --
292 -- RETURN:
293 -- Set of book ID, -9 for abnormal cases.
294 --
295 -- DESIGN REFERENCES:
296 --
297 --
298 -- CHANGE HISTORY:
299 --
300 -- 31-Mar-2006 Joseph Wang Created
301 --
302 --===========================================================================
303 FUNCTION Get_Sob(p_Legal_Entity_Id NUMBER) RETURN NUMBER IS
304 l_Sob_Id NUMBER;
305 l_Coa_Id NUMBER;
306 l_Flag NUMBER;
307 BEGIN
308 Get_Sob_And_Coa(p_Legal_Entity_Id => p_Legal_Entity_Id,
309 x_Sob_Id => l_Sob_Id,
310 x_Coa_Id => l_Coa_Id,
311 x_Flag => l_Flag);
312 IF l_Flag = -1
313 THEN
314 RETURN - 9;
315 ELSE
316 RETURN l_Sob_Id;
317 END IF;
318 END;
319 --==========================================================================
320 -- FUNCTION NAME:
321 --
322 -- Get_COA Public
323 --
324 -- DESCRIPTION:
325 --
326 -- This function is used to get chart of account id within SQL statements
327 -- by legal entity. Actually it invokes the procedure Get_SOB_And_COA
328 -- to get return value. If no data found or exception occurs, -9 will be
329 -- returned.
330 --
331 --
332 -- PARAMETERS:
333 -- In: p_legal_entity_id Legal entity ID
334 --
335 -- RETURN:
336 -- Chart of account ID, -9 for abnormal cases.
337 --
338 -- DESIGN REFERENCES:
339 --
340 --
341 -- CHANGE HISTORY:
342 --
343 -- 31-Mar-2006 Joseph Wang Created
344 --
345 --===========================================================================
346 /*
347 FUNCTION Get_Coa(p_Legal_Entity_Id NUMBER) RETURN NUMBER IS
348 l_Sob_Id NUMBER;
349 l_Coa_Id NUMBER;
350 l_Flag NUMBER;
351 BEGIN
352 Get_Sob_And_Coa(p_Legal_Entity_Id => p_Legal_Entity_Id,
353 x_Sob_Id => l_Sob_Id,
354 x_Coa_Id => l_Coa_Id,
355 x_Flag => l_Flag);
356 IF l_Flag = -1
357 THEN
358 RETURN - 9;
359 ELSE
360 RETURN l_Coa_Id;
361 END IF;
362 END;
363 */
364 --added by lyb, Get chart_of_accounts_id by current_access_id
365 FUNCTION Get_Coa(p_Access_Set_Id NUMBER) RETURN NUMBER IS
366 l_Ret_Coa NUMBER;
367
368 CURSOR c_Get_Coa IS
369 SELECT Chart_Of_Accounts_Id
370 FROM Gl_Access_Sets
371 WHERE Access_Set_Id = p_Access_Set_Id;
372
373 BEGIN
374 --Get Chart of Accounts Id
375 OPEN c_Get_Coa;
376 FETCH c_Get_Coa
377 INTO l_Ret_Coa;
378 CLOSE c_Get_Coa;
379
380 RETURN l_Ret_Coa;
381
382 END Get_Coa;
383 --==========================================================================
384 -- FUNCTION NAME:
385 --
386 -- Get_Lookup_Meaning Public
387 --
388 -- DESCRIPTION:
389 --
390 -- This function is used to get lookup meaning under one lookup code
391 -- according to lookup type.
392 --
393 --
394 -- PARAMETERS:
395 -- In: p_lookup_code lookup code
396 --
397 -- RETURN:
398 -- Lookup_meaning Varchar2
399 --
400 -- DESIGN REFERENCES:
401 --
402 --
403 -- CHANGE HISTORY:
404 --
405 -- 24-Mar-2006 Jackey Li Created
406 --
407 --===========================================================================
408 FUNCTION Get_Lookup_Meaning(p_Lookup_Code IN VARCHAR2) RETURN VARCHAR2 IS
409
410 l_Procedure_Name VARCHAR2(30) := 'Get_Lookup_Meaning';
411 l_Lookup_Meaning Fnd_Lookup_Values.Meaning%TYPE := NULL;
412 l_Runtime_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
413 l_Procedure_Level NUMBER := Fnd_Log.Level_Procedure;
414 l_Statement_Level NUMBER := Fnd_Log.Level_Statement;
415 l_Exception_Level NUMBER := Fnd_Log.Level_Exception;
416
417 -- this cursor is to get looup_meaning under some lookup_code
418 CURSOR c_Lookup IS
419 SELECT Flv.Meaning
420 FROM Fnd_Lookup_Values Flv
421 WHERE Flv.LANGUAGE = Userenv('LANG')
422 AND Flv.Lookup_Type = 'JA_CN_DUPOBJECTS_TOKENS'
423 AND Flv.View_Application_Id = 0
424 AND Flv.Security_Group_Id = 0
425 AND Flv.Lookup_Code = p_Lookup_Code;
426
427 BEGIN
428 --log
429 IF (l_Procedure_Level >= l_Runtime_Level)
430 THEN
431 Fnd_Log.STRING(l_Procedure_Level,
432 l_Module_Prefix || '.' || l_Procedure_Name || '.begin',
433 'begin procedure');
434 END IF; --l_procedure_level >= l_runtime_level
435
436 IF p_Lookup_Code IS NULL
437 THEN
438 l_Lookup_Meaning := NULL;
439 ELSE
440 OPEN c_Lookup;
441 FETCH c_Lookup
442 INTO l_Lookup_Meaning;
443 IF c_Lookup%NOTFOUND
444 THEN
445 l_Lookup_Meaning := NULL;
446 END IF;
447 CLOSE c_Lookup;
448 END IF; --IF p_lookup_code IS NULL
449
450 --log
451 IF (l_Procedure_Level >= l_Runtime_Level)
452 THEN
453 Fnd_Log.STRING(l_Procedure_Level,
454 l_Module_Prefix || '.' || l_Procedure_Name || '.end',
455 'end procedure');
456 END IF; --l_procedure_level >= l_runtime_level
457
458 RETURN l_Lookup_Meaning;
459
460 END Get_Lookup_Meaning;
461
462 --==========================================================================
463 -- FUNCTION NAME:
464 --
465 -- Check_Nat_Number Public
466 --
467 -- DESCRIPTION:
468 --
469 -- This function is used to check if the given string is a natual number.
470 --
471 --
472 -- PARAMETERS:
473 -- In: p_subject the string need to check
474 --
475 -- RETURN:
476 -- BOOLEAN
477 --
478 -- DESIGN REFERENCES:
479 --
480 --
481 -- CHANGE HISTORY:
482 -- 11-Apr-2006 Jackey Li Created
483 --
484 --===========================================================================
485 FUNCTION Check_Nat_Number(p_Subject IN VARCHAR2) RETURN BOOLEAN IS
486 l_Tmp VARCHAR2(100);
487 l_Number NUMBER;
488 l_Mod NUMBER;
489 BEGIN
490 l_Tmp := p_Subject;
491 l_Number := To_Number(l_Tmp);
492 IF l_Number < 0
493 THEN
494 RETURN FALSE;
495 END IF;
496 l_Mod := MOD(l_Number, 2);
497 IF l_Mod = 1
498 OR l_Mod = 0
499 THEN
500 RETURN TRUE;
501 ELSE
502 RETURN FALSE;
503 END IF;
504
505 EXCEPTION
506 WHEN OTHERS THEN
507 RETURN FALSE;
508
509 END Check_Nat_Number;
510
511 --==========================================================================
512 -- PROCEDURE NAME:
513 --
514 -- Output_Conc Public
515 --
516 -- DESCRIPTION:
517 --
518 -- This procedure write data to concurrent output file
519 -- the data can be longer than 4000
520 --
521 -- PARAMETERS:
522 -- In: p_clob the content which need output to concurrent output
523 --
524 --
525 -- DESIGN REFERENCES:
526 --
527 --
528 -- CHANGE HISTORY:
529 --
530 -- 30-APR-2005: qugen.hu Created.
531 -- 27-APR-2005: Andrew.liu imported.
532 -- 31-OCT-2008: Yao Zhang Fix bug 7524912 changed
533 -- 21-Jun-2010: Choli Fix bug 9775669 changed
534 --===========================================================================
535
536 PROCEDURE Output_Conc(p_Clob IN CLOB) IS
537 --Max_Linesize NUMBER := 254; --fix bug 9775669 deleted
538 Max_Linesize NUMBER := 500; --fix bug 9775669 added
539 l_Pos_Tag NUMBER;
540 l_Pos NUMBER;
541 l_Len NUMBER;
542 l_Tmp NUMBER;
543 l_Tmp1 NUMBER;
544 l_Substr CLOB;
545 BEGIN
546 NULL;
547 --initalize
548 l_Pos := 1;
549 l_Len := Length(p_Clob);
550 WHILE l_Pos <= l_Len
551 LOOP
552 --get the XML tag from reverse direction
553 l_Tmp := l_Pos + Max_Linesize - 2 - l_Len;
554 --l_Pos_Tag := Instr(p_Clob, '>', l_Tmp);--fix bug 7524912 delete
555 l_Pos_Tag := Instr(p_Clob, '><', l_Tmp);--fix bug 7524912 add
556 --the pos didnot touch the end of string
557 l_Tmp1 := l_Pos - 1;
558
559 IF (l_Pos_Tag > l_Tmp1)
560 AND (l_Tmp < 0)
561 THEN
562 l_Tmp := l_Pos_Tag - l_Pos + 1;
563 --Fnd_File.Put(Fnd_File.Output, Substr(p_Clob, l_Pos, l_Tmp));--fix bug 7524912 delete
564 Fnd_File.PUT_LINE(Fnd_File.Output, Substr(p_Clob, l_Pos, l_Tmp));--fix bug 7524912 add
565 l_Pos := l_Pos_Tag + 1;
566 ELSE
567 l_Substr := Substr(p_Clob, l_Pos);
568 --Fnd_File.Put(Fnd_File.Output, l_Substr);--fix bug 7524912 delete
569 Fnd_File.PUT_LINE(Fnd_File.Output, l_Substr);--fix bug 7524912 add
570 l_Pos := l_Len + 1;
571
572 END IF;
573
574 END LOOP;
575 EXCEPTION
576 WHEN OTHERS THEN
577 Fnd_File.PUT_LINE(Fnd_File.log, 'Error:' || Sqlcode||Sqlerrm);
578 END Output_Conc;
579
580
581
582
583
584 --==========================================================================
585 -- FUNCTION NAME:
586 --
587 -- Check_Account_Level Public
588 --
589 -- DESCRIPTION:
590 --
591 -- This procedure check the account level of an account. If the account
592 -- level is not null, and is a natural number and less than 16 than return
593 -- TRUE, else FALSE.
594 --
595 -- PARAMETERS:
596 -- In: P_LEVEL the account level
597 --
598 --
599 -- DESIGN REFERENCES:
600 --
601 --
602 -- CHANGE HISTORY:
603 --
604 -- 18-MAY-2005: Andrew.liu Created.
605 --
606 --===========================================================================
607 FUNCTION Check_Account_Level(p_Level IN VARCHAR2) RETURN BOOLEAN IS
608 l_Na_Level VARCHAR2(100) := p_Level;
609 l_Number NUMBER;
610 BEGIN
611 l_Number := To_Number(l_Na_Level);
612 IF Instr(l_Na_Level, '.', 1, 1) > 0 --not a integer
613 OR Instr(To_Char(l_Number), '.', 1, 1) > 0 --not a integer
614 OR l_Number < 1 --less than 1
615 THEN
616 RETURN FALSE;
617 END IF;
618
619 IF l_Na_Level IS NOT NULL
620 AND l_Number < 16
621 THEN
622 RETURN TRUE;
623 ELSE
624 RETURN FALSE;
625 END IF;
626 EXCEPTION
627 WHEN OTHERS THEN
628 RETURN FALSE;
629 END Check_Account_Level;
630
631 --==========================================================================
632 -- FUNCTION NAME:
633 -- get_lookup_code Public
634 --
635 -- DESCRIPTION:
636 -- This function is used to get lookup code of lookup meaning,
637 -- PARAMETERS:
638 -- In: p_lookup_meaning lookup meaning
639 -- p_lookup_type lookup code
640 -- p_view_application_id view application, DEFAULT 0
641 -- p_security_group_id security group
642 --
643 --
644 -- DESIGN REFERENCES:
645 -- None
646 --
647 -- CHANGE HISTORY:
648 -- 06/03/2006 Shujuan Yan Created
649 --==========================================================================
650 FUNCTION Get_Lookup_Code(p_Lookup_Meaning IN VARCHAR2,
651 p_Lookup_Type IN VARCHAR2,
652 p_View_Application_Id IN NUMBER DEFAULT 0,
653 p_Security_Group_Id IN NUMBER DEFAULT 0)
654 RETURN VARCHAR2 IS
655
656 l_Procedure_Name VARCHAR2(30) := 'Get_Lookup_Code';
657 l_Lookup_Code Fnd_Lookup_Values.Lookup_Code%TYPE := NULL;
658 l_Runtime_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
659 l_Procedure_Level NUMBER := Fnd_Log.Level_Procedure;
660 --l_statement_level NUMBER := fnd_log.level_statement;
661 --l_exception_level NUMBER := fnd_log.level_exception;
662
663 -- this cursor is to get looup_meaning under some lookup_code
664 CURSOR c_Lookup IS
665 SELECT Flv.Lookup_Code
666 FROM Fnd_Lookup_Values Flv
667 WHERE Flv.LANGUAGE = Userenv('LANG')
668 AND Flv.Lookup_Type = p_Lookup_Type
669 AND Flv.Meaning = p_Lookup_Meaning
670 AND Flv.View_Application_Id = p_View_Application_Id
671 AND Flv.Security_Group_Id = p_Security_Group_Id;
672
673 BEGIN
674 --log for debug
675 IF (l_Procedure_Level >= l_Runtime_Level)
676 THEN
677 Fnd_Log.STRING(l_Procedure_Level,
678 l_Module_Prefix || '.' || l_Procedure_Name || '.begin',
679 'begin procedure');
680 END IF; --l_procedure_level >= l_runtime_level
681
682 IF p_Lookup_Meaning IS NULL
683 THEN
684 l_Lookup_Code := NULL;
685 ELSE
686 OPEN c_Lookup;
687 FETCH c_Lookup
688 INTO l_Lookup_Code;
689 IF c_Lookup%NOTFOUND
690 THEN
691 l_Lookup_Code := NULL;
692 END IF;
693 CLOSE c_Lookup;
694 END IF; --IF p_lookup_code IS NULL
695
696 --log for debug
697 IF (l_Procedure_Level >= l_Runtime_Level)
698 THEN
699 Fnd_Log.STRING(l_Procedure_Level,
700 l_Module_Prefix || '.' || l_Procedure_Name || '.end',
701 'end procedure');
702 END IF; --l_procedure_level >= l_runtime_level
703
704 RETURN l_Lookup_Code;
705
706 END Get_Lookup_Code;
707 --==========================================================================
708 -- PROCEDURE NAME:
709 -- Submit_Charset_Conversion Public
710 --
711 -- DESCRIPTION:
712 -- This function is used to submit charset conversion concurrent.
713 -- PARAMETERS:
714 -- In: p_xml_request_id xml publisher concurrent request id
715 -- p_source_charset source charset
716 -- p_destination_charset destination charset
717 -- p_source_separator source separator
718 -- Out: x_charset_request_id charset conversion request id
719 -- x_result_flag result flag
720 --
721 --
722 -- DESIGN REFERENCES:
723 -- None
724 --
725 -- CHANGE HISTORY:
726 -- 06/03/2006 Shujuan Yan Created
727 --==========================================================================
728 PROCEDURE Submit_Charset_Conversion(p_Xml_Request_Id IN NUMBER,
729 p_Source_Charset IN VARCHAR2,
730 p_Destination_Charset IN VARCHAR2,
731 p_Source_Separator IN VARCHAR2,
732 x_Charset_Request_Id OUT NOCOPY NUMBER,
733 x_Result_Flag OUT NOCOPY VARCHAR2) IS
734
735 l_Procedure_Name VARCHAR2(30) := 'Submit_Charset_Conversion ';
736 l_Runtime_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
737 l_Procedure_Level NUMBER := Fnd_Log.Level_Procedure;
738 --l_statement_level NUMBER := fnd_log.level_statement;
739 --l_exception_level NUMBER := fnd_log.level_exception;
740 l_Complete_Flag BOOLEAN;
741 l_Phase VARCHAR2(100);
742 l_Status VARCHAR2(100);
743 l_Del_Phase VARCHAR2(100);
744 l_Del_Status VARCHAR2(100);
745 l_Message VARCHAR2(1000);
746
747 BEGIN
748 --log for debug
749 IF (l_Procedure_Level >= l_Runtime_Level)
750 THEN
751 Fnd_Log.STRING(l_Procedure_Level,
752 l_Module_Prefix || '.' || l_Procedure_Name || '.begin',
753 'begin procedure');
754 END IF; --l_procedure_level >= l_runtime_level
755 -- submit charset conversion concurrent program
756 x_Charset_Request_Id := Fnd_Request.Submit_Request('JA',
757 'JACNCCCP',
758 NULL,
759 SYSDATE,
760 FALSE,
761 p_Xml_Request_Id,
762 p_Source_Charset,
763 p_Destination_Charset,
764 p_Source_Separator);
765
766 IF (x_Charset_Request_Id <= 0 OR x_Charset_Request_Id IS NULL)
767 THEN
768 x_Result_Flag := 'Error';
769 ELSE
770 COMMIT;
771 --Wait for concurrent complete
772 l_Complete_Flag := Fnd_Concurrent.Wait_For_Request(x_Charset_Request_Id,
773 1,
774 0,
775 l_Phase,
776 l_Status,
777 l_Del_Phase,
778 l_Del_Status,
779 l_Message);
780 IF l_Complete_Flag = FALSE
781 OR Get_Lookup_Code(p_Lookup_Meaning => l_Status,
782 p_Lookup_Type => 'CP_STATUS_CODE') <> 'C'
783 THEN
784 x_Result_Flag := 'Error';
785 ELSE
786 x_Result_Flag := 'Success';
787 END IF; -- l_complete_flag = false
788 END IF; -- (x_xml_request_id <= 0 OR x_xml_request_id IS NULL)
789
790 --log for debug
791 IF (l_Procedure_Level >= l_Runtime_Level)
792 THEN
793 Fnd_Log.STRING(l_Procedure_Level,
794 l_Module_Prefix || '.' || l_Procedure_Name || '.end',
795 'end procedure');
796 END IF; --l_procedure_level >= l_runtime_level
797 EXCEPTION
798 WHEN OTHERS THEN
799 --log for debug
800 IF (Fnd_Log.Level_Unexpected >= Fnd_Log.g_Current_Runtime_Level)
801 THEN
802 Fnd_Log.STRING(Fnd_Log.Level_Unexpected,
803 l_Module_Prefix || l_Procedure_Name ||
804 '. OTHER_EXCEPTION ',
805 SQLCODE || SQLERRM);
806 END IF; -- fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
807 RAISE;
808 END Submit_Charset_Conversion;
809
810 --==========================================================================
811 -- PROCEDURE NAME:
812 -- Submit_XML_Charset_Conversion Public
813 --
814 -- DESCRIPTION:
815 -- This function is used to submit charset conversion concurrent
816 -- for XML files.
817 -- PARAMETERS:
818 -- In: p_xml_request_id xml publisher concurrent request id
819 -- p_source_charset source charset
820 -- p_destination_charset destination charset
821 -- p_source_separator source separator
822 -- Out: x_charset_request_id charset conversion request id
823 -- x_result_flag result flag
824 --
825 --
826 -- DESIGN REFERENCES:
827 -- None
828 --
829 -- CHANGE HISTORY:
830 -- 22/03/2010 Jason Liu Created
831 --==========================================================================
832 PROCEDURE Submit_XML_Charset_Conversion(p_Xml_Request_Id IN NUMBER,
833 p_Source_Charset IN VARCHAR2,
834 p_Destination_Charset IN VARCHAR2,
835 p_Source_Separator IN VARCHAR2,
836 x_Charset_Request_Id OUT NOCOPY NUMBER,
837 x_Result_Flag OUT NOCOPY VARCHAR2) IS
838
839 l_Procedure_Name VARCHAR2(30) := 'Submit_XML_Charset_Conversion ';
840 l_Runtime_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
841 l_Procedure_Level NUMBER := Fnd_Log.Level_Procedure;
842 --l_statement_level NUMBER := fnd_log.level_statement;
843 --l_exception_level NUMBER := fnd_log.level_exception;
844 l_Complete_Flag BOOLEAN;
845 l_Phase VARCHAR2(100);
846 l_Status VARCHAR2(100);
847 l_Del_Phase VARCHAR2(100);
848 l_Del_Status VARCHAR2(100);
849 l_Message VARCHAR2(1000);
850
851 BEGIN
852 --log for debug
853 IF (l_Procedure_Level >= l_Runtime_Level)
854 THEN
855 Fnd_Log.STRING(l_Procedure_Level,
856 l_Module_Prefix || '.' || l_Procedure_Name || '.begin',
857 'begin procedure');
858 END IF; --l_procedure_level >= l_runtime_level
859 -- submit charset conversion concurrent program
860 x_Charset_Request_Id := Fnd_Request.Submit_Request('JA',
861 'JACNCCXP',
862 NULL,
863 SYSDATE,
864 FALSE,
865 p_Xml_Request_Id,
866 p_Source_Charset,
867 p_Destination_Charset,
868 p_Source_Separator);
869
870 IF (x_Charset_Request_Id <= 0 OR x_Charset_Request_Id IS NULL)
871 THEN
872 x_Result_Flag := 'Error';
873 ELSE
874 COMMIT;
875 --Wait for concurrent complete
876 l_Complete_Flag := Fnd_Concurrent.Wait_For_Request(x_Charset_Request_Id,
877 1,
878 0,
879 l_Phase,
880 l_Status,
881 l_Del_Phase,
882 l_Del_Status,
883 l_Message);
884 IF l_Complete_Flag = FALSE
885 OR Get_Lookup_Code(p_Lookup_Meaning => l_Status,
886 p_Lookup_Type => 'CP_STATUS_CODE') <> 'C'
887 THEN
888 x_Result_Flag := 'Error';
889 ELSE
890 x_Result_Flag := 'Success';
891 END IF; -- l_complete_flag = false
892 END IF; -- (x_xml_request_id <= 0 OR x_xml_request_id IS NULL)
893
894 --log for debug
895 IF (l_Procedure_Level >= l_Runtime_Level)
896 THEN
897 Fnd_Log.STRING(l_Procedure_Level,
898 l_Module_Prefix || '.' || l_Procedure_Name || '.end',
899 'end procedure');
900 END IF; --l_procedure_level >= l_runtime_level
901 EXCEPTION
902 WHEN OTHERS THEN
903 --log for debug
904 IF (Fnd_Log.Level_Unexpected >= Fnd_Log.g_Current_Runtime_Level)
905 THEN
906 Fnd_Log.STRING(Fnd_Log.Level_Unexpected,
907 l_Module_Prefix || l_Procedure_Name ||
908 '. OTHER_EXCEPTION ',
909 SQLCODE || SQLERRM);
910 END IF; -- fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
911 RAISE;
912 END Submit_XML_Charset_Conversion;
913
914
915 --==========================================================================
916 -- PROCEDURE NAME:
917 -- Change_Output_Filename Public
918 --
919 -- DESCRIPTION:
920 -- This function is used to submit the concurrent program of change output file name
921 -- PARAMETERS:
922 -- In: p_xml_request_id xml publisher concurrent request id
923 -- p_destination_charset destination charset
924 -- p_destination_filename destination filename
925 -- Out: x_charset_request_id charset conversion request id
926 -- x_result_flag result flag
927 --
928 --
929 -- DESIGN REFERENCES:
930 -- None
931 --
932 -- CHANGE HISTORY:
933 -- 06/03/2006 Shujuan Yan Created
934 --==========================================================================
935 PROCEDURE Change_Output_Filename(p_Xml_Request_Id IN NUMBER,
936 p_Destination_Charset IN VARCHAR2,
937 p_Destination_Filename IN VARCHAR2,
938 x_Filename_Request_Id OUT NOCOPY NUMBER,
939 x_Result_Flag OUT NOCOPY VARCHAR2) IS
940
941 l_Procedure_Name VARCHAR2(30) := 'Change_Output_Filename';
942 l_Runtime_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
943 l_Procedure_Level NUMBER := Fnd_Log.Level_Procedure;
944 --l_statement_level NUMBER := fnd_log.level_statement;
945 --l_exception_level NUMBER := fnd_log.level_exception;
946 l_Complete_Flag BOOLEAN;
947 l_Phase VARCHAR2(100);
948 l_Status VARCHAR2(100);
949 l_Del_Phase VARCHAR2(100);
950 l_Del_Status VARCHAR2(100);
951 l_Message VARCHAR2(1000);
952
953 BEGIN
954 --log for debug
955 IF (l_Procedure_Level >= l_Runtime_Level)
956 THEN
957 Fnd_Log.STRING(l_Procedure_Level,
958 l_Module_Prefix || '.' || l_Procedure_Name || '.begin',
959 'begin procedure');
960 END IF; --l_procedure_level >= l_runtime_level
961 -- submit change file name concurrent program
962 x_Filename_Request_Id := Fnd_Request.Submit_Request('JA',
963 'JACNFNCP',
964 NULL,
965 SYSDATE,
966 FALSE,
967 p_Xml_Request_Id,
968 p_Destination_Charset,
969 p_Destination_Filename);
970
971 IF (x_Filename_Request_Id <= 0 OR x_Filename_Request_Id IS NULL)
972 THEN
973 x_Result_Flag := 'Error';
974 ELSE
975 COMMIT;
976 --Wait for concurrent complete
977 l_Complete_Flag := Fnd_Concurrent.Wait_For_Request(x_Filename_Request_Id,
978 1,
979 0,
980 l_Phase,
981 l_Status,
982 l_Del_Phase,
983 l_Del_Status,
984 l_Message);
985 IF l_Complete_Flag = FALSE
986 OR Get_Lookup_Code(p_Lookup_Meaning => l_Status,
987 p_Lookup_Type => 'CP_STATUS_CODE') <> 'C'
988 THEN
989 x_Result_Flag := 'Error';
990 ELSE
991 x_Result_Flag := 'Success';
992 END IF; -- l_complete_flag = false
993 END IF; -- (x_xml_request_id <= 0 OR x_xml_request_id IS NULL)
994
995 --log for debug
996 IF (l_Procedure_Level >= l_Runtime_Level)
997 THEN
998 Fnd_Log.STRING(l_Procedure_Level,
999 l_Module_Prefix || '.' || l_Procedure_Name || '.end',
1000 'end procedure');
1001 END IF; --l_procedure_level >= l_runtime_level
1002 EXCEPTION
1003 WHEN OTHERS THEN
1004 --log for debug
1005 IF (Fnd_Log.Level_Unexpected >= Fnd_Log.g_Current_Runtime_Level)
1006 THEN
1007 Fnd_Log.STRING(Fnd_Log.Level_Unexpected,
1008 l_Module_Prefix || l_Procedure_Name ||
1009 '. OTHER_EXCEPTION ',
1010 SQLCODE || SQLERRM);
1011 END IF; -- fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1012 RAISE;
1013 END Change_Output_Filename;
1014
1015 --==========================================================================
1016 -- FUNCTION NAME:
1017 --
1018 -- Check_Cash_Related_Account Public
1019 --
1020 -- DESCRIPTION:
1021 --
1022 -- This function is used to check if the gl code combination passed in is -- Cash Related.
1023 --
1024 -- PARAMETERS:
1025 -- In: p_set_of_bks_id Identifier of GL set of book
1026 -- p_acc_flex GL code combination
1027 --
1028 --
1029 -- DESIGN REFERENCES:
1030 --
1031 --
1032 -- CHANGE HISTORY:
1033 --
1034 -- 30-MAY-2005: Donghai Wang Created
1035 --
1036 --===========================================================================
1037 FUNCTION Check_Cash_Related_Account(p_Set_Of_Bks_Id IN NUMBER,
1038 p_Acc_Flex IN VARCHAR2)
1039 RETURN BOOLEAN IS
1040 l_Id_Flex_Num Fnd_Id_Flex_Structures.Id_Flex_Num%TYPE;
1041 l_Delimiter Fnd_Id_Flex_Structures.Concatenated_Segment_Delimiter%TYPE;
1042 l_Seq_Account NUMBER;
1043 l_Account_Segment_Flag Fnd_Segment_Attribute_Values.Attribute_Value%TYPE;
1044 l_Account_Segment Fnd_Id_Flex_Segments.Segment_Name%TYPE;
1045
1046 l_Cash_Related_Flag BOOLEAN;
1047 l_Cash_Acct_Count NUMBER;
1048
1049 CURSOR c_Coa_Infor IS
1050 SELECT Id_Flex_Num,
1051 Concatenated_Segment_Delimiter
1052 FROM Fnd_Id_Flex_Structures
1053 WHERE Application_Id = '101'
1054 AND Id_Flex_Code = 'GL#'
1055 AND Id_Flex_Num =
1056 (SELECT Chart_Of_Accounts_Id
1057 FROM Gl_Sets_Of_Books
1058 WHERE Set_Of_Books_Id = p_Set_Of_Bks_Id);
1059
1060 CURSOR c_Coa_Segments IS
1061 SELECT Application_Column_Name
1062 FROM Fnd_Id_Flex_Segments
1063 WHERE Application_Id = 101
1064 AND Id_Flex_Code = 'GL#'
1065 AND Id_Flex_Num = l_Id_Flex_Num
1066 AND Enabled_Flag = 'Y'
1067 AND Display_Flag = 'Y'
1068 ORDER BY Segment_Num;
1069
1070 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1071 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1072 l_Proc_Name VARCHAR2(100) := 'Check_Cash_Related_Account';
1073
1074 BEGIN
1075 --log for debug
1076 IF (l_Proc_Level >= l_Dbg_Level)
1077 THEN
1078 Fnd_Log.STRING(l_Proc_Level,
1079 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
1080 'Enter procedure');
1081 Fnd_Log.STRING(l_Proc_Level,
1082 l_Module_Prefix || '.' || l_Proc_Name || '.parameter',
1083 'p_set_of_bks_id ' || p_Set_Of_Bks_Id);
1084 Fnd_Log.STRING(l_Proc_Level,
1085 l_Module_Prefix || '.' || l_Proc_Name || '.parameter',
1086 'p_acc_flex ' || p_Acc_Flex);
1087
1088 END IF; --(l_proc_level >= l_dbg_level)
1089
1090 --To get coa id of current gl books and delimiter for gl account accordingly.
1091 OPEN c_Coa_Infor;
1092 FETCH c_Coa_Infor
1093 INTO l_Id_Flex_Num, l_Delimiter;
1094 CLOSE c_Coa_Infor;
1095
1096 --To evaluate the sequence of account segment in gl account
1097 l_Seq_Account := 0;
1098 FOR l_Coa_Segment IN c_Coa_Segments
1099 LOOP
1100 l_Seq_Account := l_Seq_Account + 1;
1101
1102 SELECT Attribute_Value
1103 INTO l_Account_Segment_Flag
1104 FROM Fnd_Segment_Attribute_Values
1105 WHERE Application_Id = 101
1106 AND Id_Flex_Code = 'GL#'
1107 AND Id_Flex_Num = l_Id_Flex_Num
1108 AND Application_Column_Name =
1109 l_Coa_Segment.Application_Column_Name
1110 AND Segment_Attribute_Type = 'GL_ACCOUNT';
1111
1112 EXIT WHEN l_Account_Segment_Flag = 'Y';
1113 END LOOP; --l_coa_segment IN c_coa_segments
1114
1115 --Extract account segment from GL account
1116 SELECT Substr(p_Acc_Flex,
1117 Instr(p_Acc_Flex, l_Delimiter, 1, l_Seq_Account - 1) + 1,
1118 (Instr(p_Acc_Flex, l_Delimiter, 1, l_Seq_Account) -
1119 Instr(p_Acc_Flex, l_Delimiter, 1, l_Seq_Account - 1) - 1))
1120 INTO l_Account_Segment
1121 FROM Dual;
1122
1123 --To check if current account segment is cash related.
1124 /* SELECT COUNT(account_segment_value)
1125 INTO l_cash_acct_count
1126 FROM ja_cn_cash_accounts_all
1127 WHERE set_of_books_id = p_set_of_bks_id
1128 AND account_segment_value = l_account_segment;*/
1129
1130 IF l_Cash_Acct_Count > 0
1131 THEN
1132 l_Cash_Related_Flag := TRUE;
1133 ELSE
1134 l_Cash_Related_Flag := FALSE;
1135 END IF; --l_cash_acct_count>0
1136
1137 IF (l_Proc_Level >= l_Dbg_Level)
1138 THEN
1139 Fnd_Log.STRING(l_Proc_Level,
1140 l_Module_Prefix || '.' || l_Proc_Name || '.end',
1141 'Exit procedure');
1142 END IF; --( l_proc_level >= l_dbg_level )
1143
1144 RETURN(l_Cash_Related_Flag);
1145
1146 EXCEPTION
1147 WHEN OTHERS THEN
1148 IF (l_Proc_Level >= l_Dbg_Level)
1149 THEN
1150 Fnd_Log.STRING(l_Proc_Level,
1151 l_Module_Prefix || '.' || l_Proc_Name ||
1152 '. Other_Exception ',
1153 SQLCODE || ':' || SQLERRM);
1154 END IF; --(l_proc_level >= l_dbg_level)
1155
1156 RETURN(FALSE);
1157
1158 END Check_Cash_Related_Account;
1159
1160 --==========================================================================
1161 -- FUNCTION NAME:
1162 --
1163 -- Check_Accounting_Period_Range Public
1164 --
1165 -- DESCRIPTION:
1166 --
1167 -- This function is used to check whether all the periods' status within
1168 -- the range are 'C' or 'P'
1169 --
1170 --
1171 -- PARAMETERS:
1172 -- In: p_legal_entity_id Legal entity ID
1173 -- In: p_start_period_name Start period name
1174 -- In: p_end_period_name End period name
1175 --
1176 -- RETURN:
1177 -- True for success, otherwise False
1178 --
1179 -- DESIGN REFERENCES:
1180 --
1181 --
1182 -- CHANGE HISTORY:
1183 --
1184 -- 04-July-2006 Joseph Wang Created
1185 --
1186 --===========================================================================
1187 FUNCTION Check_Accounting_Period_Range(p_Start_Period_Name IN VARCHAR2,
1188 p_End_Period_Name IN VARCHAR2,
1189 p_Legal_Entity_Id NUMBER,
1190 p_ledger_id IN NUMBER--added by lyb
1191 )
1192 RETURN BOOLEAN IS
1193 l_Start_Date DATE;
1194 l_End_Date DATE;
1195 l_All_Period_Number INTEGER;
1196 l_Closed_Period_Number INTEGER;
1197
1198 l_Sob_Id NUMBER;
1199 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1200 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1201 l_Proc_Name VARCHAR2(100) := 'Check_Accounting_Period_Range';
1202 BEGIN
1203
1204 -- l_Sob_Id := Ja_Cn_Utility.Get_Sob(p_Legal_Entity_Id);--updated by lyb
1205 SELECT Start_Date
1206 INTO l_Start_Date
1207 FROM Gl_Period_Statuses
1208 WHERE ledger_id=p_ledger_id--Set_Of_Books_Id = l_Sob_Id,--updated by lyb
1209 AND Application_Id = 101
1210 AND Period_Name = p_Start_Period_Name;
1211
1212 SELECT End_Date
1213 INTO l_End_Date
1214 FROM Gl_Period_Statuses
1215 WHERE ledger_id=p_ledger_id--Set_Of_Books_Id = l_Sob_Id--updated by lyb
1216 AND Application_Id = 101
1217 AND Period_Name = p_End_Period_Name;
1218
1219 SELECT COUNT(*)
1220 INTO l_All_Period_Number
1221 FROM Gl_Period_Statuses
1222 WHERE ledger_id=p_ledger_id--Set_Of_Books_Id = l_Sob_Id--updated by lyb
1223 AND Application_Id = 101
1224 AND ((Start_Date BETWEEN l_Start_Date AND l_End_Date) AND
1225 (End_Date BETWEEN l_Start_Date AND l_End_Date));
1226
1227 SELECT COUNT(*)
1228 INTO l_Closed_Period_Number
1229 FROM Gl_Period_Statuses
1230 WHERE ledger_id=p_ledger_id--Set_Of_Books_Id = l_Sob_Id,--updated by lyb
1231 AND Application_Id = 101
1232 AND ((Start_Date BETWEEN l_Start_Date AND l_End_Date) AND
1233 (End_Date BETWEEN l_Start_Date AND l_End_Date))
1234 AND (Closing_Status = 'C' OR Closing_Status = 'P');
1235
1236 IF (l_All_Period_Number <> l_Closed_Period_Number)
1237 THEN
1238 RETURN FALSE;
1239 ELSE
1240 RETURN TRUE;
1241 END IF;
1242
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 IF (l_Proc_Level >= l_Dbg_Level)
1246 THEN
1247 Fnd_Log.STRING(l_Proc_Level,
1248 l_Module_Prefix || '.' || l_Proc_Name ||
1249 '. Other_Exception ',
1250 SQLCODE || ':' || SQLERRM);
1251 END IF; --(l_proc_level >= l_dbg_level)
1252
1253 RETURN(FALSE);
1254 END Check_Accounting_Period_Range;
1255
1256 FUNCTION Fetch_Account_Structure(p_Le_Id IN NUMBER) RETURN VARCHAR2 IS
1257 l_Acc_Stru_Tablename VARCHAR2(100) := 'JA_CN_ACCOUNT_STRUCTURES_KFV';
1258 l_Sql VARCHAR2(1000);
1259 l_Result VARCHAR2(2000);
1260 BEGIN
1261 l_Sql := 'SELECT nvl(jcask.concatenated_segments, '''')
1262 FROM ' || l_Acc_Stru_Tablename ||
1263 ' jcask
1264 ,ja_cn_system_parameters_all jcsp
1265 WHERE jcask.account_structure_id = jcsp.account_structure_id
1266 AND jcsp.legal_entity_id = :1';
1267 EXECUTE IMMEDIATE l_Sql
1268 INTO l_Result
1269 USING p_Le_Id;
1270 RETURN l_Result;
1271 END Fetch_Account_Structure;
1272 --==========================================================================
1273 -- PROCEDURE NAME:
1274 --
1275 -- Populate_Ledger_Le_Bsv_Gt Public
1276 --
1277 -- DESCRIPTION:
1278 --
1279 -- This procedure is used to populate the balance segment of currenct
1280 -- legal entity and ledger into temporary table ja_cn_ledger_le_bsv_gt
1281 --
1282 --
1283 -- PARAMETERS:
1284 -- In: p_legal_entity_id Legal entity ID
1285 -- In: p_ledger_id Ledger ID
1286 --
1287 --
1288 -- DESIGN REFERENCES:
1289 --
1290 --
1291 -- CHANGE HISTORY:
1292 --
1293 -- 12-Mar-07 Qingjun Zhao Created
1294 --
1295 FUNCTION Populate_Ledger_Le_Bsv_Gt(p_Ledger_Id IN NUMBER,
1296 p_Legal_Entity_Id IN NUMBER)
1297 RETURN VARCHAR2 IS
1298 l_Ledger_Category VARCHAR2(30);
1299 l_Bsv_Option VARCHAR2(1);
1300 l_Bsv_Vset_Id NUMBER;
1301
1302 l_Fv_Table Fnd_Flex_Validation_Tables.Application_Table_Name%TYPE;
1303 l_Fv_Col Fnd_Flex_Validation_Tables.Value_Column_Name%TYPE;
1304 l_Fv_Type Fnd_Flex_Value_Sets.Validation_Type%TYPE;
1305 l_Insertsql Dbms_Sql.Varchar2s;
1306 l_Line_No NUMBER := 0;
1307 l_Cursorid INTEGER;
1308 l_Return_No NUMBER;
1309 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1310 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1311 l_Proc_Name VARCHAR2(100) := 'Populate_Ledger_Le_Bsv_Gt';
1312
1313 BEGIN
1314 --log for debug
1315 IF (l_Proc_Level >= l_Dbg_Level)
1316 THEN
1317 Fnd_Log.STRING(l_Proc_Level,
1318 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
1319 'Enter procedure');
1320
1321 Fnd_Log.STRING(l_Proc_Level,
1322 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
1323 'p_ledger_id ' || p_ledger_Id);
1324
1325 Fnd_Log.STRING(l_Proc_Level,
1326 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
1327 'p_legal_entity_id ' || p_legal_entity_id);
1328
1329 END IF; --(l_proc_level >= l_dbg_level)
1330 IF (p_Ledger_Id IS NULL)
1331 THEN
1332 -- Ledger ID is not passed, so return F (i.e. FAIL)
1333 RETURN 'F1';
1334
1335 END IF;
1336
1337 -- First, get its ledger category code and BSV option code
1338 SELECT Ledger_Category_Code,
1339 Nvl(Bal_Seg_Value_Option_Code, 'A'),
1340 Bal_Seg_Value_Set_Id
1341 INTO l_Ledger_Category,
1342 l_Bsv_Option,
1343 l_Bsv_Vset_Id
1344 FROM Gl_Ledgers
1345 WHERE Ledger_Id = p_Ledger_Id;
1346
1347 IF (l_Ledger_Category <> 'PRIMARY' AND l_Ledger_Category <> 'SECONDARY' AND
1348 l_Ledger_Category <> 'ALC')
1349 THEN
1350 -- We don't handle NONE ledgers, which haven't been set up properly yet.
1351 -- Or, invalid ledger cateogry codes of the passed ledger.
1352 RETURN 'F2';
1353
1354
1355
1356 END IF; -- IF (l_ledger_category <> 'PRIMARY' ...
1357
1358 --
1359 -- Insert segment values from GL_LEDGER_NORM_SEG_VALS if the BSV option is
1360 -- Specific (i.e. I)
1361 --
1362 IF (l_Bsv_Option = 'I')
1363 THEN
1364 -- Insert rows for the passed ledger and its associated ALC Ledgers
1365 INSERT INTO Ja_Cn_Ledger_Le_Bsv_Gt
1366 (Ledger_Id,
1367 Ledger_Category_Code,
1368 Chart_Of_Accounts_Id,
1369 Bal_Seg_Value_Option_Code,
1370 Bal_Seg_Value_Set_Id,
1371 Bal_Seg_Value,
1372 Legal_Entity_Id,
1373 Start_Date,
1374 End_Date)
1375 -- XLE uptake: Changed to get the LE name from the new XLE tables
1376 SELECT Lg.Ledger_Id,
1377 Lg.Ledger_Category_Code,
1378 Lg.Chart_Of_Accounts_Id,
1379 Lg.Bal_Seg_Value_Option_Code,
1380 Lg.Bal_Seg_Value_Set_Id,
1381 Bsv.Segment_Value,
1382 Bsv.Legal_Entity_Id,
1383 Bsv.Start_Date,
1384 Bsv.End_Date
1385 FROM Gl_Ledgers Lg,
1386 Gl_Ledger_Relationships Rs,
1387 Gl_Ledger_Norm_Seg_Vals Bsv,
1388 Gl_Ledgers Lgr_c
1389 WHERE ((Rs.Relationship_Type_Code = 'NONE' AND
1390 Rs.Target_Ledger_Id = p_Ledger_Id) OR
1391 (Rs.Target_Ledger_Category_Code = 'ALC' AND
1392 Rs.Relationship_Type_Code IN ('SUBLEDGER', 'JOURNAL') AND
1393 Rs.Source_Ledger_Id = p_Ledger_Id))
1394 AND Rs.Application_Id = 101
1395 AND Lg.Ledger_Id = Rs.Target_Ledger_Id
1396 AND Bsv.Ledger_Id = p_Ledger_Id
1397 AND Rs.Target_Ledger_Id = Lgr_c.Ledger_Id
1398 AND Nvl(Lgr_c.Complete_Flag, 'Y') = 'Y'
1399 AND Bsv.Segment_Type_Code = 'B'
1400 -- We should exclude segment values with status code = 'D' since they
1401 -- will be deleted by the flatten program when config is confirmed
1402 -- AND bsv.status_code IS NULL
1403 AND Nvl(Bsv.Status_Code, 'I') <> 'D'
1404 AND Bsv.Legal_Entity_Id = p_Legal_Entity_Id;
1405
1406
1407
1408 ELSIF (l_Bsv_Option = 'A')
1409 THEN
1410 --
1411 -- Insert segment values from the balancing flex value set if the BSV option is
1412 -- All (i.e. A)
1413 --
1414 SELECT Nvl(Fvt.Application_Table_Name, 'FND_FLEX_VALUES'),
1415 Nvl(Fvt.Value_Column_Name, 'FLEX_VALUE'),
1416 Fvs.Validation_Type
1417 INTO l_Fv_Table,
1418 l_Fv_Col,
1419 l_Fv_Type
1420 FROM Fnd_Flex_Value_Sets Fvs,
1421 Fnd_Flex_Validation_Tables Fvt
1422 WHERE Fvs.Flex_Value_Set_Id = l_Bsv_Vset_Id
1423 AND Fvt.Flex_Value_Set_Id(+) = Fvs.Flex_Value_Set_Id;
1424
1425 -- Build INSERT statement of the dynamic INSERT SQL
1426 l_Line_No := l_Line_No + 1;
1427 l_Insertsql(l_Line_No) := 'INSERT INTO JA_CN_LEDGER_LE_BSV_GT';
1428 l_Line_No := l_Line_No + 1;
1429 l_Insertsql(l_Line_No) := '(LEDGER_ID, LEDGER_CATEGORY_CODE, ';
1430 l_Line_No := l_Line_No + 1;
1431 l_Insertsql(l_Line_No) := ' CHART_OF_ACCOUNTS_ID, BAL_SEG_VALUE_OPTION_CODE, BAL_SEG_VALUE_SET_ID, ';
1432 l_Line_No := l_Line_No + 1;
1433 l_Insertsql(l_Line_No) := ' BAL_SEG_VALUE, LEGAL_ENTITY_ID, ';
1434 l_Line_No := l_Line_No + 1;
1435 l_Insertsql(l_Line_No) := ' START_DATE, END_DATE) ';
1436
1437 -- Build SELECT statement of the dynamic INSERT SQL
1438
1439 -- Columns: LEDGER_ID, LEDGER_CATEGORY_CODE
1440 l_Line_No := l_Line_No + 1;
1441 l_Insertsql(l_Line_No) := 'SELECT lg.LEDGER_ID, lg.LEDGER_CATEGORY_CODE, ';
1442
1443 -- Columns: CHART_OF_ACCOUNTS_ID, BAL_SEG_VALUE_OPTION_CODE
1444 l_Line_No := l_Line_No + 1;
1445 l_Insertsql(l_Line_No) := ' lg.CHART_OF_ACCOUNTS_ID, lg.BAL_SEG_VALUE_OPTION_CODE, ';
1446
1447 -- Columns: BAL_SEG_VALUE_SET_ID, BAL_SEG_COLUMNE_NAME, BAL_SEG_VALUE
1448 l_Line_No := l_Line_No + 1;
1449 l_Insertsql(l_Line_No) := ' lg.BAL_SEG_VALUE_SET_ID, bsv.' ||
1450 l_Fv_Col || ', ';
1451
1452 -- Columns: LEGAL_ENTITY_ID, LEGAL_ENTITY_NAME, START_DATE, END_DATE
1453 -- Note: LE ID and Name are always NULL for ALL BSV option.
1454 l_Line_No := l_Line_No + 1;
1455 IF (l_Fv_Type <> 'F')
1456 THEN
1457 l_Insertsql(l_Line_No) := p_Legal_Entity_Id ||
1458 ', bsv.START_DATE_ACTIVE, bsv.END_DATE_ACTIVE ';
1459 ELSE
1460 l_Insertsql(l_Line_No) := p_Legal_Entity_Id ||
1461 ', NULL, NULL ';
1462 END IF;
1463
1464 -- Column: RELATIONSHIP_ENABLED_FLAG
1465 --l_Line_No := l_Line_No + 1;
1466 --l_Insertsql(l_Line_No) := ' DECODE(lg.LEDGER_CATEGORY_CODE, ''PRIMARY'', ''Y'', ''N'') ';
1467
1468 -- Build FROM statement of the dynamic INSERT SQL
1469 l_Line_No := l_Line_No + 1;
1470 l_Insertsql(l_Line_No) := 'FROM GL_LEDGERS lg, ' || l_Fv_Table ||
1471 ' bsv ';
1472
1473 -- Build WHERE statement of the dynamic INSERT SQL
1474 l_Line_No := l_Line_No + 1;
1475 l_Insertsql(l_Line_No) := 'WHERE (lg.ledger_id = :lg_id1 ';
1476 l_Line_No := l_Line_No + 1;
1477 l_Insertsql(l_Line_No) := ' OR lg.ledger_id IN ( ';
1478 l_Line_No := l_Line_No + 1;
1479 l_Insertsql(l_Line_No) := ' SELECT ledger_id FROM GL_ALC_LEDGER_RSHIPS_V ';
1480 l_Line_No := l_Line_No + 1;
1481 l_Insertsql(l_Line_No) := ' WHERE application_id = 101 ';
1482 l_Line_No := l_Line_No + 1;
1483 l_Insertsql(l_Line_No) := ' AND source_ledger_id = :lg_id2)) ';
1484
1485 IF (l_Fv_Type <> 'F')
1486 THEN
1487 l_Line_No := l_Line_No + 1;
1488 l_Insertsql(l_Line_No) := 'AND bsv.flex_value_set_id = lg.bal_seg_value_set_id ';
1489 l_Line_No := l_Line_No + 1;
1490 l_Insertsql(l_Line_No) := 'AND bsv.summary_flag = ''N'' ';
1491 END IF;
1492
1493 -- Open cursor
1494 l_Cursorid := Dbms_Sql.Open_Cursor;
1495 Dbms_Sql.Parse(l_Cursorid,
1496 l_Insertsql,
1497 1,
1498 l_Line_No,
1499 TRUE,
1500 Dbms_Sql.Native);
1501
1502 -- Bind variables
1503 Dbms_Sql.Bind_Variable(l_Cursorid, ':lg_id1', p_Ledger_Id);
1504 Dbms_Sql.Bind_Variable(l_Cursorid, ':lg_id2', p_Ledger_Id);
1505
1506 -- Execute INSERT SQL
1507 l_Return_No := Dbms_Sql.EXECUTE(l_Cursorid);
1508
1509 -- Close cursor
1510 Dbms_Sql.Close_Cursor(l_Cursorid);
1511
1512 ELSE
1513 -- Invalid BSV option code for the passed ledger
1514 RETURN 'S';
1515
1516 END IF; -- IF (l_bsv_option = 'I')
1517 RETURN 'S';
1518 EXCEPTION
1519 WHEN OTHERS THEN
1520 IF (l_Proc_Level >= l_Dbg_Level)
1521 THEN
1522 Fnd_Log.STRING(l_Proc_Level,
1523 l_Module_Prefix || '.' || l_Proc_Name ||
1524 '. Other_Exception ',
1525 SQLCODE || ':' || SQLERRM);
1526 END IF; --(l_proc_level >= l_dbg_level)
1527 END Populate_Ledger_Le_Bsv_Gt;
1528
1529 --==========================================================================
1530 -- FUNCTION NAME:
1531 --
1532 -- Get_Balancing_Segment_Value Public
1533 --
1534 -- DESCRIPTION:
1535 --
1536 -- This function is used to get balancing segment value for the specified key flexfield segments.
1537 --
1538 --
1539 -- PARAMETERS:
1540 -- In: p_coa_id Chart of account ID
1541 -- In: p_concatenated_segments Concatenated segments
1542 --
1543 -- RETURN:
1544 -- Balancing segment value, NULL for abnormal cases.
1545 --
1546 -- DESIGN REFERENCES:
1547 --
1548 --
1549 -- CHANGE HISTORY:
1550 --
1551 -- 29-Aug-2008 Chaoqun Wu Created
1552 --
1553 --===========================================================================
1554 FUNCTION Get_Balancing_Segment_Value(
1555 p_coa_id IN NUMBER,
1556 p_concatenated_segments IN VARCHAR2)
1557 RETURN VARCHAR2 IS
1558 l_delimiter VARCHAR2(1);
1559 l_segments FND_FLEX_EXT.SEGMENTARRAY;
1560 l_total_num NUMBER;
1561 l_num NUMBER;
1562 BEGIN
1563 l_delimiter := FND_FLEX_EXT.GET_DELIMITER('SQLGL', 'GL#', p_coa_id);
1564 l_total_num := FND_FLEX_EXT.BREAKUP_SEGMENTS(p_concatenated_segments,
1565 l_delimiter,
1566 l_segments);
1567 SELECT NUM+1 INTO l_num
1568 FROM FND_SEGMENT_ATTRIBUTE_VALUES FSAV,
1569 (SELECT ROWNUM NUM, APPLICATION_COLUMN_NAME
1570 FROM (SELECT APPLICATION_COLUMN_NAME, SEGMENT_NUM
1571 FROM FND_ID_FLEX_SEGMENTS
1572 WHERE ID_FLEX_NUM = p_coa_id
1573 AND ID_FLEX_CODE = 'GL#'
1574 AND APPLICATION_ID = 101
1575 AND ENABLED_FLAG = 'Y'
1576 ORDER BY SEGMENT_NUM)) FIFS
1577 WHERE FSAV.APPLICATION_ID = 101
1578 AND FSAV.ID_FLEX_NUM = p_coa_id
1579 AND ID_FLEX_CODE = 'GL#'
1580 AND FSAV.ATTRIBUTE_VALUE = 'Y'
1581 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
1582 AND FSAV.APPLICATION_COLUMN_NAME = FIFS.APPLICATION_COLUMN_NAME;
1583
1584 IF l_num <= l_total_num THEN
1585 RETURN l_segments(l_num);
1586 ELSE
1587 RETURN NULL;
1588 END IF;
1589
1590 END Get_Balancing_Segment_Value;
1591
1592
1593 --==========================================================================
1594 -- FUNCTION NAME:
1595 --
1596 -- Get_XML_Encoding Public
1597 --
1598 -- DESCRIPTION:
1599 --
1600 -- This function is used to get the encoding of XML files.
1601 --
1602 -- PARAMETERS:
1603 --
1604 -- RETURN:
1605 -- the encoding of the session
1606 --
1607 -- DESIGN REFERENCES:
1608 --
1609 --
1610 -- CHANGE HISTORY:
1611 --
1612 -- 20-MAR-2010 Jason Liu Created
1613 --
1614 --===========================================================================
1615
1616 FUNCTION Get_XML_Encoding
1617 RETURN VARCHAR2
1618 IS
1619 lv_procedure_name VARCHAR2(40):='Get_XML_Encoding';
1620 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1621 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
1622 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1623 ln_xml_encoding VARCHAR2(100);
1624 BEGIN
1625 --logging for debug
1626 IF (ln_proc_level>=ln_dbg_level)
1627 THEN
1628 FND_LOG.STRING( ln_proc_level
1629 , l_Module_Prefix ||'.' || lv_procedure_name || '.begin'
1630 , 'Enter procedure'
1631 );
1632 END IF; --l_proc_level>=l_dbg_level
1633
1634 SELECT tag
1635 INTO ln_xml_encoding
1636 FROM fnd_lookup_values
1637 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
1638 AND lookup_code =
1639 SUBSTR(USERENV('LANGUAGE'), INSTR(USERENV('LANGUAGE'), '.') + 1)
1640 AND LANGUAGE = 'US';
1641 /*
1642 IF(ln_xml_encoding IS NULL)
1643 THEN
1644 ln_xml_encoding := 'UTF-8';
1645 END IF;
1646 */
1647
1648 --logging the variables
1649 IF (ln_statement_level >= ln_dbg_level)
1650 THEN
1651 FND_LOG.STRING(ln_statement_level,
1652 lv_procedure_name,
1653 'The encoding is:' || ln_xml_encoding);
1654 END IF; --(ln_statement_level >= ln_dbg_level)
1655
1656 --logging for debug
1657 IF (ln_proc_level>=ln_dbg_level)
1658 THEN
1659 FND_LOG.STRING( ln_proc_level
1660 , l_Module_Prefix ||'.' || lv_procedure_name || '.end'
1661 , 'Exit procedure'
1662 );
1663 END IF; -- (ln_proc_level>=ln_dbg_level)
1664
1665 RETURN ln_xml_encoding;
1666
1667 END Get_XML_Encoding;
1668
1669 --==========================================================================
1670 -- FUNCTION NAME:
1671 --
1672 -- Get_XML_Tag Public
1673 --
1674 -- DESCRIPTION:
1675 --
1676 -- This function is used to get the meaning for the specified lookup code
1677 -- of the lookup type 'JA_CN_XML_TAGS', 'JA_CN_FA_XML_TAGS',
1678 -- 'JA_CN_APAR_XML_TAGS', and then generate the XML tags according to the
1679 -- tag type.
1680 --
1681 -- PARAMETERS:
1682 -- In: pv_lookup_code lookup code
1683 -- pv_tag_type tag type, the values: S,E,B,N
1684 -- pv_module_name module name, the values: GLSI,
1685 -- FA,APAR,HR
1686 --
1687 -- RETURN:
1688 -- XML tags of the specified lookup code according to the tag type
1689 --
1690 -- DESIGN REFERENCES:
1691 --
1692 --
1693 -- CHANGE HISTORY:
1694 --
1695 -- 20-JAN-2010 Jason Liu Created
1696 --
1697 --===========================================================================
1698
1699 FUNCTION Get_XML_Tag
1700 (pv_lookup_code VARCHAR2
1701 ,pv_tag_type VARCHAR2 DEFAULT 'N'
1702 ,pv_module_name VARCHAR2 DEFAULT 'GLSI'
1703 )
1704 RETURN VARCHAR2
1705 IS
1706 lv_procedure_name VARCHAR2(40):='Get_XML_Tag';
1707 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1708 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
1709 lv_xml_tag VARCHAR2(200);
1710 BEGIN
1711 --logging for debug
1712 IF (ln_proc_level>=ln_dbg_level)
1713 THEN
1714 FND_LOG.STRING( ln_proc_level
1715 , l_Module_Prefix ||'.' || lv_procedure_name || '.begin'
1716 , 'Enter procedure'
1717 );
1718 END IF; --(ln_proc_level>=ln_dbg_level)
1719
1720 --get the meaning of the specified lookup code
1721 IF(pv_module_name = GV_MODULE_GLSI)
1722 THEN
1723 SELECT meaning
1724 INTO lv_xml_tag
1725 FROM fnd_lookups
1726 WHERE lookup_type = 'JA_CN_XML_TAGS'
1727 AND lookup_code = pv_lookup_code;
1728 ELSIF(pv_module_name = GV_MODULE_FA)
1729 THEN
1730 SELECT meaning
1731 INTO lv_xml_tag
1732 FROM fnd_lookups
1733 WHERE lookup_type = 'JA_CN_FA_XML_TAGS'
1734 AND lookup_code = pv_lookup_code;
1735 ELSIF(pv_module_name = GV_MODULE_APAR)
1736 THEN
1737 SELECT meaning
1738 INTO lv_xml_tag
1739 FROM fnd_lookups
1740 WHERE lookup_type = 'JA_CN_APAR_XML_TAGS' --for testing APAR Chinese XML tags
1741 AND lookup_code = pv_lookup_code;
1742 END IF; --(pv_module_name = 'GLSI')
1743
1744 --replace the blank with the underline
1745 lv_xml_tag := REPLACE(lv_xml_tag, ' ', '_');
1746 IF (pv_tag_type = GV_TAG_TYPE_START)
1747 THEN
1748 lv_xml_tag := '<' || lv_xml_tag || '>';
1749 ELSIF(pv_tag_type = GV_TAG_TYPE_END)
1750 THEN
1751 lv_xml_tag := '</' || lv_xml_tag || '>';
1752 ELSIF(pv_tag_type = GV_TAG_TYPE_BLANK)
1753 THEN
1754 lv_xml_tag := '<' || lv_xml_tag || '/>';
1755 ELSE
1756 lv_xml_tag := lv_xml_tag;
1757 END IF;
1758
1759 RETURN lv_xml_tag;
1760
1761 --logging for debug
1762 IF (ln_proc_level>=ln_dbg_level)
1763 THEN
1764 FND_LOG.STRING( ln_proc_level
1765 , l_Module_Prefix ||'.' || lv_procedure_name || '.end'
1766 , 'Exit procedure'
1767 );
1768 END IF; -- (ln_proc_level>=ln_dbg_level)
1769 END Get_XML_Tag;
1770
1771
1772 --==========================================================================
1773 -- PROCEDURE NAME:
1774 --
1775 -- Add_Sub_Root_Node Public
1776 --
1777 -- DESCRIPTION:
1778 --
1779 -- This procedure is used to add the sub root node of each report.
1780 --
1781 -- PARAMETERS:
1782 -- In: pv_sub_root_tag_name sub root tag name
1783 -- pv_tag_type tag type, the values: S,E,B
1784 -- pv_module_name module name, the values: GLSI,
1785 -- FA,APAR,HR
1786 --
1787 --
1788 -- DESIGN REFERENCES:
1789 --
1790 --
1791 -- CHANGE HISTORY:
1792 --
1793 -- 20-JAN-2010 Jason Liu Created
1794 --
1795 --===========================================================================
1796
1797 PROCEDURE Add_Sub_Root_Node
1798 (pv_sub_root_tag_name VARCHAR2
1799 ,pv_tag_type VARCHAR2
1800 ,pv_module_name VARCHAR2 DEFAULT 'GLSI'
1801 )
1802 IS
1803 lv_procedure_name VARCHAR2(40):='Add_Sub_Root_Node';
1804 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1805 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
1806
1807
1808 BEGIN
1809 --logging for debug
1810 IF (ln_proc_level>=ln_dbg_level)
1811 THEN
1812 FND_LOG.STRING( ln_proc_level
1813 , l_Module_Prefix ||'.' || lv_procedure_name || '.begin'
1814 , 'Enter procedure'
1815 );
1816 END IF; --l_proc_level>=l_dbg_level
1817
1818 FND_FILE.put_line(FND_FILE.output, Get_XML_Tag(pv_sub_root_tag_name, pv_tag_type, pv_module_name));
1819
1820
1821
1822 --logging for debug
1823 IF (ln_proc_level>=ln_dbg_level)
1824 THEN
1825 FND_LOG.STRING( ln_proc_level
1826 , l_Module_Prefix ||'.' || lv_procedure_name || '.end'
1827 , 'Exit procedure'
1828 );
1829 END IF; -- (ln_proc_level>=ln_dbg_level)
1830 EXCEPTION
1831 WHEN OTHERS THEN
1832 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1833 THEN
1834 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1835 , l_Module_Prefix ||'.' || lv_procedure_name || '.Other_Exception '
1836 , Sqlcode||Sqlerrm);
1837 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1838 END Add_Sub_Root_Node;
1839
1840 --==========================================================================
1841 -- PROCEDURE NAME:
1842 --
1843 -- Add_Child_Node Public
1844 --
1845 -- DESCRIPTION:
1846 --
1847 -- This procedure is used to add the child node of each report.
1848 --
1849 -- PARAMETERS:
1850 -- In: pv_child_tag_name child tag name
1851 -- pv_text_node_value the value of the node
1852 -- pv_data_type data type, the values: VARCHAR2,
1853 -- NUMBER
1854 -- pv_required required or not, the values: Y, N
1855 -- pv_module_name module name, the values: GLSI,
1856 -- FA,APAR,HR
1857 --
1858 -- DESIGN REFERENCES:
1859 --
1860 --
1861 -- CHANGE HISTORY:
1862 --
1863 -- 20-JAN-2010 Jason Liu Created
1864 --
1865 --===========================================================================
1866
1867 PROCEDURE Add_Child_Node
1868 (pv_child_tag_name VARCHAR2
1869 ,pv_text_node_value VARCHAR2
1870 ,pv_data_type VARCHAR2 DEFAULT 'VARCHAR2'
1871 ,pv_required VARCHAR2 DEFAULT 'Y'
1872 ,pv_module_name VARCHAR2 DEFAULT 'GLSI'
1873 )
1874 IS
1875 lv_procedure_name VARCHAR2(40):='Add_Child_Node';
1876 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1877 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
1878 BEGIN
1879 --logging for debug
1880 IF (ln_proc_level>=ln_dbg_level)
1881 THEN
1882 FND_LOG.STRING( ln_proc_level
1883 , l_Module_Prefix ||'.' || lv_procedure_name || '.begin'
1884 , 'Enter procedure'
1885 );
1886 END IF; --l_proc_level>=l_dbg_level
1887
1888 IF(pv_data_type = GV_TYPE_VARCHAR2)
1889 THEN
1890 IF (pv_text_node_value IS NOT NULL)
1891 THEN
1892 FND_FILE.put_line(FND_FILE.output, Get_XML_Tag(pv_child_tag_name,'S',pv_module_name) || Replace_Special_Characters(pv_text_node_value) || Get_XML_Tag(pv_child_tag_name,'E',pv_module_name));
1893 ELSE
1894 FND_FILE.put_line(FND_FILE.output, Get_XML_Tag(pv_child_tag_name,'B',pv_module_name));
1895 END IF;
1896 ELSIF (pv_data_type = GV_TYPE_NUMBER)
1897 THEN
1898 IF (pv_text_node_value IS NOT NULL)
1899 THEN
1900 FND_FILE.put_line(FND_FILE.output, Get_XML_Tag(pv_child_tag_name,'S',pv_module_name) || pv_text_node_value || Get_XML_Tag(pv_child_tag_name,'E',pv_module_name));
1901 ELSIF(pv_required = GV_REQUIRED_YES)
1902 THEN
1903 --FND_FILE.put_line(FND_FILE.output, Get_XML_Tag(pv_child_tag_name,'B'));
1904 FND_FILE.put_line(FND_FILE.output, Get_XML_Tag(pv_child_tag_name,'S',pv_module_name) || '0' || Get_XML_Tag(pv_child_tag_name,'E',pv_module_name));
1905 END IF;
1906 END IF;
1907 --logging for debug
1908 IF (ln_proc_level>=ln_dbg_level)
1909 THEN
1910 FND_LOG.STRING( ln_proc_level
1911 , l_Module_Prefix ||'.' || lv_procedure_name || '.end'
1912 , 'Exit procedure'
1913 );
1914 END IF; -- (ln_proc_level>=ln_dbg_level)
1915
1916 EXCEPTION
1917 WHEN OTHERS THEN
1918 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1919 THEN
1920 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1921 , l_Module_Prefix ||'.' || lv_procedure_name || '.Other_Exception '
1922 , Sqlcode||Sqlerrm);
1923 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1924 END Add_Child_Node;
1925
1926 --==========================================================================
1927 -- PROCEDURE NAME:
1928 --
1929 -- Add_Fixed_Child_Node Public
1930 --
1931 -- DESCRIPTION:
1932 --
1933 -- This procedure is used to add the child node which have fixed length.
1934 --
1935 -- PARAMETERS:
1936 -- In: pv_child_tag_name child tag name
1937 -- pv_text_node_value the value of the node
1938 -- pn_fixed_length the length
1939 -- pv_required required or not, the values: Y, N
1940 -- pv_module_name module name, the values: GLSI,
1941 -- FA,APAR,HR
1942 --
1943 -- DESIGN REFERENCES:
1944 --
1945 --
1946 -- CHANGE HISTORY:
1947 --
1948 -- 20-JAN-2010 Jason Liu Created
1949 --
1950 --===========================================================================
1951 PROCEDURE Add_Fixed_Child_Node
1952 (pv_child_tag_name VARCHAR2
1953 ,pv_text_node_value VARCHAR2
1954 ,pn_fixed_length NUMBER
1955 ,pv_required VARCHAR2 DEFAULT 'Y'
1956 ,pv_module_name VARCHAR2 DEFAULT 'GLSI'
1957 )
1958 IS
1959 lv_procedure_name VARCHAR2(40):='Add_Fixed_Child_Node';
1960 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1961 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
1962 lv_text_node_value VARCHAR2(10);
1963 LENGTH_IS_NOT_MATCHED EXCEPTION;
1964 BEGIN
1965 --logging for debug
1966 IF (ln_proc_level>=ln_dbg_level)
1967 THEN
1968 FND_LOG.STRING( ln_proc_level
1969 , l_Module_Prefix ||'.' || lv_procedure_name || '.begin'
1970 , 'Enter procedure'
1971 );
1972 END IF; --l_proc_level>=l_dbg_level
1973
1974 IF(pv_text_node_value IS NOT NULL)
1975 THEN
1976 IF(length(pv_text_node_value) = pn_fixed_length)
1977 THEN
1978 FND_FILE.put_line(FND_FILE.output, Get_XML_Tag(pv_child_tag_name, GV_TAG_TYPE_START, pv_module_name) || pv_text_node_value || Get_XML_Tag(pv_child_tag_name, GV_TAG_TYPE_END, pv_module_name));
1979 ELSE
1980 RAISE LENGTH_IS_NOT_MATCHED;
1981 END IF;
1982 ELSE
1983 IF(pv_required = GV_REQUIRED_YES)
1984 THEN
1985 SELECT rpad(NVL(NULL, '0'), pn_fixed_length, '0')
1986 INTO lv_text_node_value
1987 FROM dual;
1988 FND_FILE.put_line(FND_FILE.output, Get_XML_Tag(pv_child_tag_name, GV_TAG_TYPE_START, pv_module_name) || lv_text_node_value || Get_XML_Tag(pv_child_tag_name, GV_TAG_TYPE_END, pv_module_name));
1989 --ELSE
1990 --FND_FILE.put_line(FND_FILE.output, Get_XML_Tag(pv_child_tag_name, GV_TAG_TYPE_BLANK));
1991 END IF;
1992
1993 END IF;
1994
1995 --logging for debug
1996 IF (ln_proc_level>=ln_dbg_level)
1997 THEN
1998 FND_LOG.STRING( ln_proc_level
1999 , l_Module_Prefix ||'.' || lv_procedure_name || '.end'
2000 , 'Exit procedure'
2001 );
2002 END IF; -- (ln_proc_level>=ln_dbg_level)
2003
2004 EXCEPTION
2005 WHEN LENGTH_IS_NOT_MATCHED THEN
2006 --logging for debug
2007 IF (ln_proc_level >= ln_dbg_level)
2008 THEN
2009 FND_LOG.STRING(ln_proc_level,
2010 l_Module_Prefix || '.' || lv_procedure_name ||
2011 '.exception',
2012 'The length of the element ' || pv_child_tag_name || ' should be ' || pn_fixed_length);
2013 END IF; --l_proc_level>=l_dbg_level
2014 FND_FILE.put_line(FND_FILE.log, 'The length of the element ' || pv_child_tag_name || ' should be ' || pn_fixed_length);
2015 RAISE;
2016 WHEN OTHERS THEN
2017 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2018 THEN
2019 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
2020 , l_Module_Prefix ||'.' || lv_procedure_name || '.Other_Exception '
2021 , Sqlcode||Sqlerrm);
2022 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2023 END Add_Fixed_Child_Node;
2024
2025 --==========================================================================
2026 -- FUNCTION NAME:
2027 --
2028 -- Replace_Special_Characters Public
2029 --
2030 -- DESCRIPTION:
2031 --
2032 -- This function is used to replace the special characters.
2033 --
2034 -- PARAMETERS:
2035 -- In: pv_source_string the values which may include the
2036 -- special characters
2037 --
2038 -- DESIGN REFERENCES:
2039 --
2040 --
2041 -- CHANGE HISTORY:
2042 --
2043 -- 20-JAN-2010 Jason Liu Created
2044 --
2045 --===========================================================================
2046 FUNCTION Replace_Special_Characters(pv_source_string VARCHAR2)
2047 RETURN VARCHAR2
2048 IS
2049 lv_replaced_string VARCHAR2(2000);
2050 BEGIN
2051 --lv_replaced_string := htf.escape_sc(pv_source_string);
2052 lv_replaced_string := dbms_xmlgen.convert(pv_source_string);
2053 RETURN lv_replaced_string;
2054
2055 END Replace_Special_characters;
2056
2057 --==========================================================================
2058 -- FUNCTION NAME:
2059 --
2060 -- Process_Subsidiary_Item Public
2061 --
2062 -- DESCRIPTION:
2063 --
2064 -- This function is used to wrap the Subsidiary Items to meeting the
2065 -- CNAOV2 standard.
2066 --
2067 -- PARAMETERS:
2068 -- In: pn_subsidiary_item_no Subsidiary Item No
2069 -- pv_subsidiary_item Subsidiary Item value
2070 --
2071 -- DESIGN REFERENCES:
2072 --
2073 --
2074 -- CHANGE HISTORY:
2075 --
2076 -- 15-Mar-2010 Wei Huang Created
2077 --
2078 --===========================================================================
2079 FUNCTION Process_Subsidiary_Item
2080 ( pn_subsidiary_item_no NUMBER
2081 , pv_subsidiary_item VARCHAR2 )
2082 RETURN VARCHAR2
2083 IS
2084 lv_subsidiary_item VARCHAR2(25);
2085 BEGIN
2086 IF pv_subsidiary_item IS NOT NULL THEN
2087 lv_subsidiary_item := pn_subsidiary_item_no || '(' || pv_subsidiary_item || ')';
2088 ELSE
2089 lv_subsidiary_item := NULL;
2090 END IF;
2091
2092 RETURN lv_subsidiary_item;
2093
2094 END Process_Subsidiary_Item;
2095
2096 --==========================================================================
2097 -- FUNCTION NAME:
2098 --
2099 -- Generate_File_Name Public
2100 --
2101 -- DESCRIPTION:
2102 --
2103 -- This function is used to get generate the filename for the output CNAOv2 XML file.
2104 --
2105 --
2106 -- PARAMETERS:
2107 -- In: pv_module_name VARCHAR2 module name, the values: GLSI,
2108 -- FA,APAR,HR
2109 -- In: pv_ledger_id NUMBER identifier of legal entity
2110 -- In: pv_accounting_year VARCHAR2 accounting year
2111 -- In: pv_start_month VARCHAR2 start month
2112 -- In: pv_end_month VARCHAR2 end month
2113 --
2114 -- RETURN:
2115 -- Output file name value, NULL for abnormal cases.
2116 --
2117 -- DESIGN REFERENCES:
2118 --
2119 --
2120 -- CHANGE HISTORY:
2121 --
2122 -- 09-Mar-2010 Qingyi Wang Created
2123 --
2124 --===========================================================================
2125 FUNCTION Generate_File_name
2126 (pv_module_name IN VARCHAR2
2127 ,pv_ledger_id IN NUMBER
2128 ,pv_accounting_year IN VARCHAR2
2129 ,pv_from_period IN VARCHAR2
2130 ,pv_to_period IN VARCHAR2)
2131 RETURN VARCHAR2
2132 IS
2133 lv_kookup_meaning Fnd_Lookup_Values.Meaning%TYPE := NULL;
2134 lv_filename VARCHAR2(200);
2135 ln_accounting_year_len NUMBER;
2136 ln_from_month NUMBER;
2137 ln_to_month NUMBER;
2138 lv_procedure_name VARCHAR2(30) := 'Generate_File_Name';
2139 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2140 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
2141
2142 -- this cursor is to get looup_meaning under some lookup_code
2143 CURSOR lookup_cur IS
2144 SELECT
2145 flv.meaning
2146 FROM
2147 fnd_lookup_values flv
2148 WHERE flv.LANGUAGE = Userenv('LANG')
2149 AND flv.lookup_type = 'JA_CN_OUTPUTFILE_PREFIX'
2150 AND flv.view_application_Id = 0
2151 AND flv.security_group_id = 0
2152 AND flv.lookup_code = pv_module_name;
2153
2154 -- this cursor is to get from month from gl_period_statuses
2155 CURSOR from_month_cur IS
2156 SELECT
2157 gps.period_num
2158 FROM
2159 gl_period_statuses gps
2160 WHERE gps.ledger_id = pv_ledger_id
2161 AND gps.period_name = pv_from_period
2162 AND gps.application_id = 200;
2163
2164 -- this cursor is to get to month from gl_period_statuses
2165 CURSOR to_month_cur IS
2166 SELECT
2167 gps.period_num
2168 FROM
2169 gl_period_statuses gps
2170 WHERE gps.ledger_id = pv_ledger_id
2171 AND gps.period_name = pv_to_period
2172 AND gps.application_id = 200;
2173
2174 BEGIN
2175 --logging for debug
2176 IF (ln_proc_level>=ln_dbg_level)
2177 THEN
2178 FND_LOG.STRING( ln_proc_level
2179 , l_Module_Prefix ||'.' || lv_procedure_name || '.begin'
2180 , 'Enter procedure'
2181 );
2182 END IF;-- (ln_proc_level>=ln_dbg_level)
2183
2184 --Retrieve the paramter length
2185 ln_accounting_year_len := Length(pv_accounting_year);
2186 IF ln_accounting_year_len <> 4
2187 THEN
2188 RETURN NULL;
2189 END IF; -- (ln_accounting_year_len <> 4)
2190
2191 IF (pv_from_period IS NOT NULL) AND pv_to_period IS NULL
2192 THEN
2193 RETURN NULL;
2194 END IF; -- ((pv_from_period IS NOT NULL) AND pv_to_period IS NULL)
2195
2196 IF (pv_from_period IS NULL) AND pv_to_period IS NOT NULL
2197 THEN
2198 RETURN NULL;
2199 END IF; --((pv_from_period IS NULL) AND pv_to_period IS NOT NULL)
2200
2201 --Retrive the lookup meaning.
2202 OPEN lookup_cur;
2203 FETCH lookup_cur
2204 INTO lv_kookup_meaning;
2205 IF lookup_cur%NOTFOUND
2206 THEN
2207 lv_kookup_meaning := NULL;
2208 END IF; --(lookup_cur%NOTFOUND)
2209 CLOSE lookup_cur;
2210
2211 IF lv_kookup_meaning IS NULL
2212 THEN
2213 RETURN NULL;
2214 END IF; --(lv_kookup_meaning IS NULL)
2215
2216 lv_filename := lv_kookup_meaning || pv_accounting_year;
2217
2218 --replace the blank with the underline
2219 lv_filename := REPLACE(lv_filename, ' ', '_');
2220
2221 IF(pv_from_period IS NOT NULL) AND (pv_to_period IS NOT NULL)
2222 THEN
2223 --Retrive the from month.
2224 OPEN from_month_cur;
2225 FETCH from_month_cur
2226 INTO ln_from_month;
2227 IF from_month_cur%NOTFOUND
2228 THEN
2229 ln_from_month := NULL;
2230 END IF; --(from_month_cur%NOTFOUND)
2231 CLOSE from_month_cur;
2232
2233 IF ln_from_month IS NULL
2234 THEN
2235 RETURN NULL;
2236 END IF; --(ln_from_month IS NULL)
2237
2238 --Retrive the to month.
2239 OPEN to_month_cur;
2240 FETCH to_month_cur
2241 INTO ln_to_month;
2242 IF to_month_cur%NOTFOUND
2243 THEN
2244 ln_to_month := NULL;
2245 END IF; --(to_month_cur%NOTFOUND)
2246 CLOSE to_month_cur;
2247
2248 IF ln_to_month IS NULL
2249 THEN
2250 RETURN NULL;
2251 END IF; -- (ln_to_month IS NULL)
2252
2253 lv_filename := lv_filename || to_char(ln_from_month, 'fm00');
2254 lv_filename := lv_filename || to_char(ln_to_month, 'fm00');
2255 END IF; --((pv_from_period IS NOT NULL) AND (pv_to_period IS NOT NULL))
2256
2257 lv_filename := lv_filename || '.xml';
2258 --logging for debug
2259 IF (ln_proc_level>=ln_dbg_level)
2260 THEN
2261 FND_LOG.STRING( ln_proc_level
2262 , l_Module_Prefix ||'.' || lv_procedure_name || '.end'
2263 , 'Exit procedure');
2264 END IF; -- (ln_proc_level>=ln_dbg_level)
2265
2266 RETURN lv_filename;
2267
2268 EXCEPTION
2269 WHEN OTHERS THEN
2270 IF (ln_proc_level>=ln_dbg_level)
2271 THEN
2272 Fnd_Log.STRING(ln_proc_level,
2273 l_Module_Prefix || '.' || lv_procedure_name ||
2274 '. Other_Exception ',
2275 SQLCODE || ':' || SQLERRM);
2276 END IF;-- (ln_proc_level>=ln_dbg_level)
2277 RETURN NULL;
2278
2279 END Generate_File_name;
2280
2281
2282 --==========================================================================
2283 -- FUNCTION NAME:
2284 --
2285 -- Check_System_Option Public
2286 --
2287 -- DESCRIPTION:
2288 --
2289 -- This function is to check the system option form, if any of the fields
2290 -- has no value, the error message will be displayed.
2291 --
2292 -- PARAMETERS:
2293 -- In: pn_legal_entity_id legal_entity ID
2294 -- DESIGN REFERENCES:
2295 -- CNAO_V2_GL_TD.doc
2296 --
2297 -- CHANGE HISTORY:
2298 --
2299 -- 20-JAN-2010 Chongwu Li created
2300 FUNCTION Check_System_Option(pn_legal_entity_id NUMBER)
2301 RETURN BOOLEAN
2302 IS
2303 lv_False_Flag VARCHAR2(1) := 'N';
2304 lv_Error_Msg VARCHAR2(2000);
2305 ln_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
2306 ln_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
2307 lv_Proc_Name VARCHAR2(100) := 'Check_System_Option';
2308 ln_row_count NUMBER := 0;
2309
2310 CURSOR system_option_cur(ln_legal_entity_id NUMBER) IS
2311 SELECT jcspa.book_num,
2312 jcspa.book_name,
2313 jcspa.company_name,
2314 jcspa.organization_id,
2315 jcspa.ent_quality,
2316 jcspa.ent_industry,
2317 jcspa.cnao_stand_ver
2318 FROM ja_cn_system_parameters_all jcspa
2319 WHERE jcspa.legal_entity_id = ln_legal_entity_id;
2320 BEGIN
2321
2322 --log for debug
2323 IF (ln_Proc_Level >= ln_Dbg_Level)
2324 THEN
2325 Fnd_Log.STRING(ln_Proc_Level,
2326 l_Module_Prefix || '.' || lv_Proc_Name || '.begin',
2327 'Enter function');
2328 END IF; --(l_proc_level >= l_dbg_level)
2329
2330 FOR v_row IN system_option_cur(pn_legal_entity_id)
2331 LOOP
2332 ln_row_count := ln_row_count +1;
2333 IF (v_row.book_num IS NULL OR v_row.book_name IS NULL OR
2334 v_row.company_name IS NULL OR v_row.organization_id IS NULL OR
2335 v_row.ent_quality IS NULL OR v_row.ent_industry IS NULL OR
2336 v_row.cnao_stand_ver IS NULL)
2337 THEN
2338 --Raise error message for caller
2339 lv_False_Flag := 'Y';
2340 Fnd_Message.Set_Name(Application => 'JA',
2341 NAME => 'JA_CN_MISSING_BOOK_INFO');
2342 lv_Error_Msg := Fnd_Message.Get;
2343
2344 --Output error message
2345 Fnd_File.Put_Line(Fnd_File.Output, lv_Error_Msg);
2346 END IF; --v_row.book_num IS NULL OR v_row.book_name IS NULL OR
2347 --v_row.company_name IS NULL OR v_row.organization_id IS NULL OR
2348 --v_row.ent_quality IS NULL OR v_row.ent_industry IS NULL OR
2349 --v_row.cnao_stand_ver IS NULL
2350 END LOOP;
2351
2352
2353 IF (ln_row_count = 0)
2354 THEN
2355 lv_False_Flag := 'Y';
2356 Fnd_Message.Set_Name(Application => 'JA',
2357 NAME => 'JA_CN_MISSING_BOOK_INFO');
2358 lv_Error_Msg := Fnd_Message.Get;
2359 --Output error message
2360 Fnd_File.Put_Line(Fnd_File.Output, lv_Error_Msg);
2361 END IF; --ln_row_count = 0
2362
2363
2364 IF lv_False_Flag = 'N'
2365 THEN
2366 --log for debug
2367 IF (ln_Proc_Level >= ln_Dbg_Level) THEN
2368 Fnd_Log.STRING(ln_Proc_Level,
2369 l_Module_Prefix || '.' || lv_Proc_Name || '.end',
2370 'Exit function');
2371 END IF; --( ln_proc_level >= ln_dbg_level )
2372 RETURN TRUE;
2373 ELSE
2374 --log for debug
2375 IF (ln_Proc_Level >= ln_Dbg_Level)
2376 THEN
2377 Fnd_Log.STRING(ln_Proc_Level,
2378 l_Module_Prefix || '.' || lv_Proc_Name || '.end',
2379 'Exit function');
2380 END IF; --( ln_proc_level >= ln_dbg_level )
2381 RETURN FALSE;
2382 END IF; --lv_false_flage='N'
2383
2384 EXCEPTION
2385
2386 WHEN OTHERS
2387 THEN
2388 IF (ln_Proc_Level >= ln_Dbg_Level)
2389 THEN
2390 Fnd_Log.STRING(ln_Proc_Level,
2391 l_Module_Prefix || '.' || lv_Proc_Name ||
2392 '. Other_Exception ',
2393 SQLCODE || ':' || SQLERRM);
2394 END IF; --(ln_proc_level >= ln_dbg_level)
2395
2396 END Check_System_Option;
2397
2398 --==========================================================================
2399 -- PROCEDURE NAME:
2400 --
2401 -- Print_Concurrent_Steps_For_Log Public
2402 --
2403 -- DESCRIPTION:
2404 --
2405 -- This procedure is used to print the logs for the top concurrent.
2406 --
2407 -- PARAMETERS:
2408 -- In: pv_step_number the step number
2409 -- pv_report_name the root lookup code of the report
2410 -- pv_module_name module name, the values: GLSI,
2411 -- FA,APAR,HR
2412 --
2413 -- DESIGN REFERENCES:
2414 --
2415 --
2416 -- CHANGE HISTORY:
2417 --
2418 -- 20-MAR-2010 Jason Liu Created
2419 --
2420 --===========================================================================
2421 PROCEDURE Print_Concurrent_Steps_For_Log
2422 (pv_step_number VARCHAR2
2423 ,pv_report_name VARCHAR2
2424 ,pv_module_name VARCHAR2 DEFAULT 'GLSI'
2425 )
2426 IS
2427 lv_procedure_name VARCHAR2(40):='Print_Concurrent_Steps_For_Log';
2428 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2429 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
2430 BEGIN
2431 --logging for debug
2432 IF (ln_proc_level>=ln_dbg_level)
2433 THEN
2434 FND_LOG.STRING( ln_proc_level
2435 , l_Module_Prefix ||'.' || lv_procedure_name || '.begin'
2436 , 'Enter procedure'
2437 );
2438 END IF; --(ln_proc_level>=ln_dbg_level)
2439
2440 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
2441 ,NAME => 'JA_CN_LOG_STEP'
2442 );
2443 FND_MESSAGE.SET_TOKEN('STEP_NUMBER', pv_step_number);
2444 FND_MESSAGE.SET_TOKEN('REPORT_NAME', Get_XML_Tag(pv_report_name,'O',pv_module_name));
2445 FND_FILE.put_line(FND_FILE.log, FND_MESSAGE.Get);
2446
2447 --logging for debug
2448 IF (ln_proc_level>=ln_dbg_level)
2449 THEN
2450 FND_LOG.STRING( ln_proc_level
2451 , l_Module_Prefix ||'.' || lv_procedure_name || '.end'
2452 , 'Exit procedure'
2453 );
2454 END IF; -- (ln_proc_level>=ln_dbg_level)
2455 END Print_Concurrent_Steps_For_Log;
2456
2457 --==========================================================================
2458 -- PROCEDURE NAME:
2459 --
2460 -- Print_No_Data_Found_For_Log Public
2461 --
2462 -- DESCRIPTION:
2463 --
2464 -- This procedure is used to print the logs if the report have no records.
2465 --
2466 -- PARAMETERS:
2467 -- In: pv_report_name the root lookup code of the report
2468 -- pv_module_name module name, the values: GLSI,
2469 -- FA,APAR,HR
2470 --
2471 -- DESIGN REFERENCES:
2472 --
2473 --
2474 -- CHANGE HISTORY:
2475 --
2476 -- 20-MAR-2010 Jason Liu Created
2477 --
2478 --===========================================================================
2479 PROCEDURE Print_No_Data_Found_For_Log
2480 (pv_report_name VARCHAR2
2481 ,pv_module_name VARCHAR2 DEFAULT 'GLSI'
2482 )
2483 IS
2484 lv_procedure_name VARCHAR2(40):='Print_No_Data_Found_For_Log';
2485 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2486 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
2487 BEGIN
2488 --logging for debug
2489 IF (ln_proc_level>=ln_dbg_level)
2490 THEN
2491 FND_LOG.STRING( ln_proc_level
2492 , l_Module_Prefix ||'.' || lv_procedure_name || '.begin'
2493 , 'Enter procedure'
2494 );
2495 END IF; --(ln_proc_level>=ln_dbg_level)
2496
2497 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
2498 ,NAME => 'JA_CN_NO_DATA_FOR_REPORT'
2499 );
2500 FND_MESSAGE.SET_TOKEN('REPORT_NAME', Get_XML_Tag(pv_report_name,'O',pv_module_name));
2501 FND_FILE.put_line(FND_FILE.log, FND_MESSAGE.Get);
2502
2503 --logging for debug
2504 IF (ln_proc_level>=ln_dbg_level)
2505 THEN
2506 FND_LOG.STRING( ln_proc_level
2507 , l_Module_Prefix ||'.' || lv_procedure_name || '.end'
2508 , 'Exit procedure'
2509 );
2510 END IF; -- (ln_proc_level>=ln_dbg_level)
2511 END Print_No_Data_Found_For_Log;
2512
2513
2514
2515 --==========================================================================
2516 -- FUNCTION NAME:
2517 --
2518 -- Get_Rounding_Value Public
2519 --
2520 -- DESCRIPTION:
2521 --
2522 -- This function is used to get rounding value with the system rounding rule.
2523 --
2524 --
2525 -- PARAMETERS:
2526 -- In: pn_module_name NUMBER original value
2527 --
2528 -- RETURN:
2529 -- The rounding value.
2530 --
2531 -- DESIGN REFERENCES:
2532 --
2533 --
2534 -- CHANGE HISTORY:
2535 --
2536 -- 11-May-2010 Qingyi Wang Created
2537 --
2538 --===========================================================================
2539 FUNCTION Get_Rounding_Value
2540 (pn_original_value IN NUMBER
2541 ,pn_legal_entity_id IN NUMBER
2542 )
2543 RETURN VARCHAR2
2544 IS
2545 lv_rounding_rule VARCHAR2(30);
2546 ln_rounding_value NUMBER(20,2) := 0;
2547 lv_procedure_name VARCHAR2(30) := 'Generate_File_Name';
2548 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2549 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
2550
2551 BEGIN
2552
2553 --get the system rounding rule
2554 SELECT rounding_rule
2555 INTO lv_rounding_rule
2556 FROM ja_cn_system_parameters_all
2557 WHERE legal_entity_id = pn_legal_entity_id;
2558
2559 IF (lv_rounding_rule = 'N')
2560 THEN
2561 ln_rounding_value := round(pn_original_value*100)/100;
2562 ELSIF (lv_rounding_rule = 'U')
2563 THEN
2564 ln_rounding_value := ceil(pn_original_value*100)/100;
2565 ELSIF (lv_rounding_rule = 'D')
2566 THEN
2567 ln_rounding_value := floor(pn_original_value*100)/100;
2568 END IF;--(lv_rounding_rule = 'N')
2569
2570 RETURN ln_rounding_value;
2571
2572 EXCEPTION
2573 WHEN OTHERS THEN
2574 IF (ln_proc_level>=ln_dbg_level)
2575 THEN
2576 Fnd_Log.STRING(ln_proc_level,
2577 l_Module_Prefix || '.' || lv_procedure_name ||
2578 '. Other_Exception ',
2579 SQLCODE || ':' || SQLERRM);
2580 END IF;-- (ln_proc_level>=ln_dbg_level)
2581 RETURN round(pn_original_value*100)/100;
2582
2583 END Get_Rounding_Value;
2584
2585 --==========================================================================
2586 -- PROCEDURE NAME:
2587 --
2588 -- Get_Attribute_Column Public
2589 --
2590 -- DESCRIPTION:
2591 --
2592 -- To get segment and attribute columns defined in flexfiled assignment form
2593 --
2594 -- PARAMETERS:
2595 -- In: pv_flexfield_code IN VARCHAR2 Flex code in flexfield assignment
2596 -- pn_application_id IN NUMBER Application Id
2597 -- pn_coa_id IN NUMBER Chart of account Id
2598 --
2599 -- DESIGN REFERENCES:
2600 -- TDD_1213_FIN_GL_P_CNAOV2_GL.doc
2601 --
2602 -- CHANGE HISTORY:
2603 -- 11-Aug-2010 Chaoqun Wu created
2604 --==========================================================================
2605 FUNCTION Get_Attribute_Column
2606 (pv_flexfield_code IN VARCHAR2
2607 ,pn_application_id IN NUMBER
2608 ,pn_coa_id IN NUMBER)
2609 RETURN VARCHAR2
2610 IS
2611 lv_attribute_column VARCHAR2(30) := NULL;
2612 lv_procedure_name VARCHAR2(40) := 'Get_Attribute_Column';
2613 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2614 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2615
2616
2617 CURSOR attribute_column_cur
2618 IS
2619 SELECT Attribute_Column
2620 FROM Ja_CN_Dff_Assignments
2621 WHERE Application_Id = pn_application_id --parameter: pn_application_id
2622 AND Chart_of_Accounts_Id = pn_coa_id --parameter: pn_coa_id
2623 AND DFF_TITLE_CODE = pv_flexfield_code; --parameter: pv_flexfield_code
2624
2625 BEGIN
2626 --logging for debug
2627 IF (ln_proc_level>=ln_dbg_level)
2628 THEN
2629 FND_LOG.STRING( ln_proc_level
2630 , l_Module_Prefix ||'.' || lv_procedure_name || '.begin'
2631 , 'Enter procedure'
2632 );
2633 END IF; --(ln_proc_level>=ln_dbg_level)
2634
2635 --Cursor for getting segment or attribute column
2636 FOR v_attribute_column_row IN attribute_column_cur
2637 LOOP
2638 lv_attribute_column := v_attribute_column_row.Attribute_Column;
2639 FND_FILE.PUT_LINE(Fnd_FILE.LOG, 'lv_attribute_column='||lv_attribute_column);
2640 EXIT;
2641 END LOOP;
2642
2643 --logging for debug
2644 IF (ln_proc_level>=ln_dbg_level)
2645 THEN
2646 FND_LOG.STRING( ln_proc_level
2647 , l_Module_Prefix ||'.' || lv_procedure_name || '.end'
2648 , 'Exit procedure'
2649 );
2650 END IF; -- (ln_proc_level>=ln_dbg_level)
2651
2652 RETURN lv_attribute_column;
2653 END Get_Attribute_Column;
2654
2655 --==========================================================================
2656 -- PROCEDURE NAME:
2657 --
2658 -- Get_Segment_Value_Desc Public
2659 --
2660 -- DESCRIPTION:
2661 --
2662 -- To get the description of segment value
2663 --
2664 -- PARAMETERS:
2665 -- In: pv_flexfield_code IN VARCHAR2 Flex code in flexfield assignment
2666 -- pv_flex_value IN VARCHAR2 Segment value
2667 -- pn_coa_id IN NUMBER Chart of account Id
2668 --
2669 -- DESIGN REFERENCES:
2670 -- TDD_1213_FIN_GL_P_CNAOV2_GL.doc
2671 --
2672 -- CHANGE HISTORY:
2673 -- 11-Aug-2010 Chaoqun Wu created
2674 --==========================================================================
2675 FUNCTION Get_Segment_Value_Desc
2676 (pv_flexfield_code IN VARCHAR2
2677 ,pv_flex_value IN VARCHAR2
2678 ,pn_coa_id IN NUMBER)
2679 RETURN VARCHAR2
2680 IS
2681 lv_segment_value_desc FND_FLEX_VALUES_TL.Description%TYPE := NULL;
2682 lv_procedure_name VARCHAR2(40) := 'Get_Segment_Value_Desc';
2683 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2684 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2685
2686
2687 CURSOR segment_value_cur
2688 IS
2689 SELECT FFV.Flex_Value Segment_Value
2690 ,FFVT.Description Segment_Value_Description
2691 FROM FND_ID_FLEX_SEGMENTS FIFS,
2692 Fnd_Flex_Values FFV,
2693 FND_FLEX_VALUES_TL FFVT
2694 WHERE FIFS.Application_Id = 101
2695 AND FIFS.Application_Column_Name = Get_Attribute_Column(pv_flexfield_code --parameter: pv_flexfield_code
2696 ,101
2697 ,pn_coa_id) -- parameter: pn_coa_id
2698 AND FIFS.Id_Flex_Num = pn_coa_id --parameter: pn_coa_id
2699 AND FIFS.Id_Flex_Code = 'GL#'
2700 AND FFV.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
2701 AND FFV.Flex_Value = pv_flex_value --parameter: pv_flex_value
2702 AND FFVT.Flex_Value_Id = FFV.Flex_Value_Id
2703 AND FFVT.LANGUAGE = userenv('LANG');
2704
2705 BEGIN
2706 --logging for debug
2707 IF (ln_proc_level>=ln_dbg_level)
2708 THEN
2709 FND_LOG.STRING( ln_proc_level
2710 , l_Module_Prefix ||'.' || lv_procedure_name || '.begin'
2711 , 'Enter procedure'
2712 );
2713 END IF; --(ln_proc_level>=ln_dbg_level)
2714
2715 --Cursor for getting segment value
2716 FOR v_segment_value_row IN segment_value_cur
2717 LOOP
2718 lv_segment_value_desc := v_segment_value_row.Segment_Value_Description;
2719 FND_FILE.PUT_LINE(Fnd_FILE.LOG, 'lv_segment_value_desc='||lv_segment_value_desc);
2720 EXIT;
2721 END LOOP;
2722
2723 --logging for debug
2724 IF (ln_proc_level>=ln_dbg_level)
2725 THEN
2726 FND_LOG.STRING( ln_proc_level
2727 , l_Module_Prefix ||'.' || lv_procedure_name || '.end'
2728 , 'Exit procedure'
2729 );
2730 END IF; -- (ln_proc_level>=ln_dbg_level)
2731
2732 RETURN lv_segment_value_desc;
2733 END Get_Segment_Value_Desc;
2734
2735 --==========================================================================
2736 -- PROCEDURE NAME:
2737 --
2738 -- Get_Attribute_Value Public
2739 --
2740 -- DESCRIPTION:
2741 --
2742 -- To get fund type code and budget source code for project
2743 --
2744 -- PARAMETERS:
2745 -- In: pv_flexfield_code IN VARCHAR2 Flex code in flexfield assignment
2746 -- pv_project_id IN VARCHAR2 Project Id
2747 -- pn_coa_id IN NUMBER Chart of account Id
2748 --
2749 -- DESIGN REFERENCES:
2750 -- TDD_1213_FIN_GL_P_CNAOV2_GL.doc
2751 --
2752 -- CHANGE HISTORY:
2753 -- 11-Aug-2010 Chaoqun Wu created
2754 --==========================================================================
2755 FUNCTION Get_Attribute_Value
2756 (pv_flexfield_code IN VARCHAR2
2757 ,pv_project_id IN VARCHAR2
2758 ,pn_coa_id IN NUMBER)
2759 RETURN VARCHAR2
2760 IS
2761 lv_atrribute_value Fnd_Flex_Values.ATTRIBUTE1%TYPE := NULL;
2762 lv_procedure_name VARCHAR2(40) := 'Get_Attribute_Value';
2763 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2764 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2765
2766 lv_cursor_sql VARCHAR2(2000);
2767 TYPE ref_cursor IS REF CURSOR;
2768 lc_attribute_value ref_cursor;
2769
2770 BEGIN
2771 --logging for debug
2772 IF (ln_proc_level>=ln_dbg_level)
2773 THEN
2774 FND_LOG.STRING( ln_proc_level
2775 , l_Module_Prefix ||'.' || lv_procedure_name || '.begin'
2776 , 'Enter procedure'
2777 );
2778 END IF; --(ln_proc_level>=ln_dbg_level)
2779
2780 lv_cursor_sql := 'SELECT FFV.'|| Get_Attribute_Column(pv_flexfield_code
2781 ,0
2782 ,pn_coa_id) || ' Attribute_Value';
2783 lv_cursor_sql := lv_cursor_sql || ' FROM FND_ID_FLEX_SEGMENTS FIFS,' ;
2784 lv_cursor_sql := lv_cursor_sql || ' Fnd_Flex_Values FFV';
2785 lv_cursor_sql := lv_cursor_sql || ' WHERE FIFS.Application_Id = 101';
2786 lv_cursor_sql := lv_cursor_sql || ' AND FIFS.Application_Column_Name = '''||Get_Attribute_Column('CPRO'
2787 ,101
2788 ,pn_coa_id)||'''';
2789 lv_cursor_sql := lv_cursor_sql || ' AND FIFS.Id_Flex_Num = '||pn_coa_id; --parameter: pn_coa_id
2790 lv_cursor_sql := lv_cursor_sql || ' AND FIFS.Id_Flex_Code = ''GL#''';
2791 lv_cursor_sql := lv_cursor_sql || ' AND FFV.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID';
2792 lv_cursor_sql := lv_cursor_sql || ' AND FFV.Flex_Value ='''||pv_project_id||''''; --parameter: pv_project_id
2793
2794 --Cursor for getting attribute value
2795 OPEN lc_attribute_value FOR lv_cursor_sql;
2796 FETCH lc_attribute_value
2797 INTO lv_atrribute_value;
2798 FND_FILE.PUT_LINE(Fnd_FILE.LOG, 'lv_atrribute_value='||lv_atrribute_value);
2799 CLOSE lc_attribute_value;
2800
2801 --logging for debug
2802 IF (ln_proc_level>=ln_dbg_level)
2803 THEN
2804 FND_LOG.STRING( ln_proc_level
2805 , l_Module_Prefix ||'.' || lv_procedure_name || '.end'
2806 , 'Exit procedure'
2807 );
2808 END IF; -- (ln_proc_level>=ln_dbg_level)
2809
2810 RETURN lv_atrribute_value;
2811 END Get_Attribute_Value;
2812
2813 --==========================================================================
2814 -- PROCEDURE NAME:
2815 --
2816 -- Get_Attribute_Value_Desc Public
2817 --
2818 -- DESCRIPTION:
2819 --
2820 -- To get fund type description and budget source description for project
2821 --
2822 -- PARAMETERS:
2823 -- In: pv_flexfield_code IN VARCHAR2 Flex code in flexfield assignment
2824 -- pv_flex_value IN VARCHAR2 Segment value
2825 -- pn_coa_id IN NUMBER Chart of account Id
2826 --
2827 -- DESIGN REFERENCES:
2828 -- TDD_1213_FIN_GL_P_CNAOV2_GL.doc
2829 --
2830 -- CHANGE HISTORY:
2831 -- 11-Aug-2010 Chaoqun Wu created
2832 --==========================================================================
2833 FUNCTION Get_Attribute_Value_Desc
2834 (pv_flexfield_code IN VARCHAR2
2835 ,pv_flex_value IN VARCHAR2
2836 ,pn_coa_id IN NUMBER)
2837 RETURN VARCHAR2
2838 IS
2839 lv_attribute_value_desc FND_FLEX_VALUES_TL.Description%TYPE := NULL;
2840 lv_procedure_name VARCHAR2(40) := 'Get_Attribute_Value_Desc';
2841 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2842 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2843
2844 CURSOR attribute_value_cur
2845 IS
2846 SELECT FFV.Flex_Value Attribute_Value
2847 , FFVT.Description Attribute_Value_Desc
2848 FROM FND_DESCR_FLEX_COL_USAGE_VL FUV,
2849 Fnd_Flex_Values FFV,
2850 FND_FLEX_VALUES_TL FFVT
2851 WHERE FUV.Application_Id = 0
2852 AND FUV.Application_Column_Name = Get_Attribute_Column(pv_flexfield_code
2853 ,0
2854 ,pn_coa_id) --Get_Attribute_Column(0,'AFTY'/'ABSO')
2855 AND FUV.Descriptive_FlexField_Name = 'FND_FLEX_VALUES'
2856 AND FFV.FLEX_VALUE_SET_ID = FUV.FLEX_VALUE_SET_ID
2857 AND FFV.Flex_Value = pv_flex_value --parameter: pv_flex_value
2858 AND FFVT.Flex_Value_Id = FFV.Flex_Value_Id
2859 AND FFVT.LANGUAGE = userenv('LANG');
2860
2861 BEGIN
2862 --logging for debug
2863 IF (ln_proc_level>=ln_dbg_level)
2864 THEN
2865 FND_LOG.STRING( ln_proc_level
2866 , l_Module_Prefix ||'.' || lv_procedure_name || '.begin'
2867 , 'Enter procedure'
2868 );
2869 END IF; --(ln_proc_level>=ln_dbg_level)
2870
2871 --Cursor for getting attribute value description
2872 FOR v_attribute_value_row IN attribute_value_cur
2873 LOOP
2874 lv_attribute_value_desc := v_attribute_value_row.Attribute_Value_Desc;
2875 FND_FILE.PUT_LINE(Fnd_FILE.LOG, 'lv_attribute_value_desc='||lv_attribute_value_desc);
2876 EXIT;
2877 END LOOP;
2878
2879 --logging for debug
2880 IF (ln_proc_level>=ln_dbg_level)
2881 THEN
2882 FND_LOG.STRING( ln_proc_level
2883 , l_Module_Prefix ||'.' || lv_procedure_name || '.end'
2884 , 'Exit procedure'
2885 );
2886 END IF; -- (ln_proc_level>=ln_dbg_level)
2887
2888 RETURN lv_attribute_value_desc;
2889 END Get_Attribute_Value_Desc;
2890
2891 --==========================================================================
2892 -- PROCEDURE NAME:
2893 -- get_balancing_segment public
2894 --
2895 -- DESCRIPTION:
2896 -- This procedure returns the balancing segment value of a CCID.
2897 --
2898 -- PARAMETERS:
2899 -- In: P_CC_ID NUMBER
2900 --
2901 -- DESIGN REFERENCES:
2902 -- None
2903 --
2904 -- CHANGE HISTORY:
2905 -- 11-Aug-2010 Chaoqun Wu created
2906 --===========================================================================
2907 FUNCTION get_balancing_segment
2908 (P_CC_ID IN NUMBER
2909 )
2910 RETURN VARCHAR2
2911 IS
2912 L_BALANCING_SEGMENT GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
2913 BEGIN
2914 SELECT
2915 DECODE(FSAV.APPLICATION_COLUMN_NAME, --segment FSAV.APPLICATION_COLUMN_NAME of gcc
2916 'SEGMENT1',GCC.SEGMENT1, 'SEGMENT2',GCC.SEGMENT2, 'SEGMENT3',GCC.SEGMENT3,
2917 'SEGMENT4',GCC.SEGMENT4, 'SEGMENT5',GCC.SEGMENT5, 'SEGMENT6',GCC.SEGMENT6,
2918 'SEGMENT7',GCC.SEGMENT7, 'SEGMENT8',GCC.SEGMENT8, 'SEGMENT9',GCC.SEGMENT9,
2919 'SEGMENT10',GCC.SEGMENT10, 'SEGMENT11',GCC.SEGMENT11, 'SEGMENT12',GCC.SEGMENT12,
2920 'SEGMENT13',GCC.SEGMENT13, 'SEGMENT14',GCC.SEGMENT14, 'SEGMENT15',GCC.SEGMENT15,
2921 'SEGMENT16',GCC.SEGMENT16, 'SEGMENT17',GCC.SEGMENT17, 'SEGMENT18',GCC.SEGMENT18,
2922 'SEGMENT19',GCC.SEGMENT19, 'SEGMENT20',GCC.SEGMENT20, 'SEGMENT21',GCC.SEGMENT21,
2923 'SEGMENT22',GCC.SEGMENT22, 'SEGMENT23',GCC.SEGMENT23, 'SEGMENT24',GCC.SEGMENT24,
2924 'SEGMENT25',GCC.SEGMENT25, 'SEGMENT26',GCC.SEGMENT26, 'SEGMENT27',GCC.SEGMENT27,
2925 'SEGMENT28',GCC.SEGMENT28, 'SEGMENT29',GCC.SEGMENT29, 'SEGMENT30',GCC.SEGMENT30)
2926 INTO L_BALANCING_SEGMENT
2927 FROM GL_CODE_COMBINATIONS GCC,
2928 FND_SEGMENT_ATTRIBUTE_VALUES FSAV
2929 WHERE GCC.CODE_COMBINATION_ID = P_CC_ID
2930 AND FSAV.ATTRIBUTE_VALUE = 'Y'
2931 AND FSAV.APPLICATION_ID = 101
2932 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
2933 AND FSAV.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
2934 AND FSAV.ID_FLEX_CODE = 'GL#';
2935
2936 RETURN L_BALANCING_SEGMENT;
2937 END get_balancing_segment;
2938
2939
2940
2941
2942 --==========================================================================
2943 -- PROCEDURE NAME:
2944 -- get_accounting_segment public
2945 --
2946 -- DESCRIPTION:
2947 -- This procedure returns the balancing segment value of a CCID.
2948 --
2949 -- PARAMETERS:
2950 -- In: P_CC_ID NUMBER
2951 --
2952 -- DESIGN REFERENCES:
2953 -- None
2954 --
2955 -- CHANGE HISTORY:
2956 -- 22-Oct-2012 Jar Wang created
2957 --===========================================================================
2958 FUNCTION Get_Accounting_Segment(P_CC_ID IN NUMBER) RETURN VARCHAR2 IS
2959 L_ACCOUNTING_SEGMENT GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
2960 BEGIN
2961 SELECT DECODE(FSAV.APPLICATION_COLUMN_NAME, --segment FSAV.APPLICATION_COLUMN_NAME of gcc
2962 'SEGMENT1',
2963 GCC.SEGMENT1,
2964 'SEGMENT2',
2965 GCC.SEGMENT2,
2966 'SEGMENT3',
2967 GCC.SEGMENT3,
2968 'SEGMENT4',
2969 GCC.SEGMENT4,
2970 'SEGMENT5',
2971 GCC.SEGMENT5,
2972 'SEGMENT6',
2973 GCC.SEGMENT6,
2974 'SEGMENT7',
2975 GCC.SEGMENT7,
2976 'SEGMENT8',
2977 GCC.SEGMENT8,
2978 'SEGMENT9',
2979 GCC.SEGMENT9,
2980 'SEGMENT10',
2981 GCC.SEGMENT10,
2982 'SEGMENT11',
2983 GCC.SEGMENT11,
2984 'SEGMENT12',
2985 GCC.SEGMENT12,
2986 'SEGMENT13',
2987 GCC.SEGMENT13,
2988 'SEGMENT14',
2989 GCC.SEGMENT14,
2990 'SEGMENT15',
2991 GCC.SEGMENT15,
2992 'SEGMENT16',
2993 GCC.SEGMENT16,
2994 'SEGMENT17',
2995 GCC.SEGMENT17,
2996 'SEGMENT18',
2997 GCC.SEGMENT18,
2998 'SEGMENT19',
2999 GCC.SEGMENT19,
3000 'SEGMENT20',
3001 GCC.SEGMENT20,
3002 'SEGMENT21',
3003 GCC.SEGMENT21,
3004 'SEGMENT22',
3005 GCC.SEGMENT22,
3006 'SEGMENT23',
3007 GCC.SEGMENT23,
3008 'SEGMENT24',
3009 GCC.SEGMENT24,
3010 'SEGMENT25',
3011 GCC.SEGMENT25,
3012 'SEGMENT26',
3013 GCC.SEGMENT26,
3014 'SEGMENT27',
3015 GCC.SEGMENT27,
3016 'SEGMENT28',
3017 GCC.SEGMENT28,
3018 'SEGMENT29',
3019 GCC.SEGMENT29,
3020 'SEGMENT30',
3021 GCC.SEGMENT30)
3022 INTO L_ACCOUNTING_SEGMENT
3023 FROM GL_CODE_COMBINATIONS GCC, FND_SEGMENT_ATTRIBUTE_VALUES FSAV
3024 WHERE GCC.CODE_COMBINATION_ID = P_CC_ID
3025 AND FSAV.ATTRIBUTE_VALUE = 'Y'
3026 AND FSAV.APPLICATION_ID = 101
3027 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
3028 AND FSAV.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
3029 AND FSAV.ID_FLEX_CODE = 'GL#';
3030
3031 RETURN L_ACCOUNTING_SEGMENT;
3032 Exception
3033 when others then
3034 return null;
3035 END Get_Accounting_Segment;
3036
3037
3038
3039 --==========================================================================
3040 -- PROCEDURE NAME:
3041 --
3042 -- Get_Budget_Type_Value Public
3043 --
3044 -- DESCRIPTION:
3045 --
3046 -- To get budget type value for project
3047 --
3048 -- PARAMETERS:
3049 -- In: pv_flexfield_code IN VARCHAR2 Flex code in flexfield assignment
3050 -- pv_project_id IN VARCHAR2 Project Id
3051 -- pn_coa_id IN NUMBER Chart of account Id
3052 --
3053 -- DESIGN REFERENCES:
3054 -- TDD_1213_FIN_GL_P_CNAOV2_GL.doc
3055 --
3056 -- CHANGE HISTORY:
3057 -- 18-Aug-2010 Richard Qi created
3058 --==========================================================================
3059 FUNCTION Get_Budget_Type_Value(pn_coa_id IN NUMBER
3060 ,pv_flexfield_code IN VARCHAR2
3061 ) RETURN VARCHAR2 IS
3062 lv_budget_type_value Fnd_Flex_Values.Flex_Value%TYPE := NULL;
3063 lv_procedure_name VARCHAR2(40) := 'Get_Attribute_Value';
3064 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3065 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
3066
3067 lv_cursor_sql VARCHAR2(2000);
3068 TYPE ref_cursor IS REF CURSOR;
3069 lc_budget_type_value ref_cursor;
3070
3071 BEGIN
3072 --logging for debug
3073 IF (ln_proc_level>=ln_dbg_level)
3074 THEN
3075 FND_LOG.STRING( ln_proc_level
3076 , l_Module_Prefix ||'.' || lv_procedure_name || '.begin'
3077 , 'Enter procedure'
3078 );
3079 END IF; --(ln_proc_level>=ln_dbg_level)
3080
3081 lv_cursor_sql := 'SELECT FFV.Flex_Value Budget_Type';
3082 lv_cursor_sql := lv_cursor_sql || ' FROM FND_ID_FLEX_SEGMENTS FIFS,';
3083 lv_cursor_sql := lv_cursor_sql || ' Fnd_Flex_Values FFV';
3084 lv_cursor_sql := lv_cursor_sql || ' WHERE FIFS.Application_Id = 101';
3085 lv_cursor_sql := lv_cursor_sql || ' AND FIFS.Application_Column_Name = '''||Get_Attribute_Column('CBTY'
3086 ,101
3087 ,pn_coa_id)||'''';
3088 lv_cursor_sql := lv_cursor_sql || ' AND FIFS.Id_Flex_Num = '||pn_coa_id; --parameter: pn_coa_id
3089 lv_cursor_sql := lv_cursor_sql || ' AND FIFS.Id_Flex_Code = ''GL#''';
3090 lv_cursor_sql := lv_cursor_sql || ' AND FFV.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID';
3091 lv_cursor_sql := lv_cursor_sql || ' AND FFV.'|| Get_Attribute_Column('ABCA'
3092 ,0
3093 ,pn_coa_id) || ' = '''||pv_flexfield_code||'''';
3094
3095 --Cursor for getting budget type value
3096 OPEN lc_budget_type_value FOR lv_cursor_sql;
3097 FETCH lc_budget_type_value
3098 INTO lv_budget_type_value;
3099 FND_FILE.PUT_LINE(Fnd_FILE.LOG, 'lv_budget_type_value='||lv_budget_type_value);
3100 CLOSE lc_budget_type_value;
3101
3102 --logging for debug
3103 IF (ln_proc_level>=ln_dbg_level)
3104 THEN
3105 FND_LOG.STRING( ln_proc_level
3106 , l_Module_Prefix ||'.' || lv_procedure_name || '.end'
3107 , 'Exit procedure'
3108 );
3109 END IF; -- (ln_proc_level>=ln_dbg_level)
3110
3111 RETURN lv_budget_type_value;
3112 END Get_Budget_Type_Value;
3113
3114 END Ja_Cn_Utility;
3115