[Home] [Help]
PACKAGE BODY: APPS.JA_CN_ACC_JE_ITEMIZATION_PKG
Source
1 PACKAGE BODY Ja_Cn_Acc_Je_Itemization_Pkg AS
2 --$Header: JACNAJIB.pls 120.9 2007/12/11 09:06:36 qzhao noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNAJIB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used in account and journal itemizatoin to |
13 --| generate |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE populate_journal_of_period |
17 --| PROCEDURE unitemize_journal_lines |
18 --| PROCEDURE generate_code_combination_view |
19 --| PROCEDURE get_period_range |
20 --| PROCEDURE transfer_gl_sla_to_cnao |
21 --| PROCEDURE generate_journal_and_line_num |
22 --| PROCEDURE purge_unmatch_lines |
23 --| PROCEDURE get_journal_approver |
24 --| PROCEDURE get_lookup_code |
25 --| |
26 --| HISTORY |
27 --| 02/21/2006 Qingjun Zhao Created |
28 --| 04/10/2006 Qingjun Zhao update generate_code_combiantion_view|
29 --| 05/16/2006 Qingjun Zhao Add procedure get_journal_approver |
30 --| 05/24/2006 Qingjun Zhao Add procedure purge_unmatch_lines |
31 --| 04/12/2007 Qingjun Zhao Update for Release 12,extract project|
32 --| ,third party by using Supporting |
33 --| Reference functionality |
34 --+========================================================================
35 l_Module_Prefix VARCHAR2(100) := 'JA_CN_ACC_JE_ITEMIZATION_PKG';
36 -- l_Ledger_Id NUMBER;
37 l_Chart_Of_Accounts_Id NUMBER;
38 l_Legal_Entity_Id NUMBER;
39 l_ledger_id number;
40 l_Project_Option VARCHAR2(240);
41 PROCEDURE Test_Concurrent_Conflict IS
42 l_Message VARCHAR2(1000) := 'This concurrent is used to test both concurrents call procedure in same package';
43 BEGIN
44 Fnd_File.Put_Line(Fnd_File.Log, l_Message);
45 END;
46 --==========================================================================
47 -- PROCEDURE NAME:
48 -- get_journal_approver Private
49 --
50 -- DESCRIPTION:
51 -- This procedure is used to get approver of journal in General Ledger
52 -- if the journal has been appoved
53 -- PARAMETERS:
54 -- In: p_request_id identifier of current session
55 --
56 --
57 -- DESIGN REFERENCES:
58 -- None
59 --
60 -- CHANGE HISTORY:
61 -- 05/16/2006 Qingjun Zhao Created
62 -- 04/29/2006 Qingjun Zhao Change for
63 --==========================================================================
64 PROCEDURE Get_Journal_Approver(p_Request_Id IN NUMBER) IS
65 l_Request_Id NUMBER := p_Request_Id;
66 l_Je_Header_Id NUMBER;
67 l_Batch_Name Gl_Je_Batches.NAME%TYPE;
68 l_Period_Name Gl_Periods.Period_Name%TYPE;
69 l_Approver VARCHAR2(100);
70 l_Approval_Status VARCHAR2(1);
71 l_Approver_Name VARCHAR2(100);
72 l_Step VARCHAR2(100);
73 l_Proc_Name VARCHAR2(30) := 'get_journal_approver';
74 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
75 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
76 l_Statement_Level NUMBER := Fnd_Log.Level_Statement;
77 l_Exception_Level NUMBER := Fnd_Log.Level_Exception;
78 CURSOR c_Journal IS
79 SELECT DISTINCT Jop.Je_Header_Id
80 FROM Ja_Cn_Journals_Of_Period Jop, Gl_Je_Sources_Tl Gjs
81 WHERE Jop.Request_Id = l_Request_Id
82 AND Gjs.Je_Source_Name = Jop.Je_Source
83 AND Gjs.Source_Lang = Userenv('LANG')
84 AND Gjs.LANGUAGE = Userenv('LANG')
85 AND Gjs.Journal_Approval_Flag = 'Y';
86 CURSOR c_Batch_Status IS
87 SELECT Jeb.Default_Period_Name, Jeb.NAME, Jeb.Approval_Status_Code
88 FROM Gl_Je_Headers Jeh, Gl_Je_Batches Jeb
89 WHERE Jeb.Je_Batch_Id = Jeh.Je_Batch_Id
90 AND Jeh.Je_Header_Id = l_Je_Header_Id;
91
92 CURSOR c_Batch_Approver IS
93 SELECT d.Text_Value
94 FROM Wf_Items t, Wf_Item_Attribute_Values d
95 WHERE d.Item_Key = t.Item_Key
96 AND d.NAME = 'APPROVER_NAME'
97 AND t.User_Key = l_Batch_Name
98 AND d.Item_Type = 'GLBATCH'
99 AND t.Begin_Date IN
100 (SELECT MAX(It.Begin_Date)
101 FROM Wf_Items It,
102 Wf_Item_Attribute_Values T1,
103 Wf_Item_Attribute_Values t
104 WHERE It.User_Key = l_Batch_Name
105 AND It.Item_Key = t.Item_Key
106 AND T1.Item_Type = 'GLBATCH'
107 AND T1.Item_Key = t.Item_Key
108 AND t.Item_Type = 'GLBATCH'
109 AND t.NAME = 'BATCH_NAME'
110 AND t.Text_Value = l_Batch_Name
111 AND T1.NAME = 'PERIOD_NAME'
112 AND T1.Text_Value = l_Period_Name);
113 -- group by it.item_key);
114
115 CURSOR c_Approver_Name IS
116 SELECT Last_Name || First_Name Full_Name
117 FROM Per_All_People_f
118 WHERE Person_Id =
119 (SELECT Employee_Id FROM Fnd_User WHERE User_Name = l_Approver);
120
121 BEGIN
122 --log
123 IF (l_Proc_Level >= l_Dbg_Level) THEN
124 Fnd_Log.STRING(l_Proc_Level,
125 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
126 'begin procedure');
127
128 END IF; --l_procedure_level >= l_runtime_level
129
130 OPEN c_Journal;
131 LOOP
132 FETCH c_Journal
133 INTO l_Je_Header_Id;
134 EXIT WHEN c_Journal%NOTFOUND;
135 --get batch name and period name
136 l_Step := To_Char(l_Je_Header_Id);
137 OPEN c_Batch_Status;
138 FETCH c_Batch_Status
139 INTO l_Period_Name, l_Batch_Name, l_Approval_Status;
140 l_Step := l_Period_Name || l_Batch_Name || l_Approval_Status;
141
142 IF Nvl(l_Approval_Status, 'N') = 'A' THEN
143 -- get approver
144 OPEN c_Batch_Approver;
145 FETCH c_Batch_Approver
146 INTO l_Approver;
147 l_Step := l_Approver;
148
149 IF c_Batch_Approver%FOUND THEN
150
151 OPEN c_Approver_Name;
152 FETCH c_Approver_Name
153 INTO l_Approver_Name;
154
155 IF c_Approver_Name%FOUND THEN
156 UPDATE Ja_Cn_Journal_Lines_Req t
157 SET t.Journal_Approver = l_Approver_Name
158 WHERE t.Je_Header_Id = l_Je_Header_Id;
159 END IF; --c_approver_name%found
160
161 CLOSE c_Approver_Name;
162 END IF; --c_batch_approver%found
163
164 CLOSE c_Batch_Approver;
165 END IF; --nvl(l_approval_status,'N') = 'A'
166 CLOSE c_Batch_Status;
167 END LOOP; --c_journal
168
169 CLOSE c_Journal;
170 -- log for debug
171 IF (l_Proc_Level >= l_Dbg_Level) THEN
172 Fnd_Log.STRING(l_Proc_Level,
173 l_Module_Prefix || '.' || l_Proc_Name || '.end',
174 'Enter procedure');
175 END IF; --(l_proc_level >= l_dbg_level)
176 EXCEPTION
177 WHEN OTHERS THEN
178 Fnd_File.Put_Line(Fnd_File.Log, 'l_step:' || l_Step);
179 --log for debug
180 IF (l_Proc_Level >= l_Dbg_Level) THEN
181 Fnd_Log.STRING(l_Proc_Level,
182 l_Module_Prefix || '.' || l_Proc_Name ||
183 '. Other_Exception ',
184 SQLCODE || ':' || SQLERRM);
185 END IF; --(l_proc_level >= l_dbg_level)
186 RAISE;
187 END Get_Journal_Approver;
188 --==========================================================================
189 -- FUNCTION NAME:
190 -- get_lookup_code Private
191 --
192 -- DESCRIPTION:
193 -- This function is used to get lookup code of lookup meaning,
194 -- PARAMETERS:
195 -- In: p_lookup_meaning lookup meaning
196 -- p_lookup_type lookup code
197 -- p_view_application_id view application, DEFAULT 0
198 -- p_security_group_id security group
199 --
200 --
201 -- DESIGN REFERENCES:
202 -- None
203 --
204 -- CHANGE HISTORY:
205 -- 02/21/2006 Qingjun Zhao Created
206 --==========================================================================
207 FUNCTION Get_Lookup_Code(p_Lookup_Meaning IN VARCHAR2,
208 p_Lookup_Type IN VARCHAR2,
209 p_View_Application_Id IN NUMBER DEFAULT 0,
210 p_Security_Group_Id IN NUMBER DEFAULT 0)
211 RETURN VARCHAR2 IS
212
213 l_Procedure_Name VARCHAR2(30) := 'get_lookup_code';
214 l_Lookup_Code Fnd_Lookup_Values.Lookup_Code%TYPE := NULL;
215 l_Runtime_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
216 l_Procedure_Level NUMBER := Fnd_Log.Level_Procedure;
217 l_Statement_Level NUMBER := Fnd_Log.Level_Statement;
218 l_Exception_Level NUMBER := Fnd_Log.Level_Exception;
219
220 -- this cursor is to get looup_meaning under some lookup_code
221 CURSOR c_Lookup IS
222 SELECT Flv.Lookup_Code
223 FROM Fnd_Lookup_Values Flv
224 WHERE Flv.LANGUAGE = Userenv('LANG')
225 AND Flv.Lookup_Type = p_Lookup_Type
226 AND Flv.Meaning = p_Lookup_Meaning
227 AND Flv.View_Application_Id = p_View_Application_Id
228 AND Flv.Security_Group_Id = p_Security_Group_Id;
229
230 BEGIN
231 --log
232 IF (l_Procedure_Level >= l_Runtime_Level) THEN
233 Fnd_Log.STRING(l_Procedure_Level,
234 l_Module_Prefix || '.' || l_Procedure_Name || '.begin',
235 'begin procedure');
236 END IF; --l_procedure_level >= l_runtime_level
237
238 IF p_Lookup_Meaning IS NULL THEN
239 l_Lookup_Code := NULL;
240 ELSE
241 OPEN c_Lookup;
242 FETCH c_Lookup
243 INTO l_Lookup_Code;
244 IF c_Lookup%NOTFOUND THEN
245 l_Lookup_Code := NULL;
246 END IF;
247 CLOSE c_Lookup;
248 END IF; --IF p_lookup_code IS NULL
249
250 --log
251 IF (l_Procedure_Level >= l_Runtime_Level) THEN
252 Fnd_Log.STRING(l_Procedure_Level,
253 l_Module_Prefix || '.' || l_Procedure_Name || '.end',
254 'end procedure');
255 END IF; --l_procedure_level >= l_runtime_level
256
257 RETURN l_Lookup_Code;
258
259 END Get_Lookup_Code;
260 --==========================================================================
261 -- PROCEDURE NAME:
262 -- populate_journal_of_period Private
263 --
264 -- DESCRIPTION:
265 -- This procedure is used to populate journal with period ,journal
266 -- soruce and category, which company segment is possessed by current
267 -- legal entity into table JA_CN_JOURNALS_OF_PERIOD
268 -- PARAMETERS:
269 -- In: p_start_period the start period name from which
270 -- current SOB is start-up
271 -- p_end_period the till period name to which
272 -- the CNAO journal should be processed
273 -- p_request_id identifier of current session
274 --
275 --
276 -- DESIGN REFERENCES:
277 -- None
278 --
279 -- CHANGE HISTORY:
280 -- 02/21/2006 Qingjun Zhao Created
281 --==========================================================================
282 PROCEDURE Populate_Journal_Of_Period(p_Start_Period IN VARCHAR2,
283 p_ledger_id in number,
284 p_legal_entity_id in number,
285 p_End_Period IN VARCHAR2,
286 p_Request_Id IN NUMBER) IS
287 l_Populate_Journal_Sql VARCHAR2(4000);
288 l_Company_Column_Name VARCHAR2(30);
289 l_Start_Period VARCHAR2(15);
290 l_End_Period VARCHAR2(15);
291 l_Request_Id NUMBER;
292 l_ledger_id number;
293 l_legal_entity_id number;
294 -- l_Populate_Bsv_Flag number;
295 -- l_Populate_Bsv_F varchar2(1);
296 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
297 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
298 l_Proc_Name VARCHAR2(100) := 'populate_journal_of_period';
299 l_Populate_Bsv_Flag VARCHAR2(1);
300 BEGIN
301
302 --log for debug
303 IF (l_Proc_Level >= l_Dbg_Level) THEN
304 Fnd_Log.STRING(l_Proc_Level,
305 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
306 'Enter procedure');
307 Fnd_Log.STRING(l_Proc_Level,
308 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
309 'p_start_period: ' || p_Start_Period);
310 Fnd_Log.STRING(l_Proc_Level,
311 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
312 'p_end_period: ' || p_End_Period);
313 Fnd_Log.STRING(l_Proc_Level,
314 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
315 'p_request_id: ' || p_Request_Id);
316 END IF; --(l_proc_level >= l_dbg_level)
317
318 l_Start_Period := p_Start_Period;
319 l_End_Period := p_End_Period;
320 l_Request_Id := p_Request_Id;
321 l_ledger_id := p_ledger_id;
322 l_legal_entity_id := P_legal_entity_id;
323 --populate BSV for current legal entity and ledger
324 l_Populate_Bsv_Flag := Ja_Cn_Utility.Populate_Ledger_Le_Bsv_Gt(l_Ledger_Id,
325 l_Legal_Entity_Id);
326 IF l_Populate_Bsv_Flag = 'F' THEN
327 IF (l_Proc_Level >= l_Dbg_Level) THEN
328 Fnd_Log.STRING(l_Proc_Level,
329 l_Module_Prefix || '.' || l_Proc_Name,
330 'fail to populate BSV');
331 END IF; --(l_proc_level >= l_dbg_level)
332 END IF;
333
334 --get application column name of company segment
335 SELECT Fsav.Application_Column_Name
336 INTO l_Company_Column_Name
337 FROM Fnd_Id_Flex_Segments Fifs,
338 Fnd_Segment_Attribute_Values Fsav,
339 Gl_Ledgers Led
340 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
341 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
342 AND Fsav.Segment_Attribute_Type = 'GL_BALANCING'
343 AND Fsav.Attribute_Value = 'Y'
344 AND Fifs.Application_Id = 101
345 and fifs.id_flex_code = fsav.id_flex_code
346 and fifs.id_flex_code = 'GL#'
347 AND Fifs.Application_Id = Fsav.Application_Id
348 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
349 AND Led.Ledger_Id = l_Ledger_Id;
350
351 --generate dynamic sql to populate journal into ja_cn_journals_of_period,
352 --which will be itemized.
353 l_Populate_Journal_Sql := 'INSERT INTO ja_cn_journals_of_period' ||
354 '(je_header_id' || ',je_line_num' ||
355 ',period_name' || ',je_category' ||
356 ',je_source' || ',legal_entity_id' ||
357 ',request_id,effective_date)' ||
358 'SELECT /*+index(jop,ja_cn_journals_of_period_n3)+*/ ' ||
359 ' jeh.je_header_id' ||
360 ' ,jel.je_line_num' ||
361 ' ,jeh.period_name' ||
362 ' ,jeh.je_category' ||
363 ' ,jeh.je_source' ||
364 ' ,bsv.legal_entity_id' || ',' ||
365 l_Request_Id ||
366 ',jeh.default_effective_date ' ||
367 ' FROM gl_je_headers jeh' ||
368 ' ,gl_je_lines jel' ||
369 ' ,gl_code_combinations gcc' ||
370 ' ,gl_periods gp' ||
371 ' ,gl_ledgers led' ||
372 ' ,ja_cn_ledger_le_bsv_gt bsv' ||
373 ' WHERE jeh.je_header_id = jel.je_header_id' ||
374 ' AND jeh.status = ''P''' ||
375 ' AND jeh.period_name = gp.period_name' ||
376 ' AND jel.code_combination_id = gcc.code_combination_id' ||
377 ' AND jeh.LEDGER_ID = ' || l_Ledger_Id ||
378 ' AND gcc.' || l_Company_Column_Name ||
379 ' = bsv.BAL_SEG_VALUE' ||
380 ' AND bsv.legal_entity_id = ' ||
381 l_Legal_Entity_Id ||
382 ' AND gp.start_date BETWEEN' ||
383 ' (SELECT start_date' ||
384 ' FROM gl_periods' ||
385 ' WHERE period_name =''' ||
386 l_Start_Period || '''' ||
387 ' AND period_set_name = led.period_set_name)' ||
388 ' AND (SELECT start_date' ||
389 ' FROM gl_periods' ||
390 ' WHERE period_name =''' ||
391 l_End_Period || '''' ||
392 ' AND period_set_name = led.period_set_name)' ||
393 ' AND gp.period_set_name = led.period_set_name' ||
394 ' AND gp.period_type = led.accounted_period_type' ||
395 ' AND led.ledger_id = jeh.ledger_id' ||
396 ' AND nvl(jel.global_attribute2' ||
397 ' ,''U'') <> ''P''';
398
399 --log for debug
400 IF (l_Proc_Level >= l_Dbg_Level) THEN
401 Fnd_Log.STRING(l_Proc_Level,
402 l_Module_Prefix || '.' || l_Proc_Name,
403 'l_populate_journal_sql:' || l_Populate_Journal_Sql);
404 END IF; --(l_proc_level >= l_dbg_level)
405
406 EXECUTE IMMEDIATE l_Populate_Journal_Sql;
407 COMMIT;
408 -- log for debug
409 IF (l_Proc_Level >= l_Dbg_Level) THEN
410 Fnd_Log.STRING(l_Proc_Level,
411 l_Module_Prefix || '.' || l_Proc_Name || '.end',
412 'Enter procedure');
413 END IF; --(l_proc_level >= l_dbg_level)
414 EXCEPTION
415 WHEN OTHERS THEN
416
417 --log for debug
418 IF (l_Proc_Level >= l_Dbg_Level) THEN
419 Fnd_Log.STRING(l_Proc_Level,
420 l_Module_Prefix || '.' || l_Proc_Name ||
421 '. Other_Exception ',
422 SQLCODE || ':' || SQLERRM);
423 END IF; --(l_proc_level >= l_dbg_level)
424 RAISE;
425 END Populate_Journal_Of_Period;
426
427 --==========================================================================
428 -- PROCEDURE NAME:
429 -- generate_code_combination_view private
430 --
431 -- DESCRIPTION:
432 -- This procedure is used to populate account segment, company segment
433 -- cost center segment and project number if project option as 'COA'
434 -- into view JA_CN_CODE_COMBINATION_V
435 -- PARAMETERS:
436 --
437 --
438 --
439 -- DESIGN REFERENCES:
440 -- None
441 --
442 -- CHANGE HISTORY:
443 -- 02/21/2006 Qingjun Zhao Created
444 -- 04/10/2006 Qingjun Zhao Deal with this situation which Cost
445 -- segment is NULL in current Chart of
446 -- account
447 --===========================================================================
448 PROCEDURE Generate_Code_Combination_View(p_ledger_id in number) IS
449
450 l_Create_View_Sql VARCHAR2(4000);
451 l_Company_Column_Name VARCHAR2(30);
452 l_Account_Column_Name VARCHAR2(30);
453 l_Cost_Column_Name VARCHAR2(30);
454 l_Project_Column_Name VARCHAR2(30);
455 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
456 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
457 l_Proc_Name VARCHAR2(100) := 'generate_code_combination_view';
458 l_Second_Track_Col_Name VARCHAR2(30);
459 l_Other_Cols_Name VARCHAR2(200);
460 l_ledger_id number;
461
462 cursor c_company_segment is
463 SELECT led.bal_seg_column_name
464 from gl_ledgers led
465 where Led.Ledger_Id = l_Ledger_Id;
466
467 CURSOR c_Cost_Center IS
468 SELECT Fsav.Application_Column_Name
469 FROM Fnd_Id_Flex_Segments Fifs,
470 Fnd_Segment_Attribute_Values Fsav,
471 Gl_Ledgers Led
472 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
473 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
474 AND Fsav.Segment_Attribute_Type = 'FA_COST_CTR'
475 AND Fsav.Attribute_Value = 'Y'
476 AND Fifs.Application_Id = 101
477 and fsav.id_flex_code = fifs.id_flex_code
478 and fsav.id_flex_code = 'GL#'
479 AND Fifs.Application_Id = Fsav.Application_Id
480 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
481 AND Led.Ledger_Id = l_Ledger_Id;
482
483 --jogen
484 CURSOR c_Segements IS
485 SELECT Fsav.Application_Column_Name
486 FROM Fnd_Id_Flex_Segments Fifs,
487 Fnd_Segment_Attribute_Values Fsav,
488 Gl_Ledgers Led
489 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
490 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
491 AND Fsav.Segment_Attribute_Type = 'GL_GLOBAL'
492 AND Fsav.Attribute_Value = 'Y'
493 AND Fifs.Application_Id = 101
494 and fifs.id_flex_code = fsav.id_flex_code
495 and fifs.id_flex_code = 'GL#'
496 AND Fifs.Application_Id = Fsav.Application_Id
497 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
498 AND Led.Ledger_Id = l_Ledger_Id;
499
500 --jogen
501 BEGIN
502
503 --log for debug
504 IF (l_Proc_Level >= l_Dbg_Level) THEN
505 Fnd_Log.STRING(l_Proc_Level,
506 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
507 'Enter procedure');
508 END IF; --(l_proc_level >= l_dbg_level)
509 l_ledger_id := p_ledger_id;
510 --get application column name of company segment
511 SELECT led.bal_seg_column_name
512 INTO l_Company_Column_Name
513 from gl_ledgers led
514 where Led.Ledger_Id = l_Ledger_Id;
515
516 --get application column name of account segment
517 SELECT Fsav.Application_Column_Name
518 INTO l_Account_Column_Name
519 FROM Fnd_Id_Flex_Segments Fifs,
520 Fnd_Segment_Attribute_Values Fsav,
521 Gl_Ledgers Led
522 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
523 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
524 AND Fsav.Segment_Attribute_Type = 'GL_ACCOUNT'
525 AND Fsav.Attribute_Value = 'Y'
526 AND Fifs.Application_Id = 101
527 and fsav.id_flex_code = fifs.id_flex_code
528 and fsav.id_flex_code = 'GL#'
529 AND Fifs.Application_Id = Fsav.Application_Id
530 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
531 AND Led.Ledger_Id = l_Ledger_Id;
532 l_Create_View_Sql := 'select GCC.CODE_COMBINATION_ID,led.ledger_id,' ||
533 'gcc.' || l_Company_Column_Name ||
534 ' company_segment,';
535 l_Create_View_Sql := l_Create_View_Sql || 'gcc.' ||
536 l_Account_Column_Name || ' account_segment,';
537
538 --get application column name of cost center segment
539 OPEN c_Cost_Center;
540 FETCH c_Cost_Center
541 INTO l_Cost_Column_Name;
542
543 IF c_Cost_Center%NOTFOUND THEN
544 CLOSE c_Cost_Center;
545 l_Create_View_Sql := l_Create_View_Sql ||
546 ' to_char(null) cost_segment,';
547 ELSE
548 l_Create_View_Sql := l_Create_View_Sql || 'gcc.' ||
549 l_Cost_Column_Name || ' cost_segment,';
550 CLOSE c_Cost_Center;
551 END IF; --c_cost_center%NOTFOUND
552
553 IF l_Project_Option = 'COA' THEN
554 --get application column name of project segment
555 SELECT Coa_Segment
556 INTO l_Project_Column_Name
557 FROM Ja_Cn_Sub_Acc_Sources_All
558 WHERE Chart_Of_Accounts_Id = l_Chart_Of_Accounts_Id;
559 l_Create_View_Sql := l_Create_View_Sql || 'gcc.' ||
560 l_Project_Column_Name || ' project_number,';
561 ELSE
562 l_Create_View_Sql := l_Create_View_Sql || 'to_char(null)' ||
563 ' project_number,';
564 END IF; --l_project_option = 'COA'
565
566 ---jogen
567 BEGIN
568 SELECT Fsav.Application_Column_Name
569 INTO l_Second_Track_Col_Name
570 FROM Fnd_Id_Flex_Segments Fifs,
571 Fnd_Segment_Attribute_Values Fsav,
572 Gl_Ledgers Led
573 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
574 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
575 AND Fsav.Segment_Attribute_Type = 'GL_SECONDARY_TRACKING'
576 AND Fsav.Attribute_Value = 'Y'
577 AND Fifs.Application_Id = 101
578 and fifs.id_flex_code = fsav.id_flex_code
579 and fsav.id_flex_code = 'GL#'
580 AND Fifs.Application_Id = Fsav.Application_Id
581 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
582 AND Led.Ledger_Id = l_Ledger_Id;
583 EXCEPTION
584 WHEN No_Data_Found THEN
585 NULL;
586 END;
587
588 IF l_Second_Track_Col_Name IS NULL THEN
589 l_Second_Track_Col_Name := 'NULL';
590 END IF;
591
592 FOR Rec_Segment IN c_Segements LOOP
593 IF Rec_Segment.Application_Column_Name NOT IN
594 (l_Company_Column_Name, l_Account_Column_Name, l_Cost_Column_Name,
595 l_Second_Track_Col_Name) THEN
596 l_Other_Cols_Name := l_Other_Cols_Name || '||''.''||' ||
597 Rec_Segment.Application_Column_Name;
598 END IF;
599 END LOOP;
600
601 IF l_Other_Cols_Name IS NULL THEN
602 l_Other_Cols_Name := 'NULL';
603 ELSE
604 l_Other_Cols_Name := Substr(l_Other_Cols_Name, 8);
605 END IF;
606
607 l_Create_View_Sql := l_Create_View_Sql || l_Second_Track_Col_Name ||
608 ' second_tracking_col,' || l_Other_Cols_Name ||
609 ' other_columns,';
610 --jogen
611 l_Create_View_Sql := l_Create_View_Sql ||
612 'to_number(null) project_id from gl_code_combinations gcc,' ||
613 ' GL_LEDGERS led where led.chart_of_accounts_id ' ||
614 ' = gcc.chart_of_accounts_id';
615
616 l_Create_View_Sql := 'create or replace view ja_cn_code_combination_v as ' ||
617 l_Create_View_Sql;
618
619 --log for debug
620 IF (l_Proc_Level >= l_Dbg_Level) THEN
621 Fnd_Log.STRING(l_Proc_Level,
622 l_Module_Prefix || '.' || l_Proc_Name,
623 'l_create_view_sql:' || l_Create_View_Sql);
624 END IF; --(l_proc_level >= l_dbg_level)
625
626 EXECUTE IMMEDIATE l_Create_View_Sql;
627
628 --log for dubug
629 IF (l_Proc_Level >= l_Dbg_Level) THEN
630 Fnd_Log.STRING(l_Proc_Level,
631 l_Module_Prefix || '.' || l_Proc_Name || '.end',
632 'Enter procedure');
633 END IF; --(l_proc_level >= l_dbg_level)
634
635 EXCEPTION
636 WHEN OTHERS THEN
637 FND_FILE.put_line(FND_FILE.OUTPUT,SQLCODE || ':' || SQLERRM);
638 --log for debug
639 IF (l_Proc_Level >= l_Dbg_Level) THEN
640 Fnd_Log.STRING(l_Proc_Level,
641 l_Module_Prefix || '.' || l_Proc_Name ||
642 '. Other_Exception ',
643 SQLCODE || ':' || SQLERRM);
644 END IF; --(l_proc_level >= l_dbg_level)
645 RAISE;
646 END Generate_Code_Combination_View;
647 --==========================================================================
648 -- PROCEDURE NAME:
649 -- get_period_range private
650 --
651 -- DESCRIPTION:
652 -- This procedure is used to get range of period in which journal lines
653 -- will be itemized
654 -- PARAMETERS:
655 -- p_period_name period inputted by user
656 -- p_start_period_name start period
657 -- p_end_period_name end period
658 --
659 -- DESIGN REFERENCES:
660 -- None
661 --
662 -- CHANGE HISTORY:
663 -- 02/21/2006 Qingjun Zhao Created
664 --===========================================================================
665 PROCEDURE Get_Period_Range(p_Period_Name IN VARCHAR2,
666 p_ledger_id in number,
667 p_Start_Period_Name OUT NOCOPY VARCHAR2,
668 p_End_Period_Name OUT NOCOPY VARCHAR2) IS
669
670 l_Period_Name VARCHAR2(15);
671 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
672 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
673 l_Proc_Name VARCHAR2(100) := 'get_period_range';
674 l_ledger_id number;
675 BEGIN
676
677 --log for debug
678 IF (l_Proc_Level >= l_Dbg_Level) THEN
679 Fnd_Log.STRING(l_Proc_Level,
680 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
681 'Enter procedure');
682 Fnd_Log.STRING(l_Proc_Level,
683 l_Module_Prefix || '.' || l_Proc_Name || '.prameter',
684 'P_period_name:' || p_Period_Name);
685 END IF; --(l_proc_level >= l_dbg_level)
686
687 l_Period_Name := p_Period_Name;
688 l_ledger_id := p_ledger_id;
689 --get the first period of current led
690 SELECT Gp.Period_Name
691 INTO p_Start_Period_Name
692 FROM Gl_Periods Gp, Gl_Ledgers Led
693 WHERE Led.Ledger_Id = l_Ledger_Id
694 AND Led.Period_Set_Name = Gp.Period_Set_Name
695 AND Led.Accounted_Period_Type = Gp.Period_Type
696 AND Gp.Start_Date IN
697 (SELECT MIN(Start_Date)
698 FROM Gl_Periods Gp
699 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
700 AND Led.Accounted_Period_Type = Gp.Period_Type);
701
702 -- if parameter period is null then pick up last open period as end period
703 IF l_Period_Name IS NULL THEN
704 SELECT Gp.Period_Name
705 INTO p_End_Period_Name
706 FROM Gl_Periods Gp, Gl_Ledgers Led
707 WHERE Led.Ledger_Id = l_Ledger_Id
708 AND Led.Period_Set_Name = Gp.Period_Set_Name
709 AND Led.Accounted_Period_Type = Gp.Period_Type
710 AND Gp.Start_Date IN
711 (SELECT MAX(Start_Date)
712 FROM Gl_Periods Gp
713 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
714 AND Led.Accounted_Period_Type = Gp.Period_Type);
715 ELSE
716 p_End_Period_Name := l_Period_Name;
717 END IF; --l_period_name IS NULL
718
719 --log for debug
720 IF (l_Proc_Level >= l_Dbg_Level) THEN
721 Fnd_Log.STRING(l_Proc_Level,
722 l_Module_Prefix || '.' || l_Proc_Name || '.parameter',
723 'p_start_period_name: ' || p_Start_Period_Name);
724 Fnd_Log.STRING(l_Proc_Level,
725 l_Module_Prefix || '.' || l_Proc_Name || '.parameter',
726 'p_end_period_name: ' || p_End_Period_Name);
727 Fnd_Log.STRING(l_Proc_Level,
728 l_Module_Prefix || '.' || l_Proc_Name || '.end',
729 'Exit procedure');
730 END IF; --( l_proc_level >= l_dbg_level )
731
732 EXCEPTION
733 WHEN OTHERS THEN
734
735 --log for debug
736 IF (l_Proc_Level >= l_Dbg_Level) THEN
737 Fnd_Log.STRING(l_Proc_Level,
738 l_Module_Prefix || '.' || l_Proc_Name ||
739 '. Other_Exception ',
740 SQLCODE || ':' || SQLERRM);
741 END IF; --(l_proc_level >= l_dbg_level)
742 RAISE;
743 END Get_Period_Range;
744
745 --==========================================================================
746 -- PROCEDURE NAME:
747 -- purge_unmatch_lines Private
748 --
749 -- DESCRIPTION:
750 -- This procedure is used to populate the journals which cannot be
751 -- drill down into possible sub legder or which is inputed directly
752 -- in manual way in Oracle General Ledger Module
753 -- PARAMETERS:
754 -- In: p_request_id identifier of current session
755 --
756 -- DESIGN REFERENCES:
757 -- None
758 --
759 -- CHANGE HISTORY:
760 -- 02/21/2006 Qingjun Zhao Created
761 --===========================================================================
762 PROCEDURE Purge_Unmatch_Lines(p_Request_Id IN NUMBER) IS
763 l_Request_Id NUMBER;
764 l_Je_Line_Num NUMBER;
765 l_Je_Header_Id NUMBER;
766 l_Error_Msg VARCHAR2(2000);
767 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
768 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
769 l_Proc_Name VARCHAR2(100) := 'purge_unmatch_lines';
770 CURSOR c_Unmatch_Lines IS
771 SELECT /*+ index(jel,gl_je_lines_u1)*/
772 Req.Je_Header_Id, Req.Je_Line_Num
773 FROM (SELECT SUM(Nvl(Req.Accounted_Dr, 0) - Nvl(Req.Accounted_Cr, 0)) Accounted_Amount,
774 SUM(Nvl(Req.Entered_Dr, 0) - Nvl(Req.Entered_Cr, 0)) Entered_Amount,
775 Req.Je_Header_Id,
776 Req.Je_Line_Num
777 FROM Ja_Cn_Journal_Lines_Req Req
778 WHERE Req.Request_Id = l_Request_Id
779 GROUP BY Req.Je_Header_Id, Req.Je_Line_Num) Req,
780 Gl_Je_Lines Jel
781 WHERE (Nvl(Jel.Accounted_Dr, 0) - Nvl(Jel.Accounted_Cr, 0) <>
782 Req.Accounted_Amount OR
783 Nvl(Jel.Entered_Dr, 0) - Nvl(Jel.Entered_Cr, 0) <>
784 Req.Entered_Amount)
785 AND Jel.Je_Line_Num = Req.Je_Line_Num
786 AND Jel.Je_Header_Id = Req.Je_Header_Id;
787 BEGIN
788 --log for debug
789 IF (l_Proc_Level >= l_Dbg_Level) THEN
790 Fnd_Log.STRING(l_Proc_Level,
791 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
792 'Enter procedure');
793 Fnd_Log.STRING(l_Proc_Level,
794 l_Module_Prefix || '.' || l_Proc_Name || '.prameter',
795 'P_request_id:' || p_Request_Id);
796 END IF; --(l_proc_level >= l_dbg_level)
797
798 l_Request_Id := p_Request_Id;
799 OPEN c_Unmatch_Lines;
800
801 LOOP
802 FETCH c_Unmatch_Lines
803 INTO l_Je_Header_Id, l_Je_Line_Num;
804 EXIT WHEN c_Unmatch_Lines%NOTFOUND;
805 --Raise error message for caller
806 Fnd_Message.Set_Name(Application => 'JA', NAME => 'JA_CN_XXXX');
807 Fnd_Message.Set_Token('header id', l_Je_Header_Id, TRUE);
808 Fnd_Message.Set_Token('line num', l_Je_Line_Num, TRUE);
809 l_Error_Msg := Fnd_Message.Get;
810
811 --Output error message
812 Fnd_File.Put_Line(Fnd_File.Log, l_Error_Msg);
813
814 -- log for debug
815 IF (l_Proc_Level >= l_Dbg_Level) THEN
816 Fnd_Log.STRING(l_Proc_Level,
817 l_Module_Prefix || '.' || l_Proc_Name,
818 l_Error_Msg);
819 END IF; --(l_proc_level >= l_dbg_level)
820
821 DELETE FROM Ja_Cn_Journal_Lines_Req
822 WHERE Je_Header_Id = l_Je_Header_Id
823 AND Je_Line_Num = l_Je_Line_Num;
824
825 END LOOP;
826
827 CLOSE c_Unmatch_Lines;
828
829 -- log for debug
830 IF (l_Proc_Level >= l_Dbg_Level) THEN
831 Fnd_Log.STRING(l_Proc_Level,
832 l_Module_Prefix || '.' || l_Proc_Name || '.end',
833 'Enter procedure');
834 END IF; --(l_proc_level >= l_dbg_level)
835 EXCEPTION
836 WHEN OTHERS THEN
837
838 --log for debug
839 IF (l_Proc_Level >= l_Dbg_Level) THEN
840 Fnd_Log.STRING(l_Proc_Level,
841 l_Module_Prefix || '.' || l_Proc_Name ||
842 '. Other_Exception ',
843 SQLCODE || ':' || SQLERRM);
844 END IF; --(l_proc_level >= l_dbg_level)
845 RAISE;
846 END Purge_Unmatch_Lines;
847 --==========================================================================
848 -- PROCEDURE NAME:
849 -- unitemize_journal_lines Private
850 --
851 -- DESCRIPTION:
852 -- This procedure is used to populate the journals which cannot be
853 -- drill down into possible sub legder or which is inputed directly
854 -- in manual way in Oracle General Ledger Module
855 -- PARAMETERS:
856 -- In: p_project_option porject option
857 -- p_request_id identifier of current session
858 --
859 -- DESIGN REFERENCES:
860 -- None
861 --
862 -- CHANGE HISTORY:
863 -- 02/21/2006 Qingjun Zhao Created
864 -- 07/12/2007 Yanbo Liu Updated
865 --===========================================================================
866
867 PROCEDURE Unitemize_Journal_Lines(p_Project_Option IN VARCHAR2,
868 p_Request_Id IN NUMBER) IS
869
870 l_Request_Id NUMBER;
871 l_Project_Option Ja_Cn_Sub_Acc_Sources_All.Project_Source_Flag%TYPE;
872 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
873 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
874 l_Proc_Name VARCHAR2(100) := 'unitemize_journal_lines';
875
876 BEGIN
877
878 --log for debug
879 IF (l_Proc_Level >= l_Dbg_Level) THEN
880 Fnd_Log.STRING(l_Proc_Level,
881 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
882 'Enter procedure');
883 Fnd_Log.STRING(l_Proc_Level,
884 l_Module_Prefix || '.' || l_Proc_Name || '.prameter',
885 'P_request_id:' || p_Request_Id);
886 Fnd_Log.STRING(l_Proc_Level,
887 l_Module_Prefix || '.' || l_Proc_Name || '.prameter',
888 'p_project_option:' || p_Project_Option);
889
890 END IF; --(l_proc_level >= l_dbg_level)
891
892 l_Request_Id := p_Request_Id;
893 l_Project_Option := p_Project_Option;
894 INSERT INTO Ja_Cn_Journal_Lines_Req
895 (Je_Header_Id,
896 Ledger_Id,
897 Legal_Entity_Id,
898 Journal_Number,
899 Je_Category,
900 Default_Effective_Date,
901 Period_Name,
902 Currency_Code,
903 Currency_Conversion_Rate,
904 Je_Line_Num,
905 Line_Number,
906 Description,
907 Company_Segment,
908 Code_Combination_Id,
909 Cost_Center,
910 Third_Party_Id,
911 Third_Party_Number,
912 Personnel_Id,
913 Personnel_Number,
914 Project_Number,
915 Project_Source,
916 Account_Segment,
917 Entered_Dr,
918 Entered_Cr,
919 Accounted_Dr,
920 Accounted_Cr,
921 Status,
922 Created_By,
923 Creation_Date,
924 Last_Updated_By,
925 Last_Update_Date,
926 Last_Update_Login,
927 Populate_Code,
928 Request_Id,
929 Journal_Created_By,
930 Journal_Posted_By)
931 SELECT Jel.Je_Header_Id Je_Header_Id,
932 Jeh.Ledger_Id Ledger_Id,
933 Jop.Legal_Entity_Id Legal_Entity_Id,
934 To_Number(NULL) Journal_Number,
935 Jeh.Je_Category Je_Category,
936 Jeh.Default_Effective_Date Default_Effective_Date,
937 Jeh.Period_Name Period_Name,
938 Jeh.Currency_Code Currency_Code,
939 Jeh.Currency_Conversion_Rate Currency_Conversion_Rate,
940 Jel.Je_Line_Num Je_Line_Num,
941 To_Number(NULL) Line_Number,
942 Nvl(Jel.Description, Jeh.Description) Description,
943 Jcc.Company_Segment Company_Segment,
944 Jcc.Code_Combination_Id Code_Combination_Id,
945 Jcc.Cost_Segment Cost_Segment,
946 To_Number(NULL) Third_Party_Id,
947 To_Char(NULL) Third_Party_Number,
948 To_Number(NULL) Personnel_Id,
949 To_Char(NULL) Personnel_Number,
950 Decode(Nvl(l_Project_Option, 'N'),
951 'N',
952 To_Char(NULL),
953 'COA',
954 Jcc.Project_Number,
955 To_Char(NULL)) Project_Number,
956 Nvl(l_Project_Option, 'N') Project_Source,
957 Jcc.Account_Segment Account_Segment,
958 Jel.Entered_Dr,
959 Jel.Entered_Cr,
960 Jel.Accounted_Dr,
961 Jel.Accounted_Cr,
962 'U' Status,
963 Fnd_Global.User_Id Created_Gy,
964 SYSDATE Creation_Date,
965 Fnd_Global.User_Id Last_Updated_By,
966 SYSDATE Last_Update_Date,
967 Fnd_Global.Login_Id Last_Update_Login,
968 'NO ITEMIZATION',
969 l_Request_Id,
970 Jeh.Created_By,
971 Jeb.Posted_By --added by lyb, for bug for bug 6654734
972 -- Decode(Nvl(Jeh.Accrual_Rev_Status, 'N'),
973 -- 'R',
974 -- To_Number(NULL),
975 -- Jeh.Last_Updated_By)
976 FROM Gl_Je_Headers Jeh,
977 Gl_Je_Lines Jel,
978 Ja_Cn_Code_Combination_v Jcc,
979 Ja_Cn_Journals_Of_Period Jop,
980 Gl_Je_Batches Jeb--added by lyb, for bug 6654734
981 WHERE Jeh.Je_Header_Id = Jel.Je_Header_Id
982 AND Jcc.Ledger_Id = Jeh.Ledger_Id
983 AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id
984 AND Jeh.Je_Header_Id = Jop.Je_Header_Id
985 AND Jel.Je_Line_Num = Jop.Je_Line_Num
986 AND Jop.Request_Id = l_Request_Id
987 AND Jel.Je_Line_Num NOT IN
988 (SELECT Je_Line_Num
989 FROM Ja_Cn_Journal_Lines_Req
990 WHERE Je_Header_Id = Jeh.Je_Header_Id)
991 AND Jeb.Je_Batch_Id=Jeh.Je_Batch_Id;--added by lyb, for bug 6654734
992
993 -- log for debug
994 IF (l_Proc_Level >= l_Dbg_Level) THEN
995 Fnd_Log.STRING(l_Proc_Level,
996 l_Module_Prefix || '.' || l_Proc_Name || '.end',
997 'Enter procedure');
998 END IF; --(l_proc_level >= l_dbg_level)
999 EXCEPTION
1000 WHEN OTHERS THEN
1001 ROLLBACK;
1002
1003 --log for debug
1004 IF (l_Proc_Level >= l_Dbg_Level) THEN
1005 Fnd_Log.STRING(l_Proc_Level,
1006 l_Module_Prefix || '.' || l_Proc_Name ||
1007 '. Other_Exception ',
1008 SQLCODE || ':' || SQLERRM);
1009 END IF; --(l_proc_level >= l_dbg_level)
1010 RAISE;
1011 END Unitemize_Journal_Lines;
1012
1013 --==========================================================================
1014 -- PROCEDURE NAME:
1015 -- generate_journal_and_line_num Private
1016 --
1017 -- DESCRIPTION:
1018 -- This procedure is used to generate journal number
1019 -- and journal line number based on legal entity level and period
1020 -- PARAMETERS:
1021 -- In: p_period_name period
1022 -- p_request_id identifier of current session
1023 --
1024 -- DESIGN REFERENCES:
1025 -- None
1026 --
1027 -- CHANGE HISTORY:
1028 -- 02/21/2006 Qingjun Zhao Created
1029 --===========================================================================
1030
1031 PROCEDURE Generate_Journal_Num(p_Period_Name IN VARCHAR2,
1032 p_Request_Id IN NUMBER,
1033 p_ledger_id in number,
1034 P_legal_entity_id in number) IS
1035 l_Request_Id NUMBER;
1036 l_Period_Name Gl_Periods.Period_Name%TYPE;
1037 l_Je_Header_Id NUMBER;
1038 l_Journal_Number NUMBER;
1039 l_Je_Appending_Id NUMBER;
1040 l_Line_Num_m NUMBER;
1041 l_ledger_id number;
1042 l_legal_entity_id number;
1043 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1044 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1045 l_Proc_Name VARCHAR2(100) := 'generate_journal_num';
1046
1047 CURSOR c_Journal IS
1048 SELECT Je_Header_Id
1049 FROM (SELECT DISTINCT Effective_Date, Je_Header_Id
1050 FROM Ja_Cn_Journals_Of_Period
1051 WHERE Request_Id = l_Request_Id
1052 AND Period_Name = l_Period_Name)
1053 ORDER BY Effective_Date ASC, Je_Header_Id ASC;
1054
1055 CURSOR c_Journal_Appending IS
1056 SELECT DISTINCT Je_Header_Id, Journal_Number
1057 FROM Ja_Cn_Journal_Lines Jl
1058 WHERE Je_Header_Id = l_Je_Header_Id
1059 AND Journal_Number IS NOT NULL
1060 AND Company_Segment IN
1061 (SELECT bsv.bal_seg_value
1062 FROM ja_cn_ledger_le_bsv_gt bsv
1063 WHERE Legal_Entity_Id = l_Legal_Entity_Id
1064 and ledger_id = l_ledger_id);
1065
1066 BEGIN
1067 --log for debug
1068 IF (l_Proc_Level >= l_Dbg_Level) THEN
1069 Fnd_Log.STRING(l_Proc_Level,
1070 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
1071 'Enter procedure');
1072 END IF; --(l_proc_level >= l_dbg_level)
1073
1074 l_Request_Id := p_Request_Id;
1075 l_Period_Name := p_Period_Name;
1076 l_legal_entity_id:=p_legal_entity_id;
1077 l_ledger_id:=p_ledger_id;
1078 OPEN c_Journal;
1079
1080 LOOP
1081 FETCH c_Journal
1082 INTO l_Je_Header_Id;
1083 EXIT WHEN c_Journal%NOTFOUND;
1084
1085 OPEN c_Journal_Appending;
1086 FETCH c_Journal_Appending
1087 INTO l_Je_Appending_Id, l_Journal_Number;
1088
1089 IF c_Journal_Appending%FOUND THEN
1090 CLOSE c_Journal_Appending;
1091 UPDATE Ja_Cn_Journal_Lines jop
1092 SET Journal_Number = l_Journal_Number
1093 WHERE Je_Header_Id = l_Je_Header_Id
1094 AND Journal_Number IS NULL
1095 AND Company_Segment IN
1096 (SELECT bsv.bal_seg_value
1097 FROM ja_cn_ledger_le_bsv_gt bsv
1098 WHERE Legal_Entity_Id = l_Legal_Entity_Id
1099 and ledger_id = l_ledger_id);
1100 ELSE
1101 CLOSE c_Journal_Appending;
1102 END IF; --c_journal_appending%FOUND
1103
1104 --get journal number based on legal entity and period
1105
1106 l_Journal_Number := Ja_Cn_Update_Jl_Seq_Pkg.Fetch_Jl_Seq(p_Legal_Entity_Id => l_Legal_Entity_Id,
1107 p_ledger_id=>l_ledger_id,
1108 p_Period_Name => l_Period_Name);
1109
1110 IF Nvl(l_Journal_Number, 0) > 0 THEN
1111 UPDATE Ja_Cn_Journal_Lines
1112 SET Journal_Number = l_Journal_Number
1113 WHERE Je_Header_Id = l_Je_Header_Id
1114 AND Company_Segment IN
1115 (SELECT bsv.bal_seg_value
1116 FROM ja_cn_ledger_le_bsv_gt bsv
1117 WHERE Legal_Entity_Id = l_Legal_Entity_Id
1118 and ledger_id = l_ledger_id);
1119 END IF;
1120
1121 END LOOP;
1122 CLOSE c_Journal;
1123 EXCEPTION
1124 WHEN OTHERS THEN
1125 IF (l_Proc_Level >= l_Dbg_Level) THEN
1126 Fnd_Log.STRING(l_Proc_Level,
1127 l_Module_Prefix || '.' || l_Proc_Name ||
1128 '. Other_Exception ',
1129 SQLCODE || ':' || SQLERRM);
1130 END IF; --(l_proc_level >= l_dbg_level)
1131 RAISE;
1132 END Generate_Journal_Num;
1133
1134 --==========================================================================
1135 -- PROCEDURE NAME:
1136 -- generate_journal_and_line_num Private
1137 --
1138 -- DESCRIPTION:
1139 -- This procedure is used to generate journal number
1140 -- and journal line number based on legal entity level and period
1141 -- PARAMETERS:
1142 -- In: p_period_name period
1143 -- p_request_id identifier of current session
1144 --
1145 -- DESIGN REFERENCES:
1146 -- None
1147 --
1148 -- CHANGE HISTORY:
1149 -- 02/21/2006 Qingjun Zhao Created
1150 --===========================================================================
1151
1152 PROCEDURE Itemize_Journals_Sla(p_chart_of_accounts_id in number,
1153 p_Request_Id IN NUMBER) IS
1154 l_Request_Id NUMBER;
1155 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1156 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1157 l_Proc_Name VARCHAR2(100) := 'ITEMIZE_JOURNALS_SLA';
1158 l_Project_Source_Flag VARCHAR2(15);
1159 l_Project_Ac_Code VARCHAR2(30);
1160 l_Project_Ac_Detail_Code VARCHAR2(30);
1161 l_Grouping_Order NUMBER;
1162 l_Insertsql Dbms_Sql.Varchar2s;
1163 l_Line_No NUMBER := 0;
1164 l_Chart_Of_Accounts_Id number;
1165 l_sql varchar2(4000);
1166 CURSOR c_Sub_Acc_Sources IS
1167 SELECT Sas.Project_Source_Flag,
1168 Sas.Project_Ac_Code,
1169 Sas.Project_Ac_Detail_Code,
1170 Sas.Ac_Grouping_Order--added for bug 6669665
1171 FROM Ja_Cn_Sub_Acc_Sources_All Sas
1172 WHERE Sas.Chart_Of_Accounts_Id = l_Chart_Of_Accounts_Id;
1173
1174 BEGIN
1175 --log for debug
1176 IF (l_Proc_Level >= l_Dbg_Level) THEN
1177 Fnd_Log.STRING(l_Proc_Level,
1178 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
1179 'Enter procedure');
1180 END IF; --(l_proc_level >= l_dbg_level)
1181 l_Chart_Of_Accounts_Id := p_chart_of_accounts_id;
1182 l_Request_Id := p_Request_Id;
1183
1184 OPEN c_Sub_Acc_Sources;
1185 FETCH c_Sub_Acc_Sources
1186 INTO l_Project_Source_Flag, l_Project_Ac_Code, l_Project_Ac_Detail_Code,l_Grouping_Order;--added for bug 6669665
1187 CLOSE c_Sub_Acc_Sources;
1188
1189 /* l_Line_No:=l_line_no+1;
1190 l_Insertsql:='*/
1191 if nvl(l_Project_Source_Flag, 'N') = 'PA' then
1192 --get the detail code's group order in the analytical criterion
1193
1194 --deleted for bug 6669665
1195 -- SELECT Dtl.Grouping_Order
1196 -- INTO l_Grouping_Order
1197 -- FROM Xla_Analytical_Dtls_b Dtl
1198 -- WHERE Dtl.Analytical_Criterion_Code = l_Project_Ac_Code
1199 -- AND Dtl.Analytical_Detail_Code = l_Project_Ac_Detail_Code;
1200
1201 l_sql:='INSERT INTO Ja_Cn_Journal_Lines_Req';
1202 l_sql:=l_sql||' (Je_Header_Id,';
1203 l_sql:=l_sql||' Ledger_Id,';
1204 l_sql:=l_sql||' Legal_Entity_Id,';
1205 l_sql:=l_sql||' Journal_Number,';
1206 l_sql:=l_sql||' Je_Category,';
1207 l_sql:=l_sql||' Default_Effective_Date,';
1208 l_sql:=l_sql||' Period_Name,';
1209 l_sql:=l_sql||' Currency_Code,';
1210 l_sql:=l_sql||' Currency_Conversion_Rate,';
1211 l_sql:=l_sql||' Je_Line_Num,';
1212 l_sql:=l_sql||' Line_Number,';
1213 l_sql:=l_sql||' Description,';
1214 l_sql:=l_sql||' Company_Segment,';
1215 l_sql:=l_sql||' Code_Combination_Id,';
1216 l_sql:=l_sql||' Cost_Center,';
1217 l_sql:=l_sql||' Third_Party_Id,';
1218 l_sql:=l_sql||' Third_Party_Number,';
1219 l_sql:=l_sql||' third_party_type,';
1220 l_sql:=l_sql||' Personnel_Id,';
1221 l_sql:=l_sql||' Personnel_Number,';
1222 l_sql:=l_sql||' Project_Number,';
1223 l_sql:=l_sql||' Project_Source,';
1224 l_sql:=l_sql||' Account_Segment,';
1225 l_sql:=l_sql||' Entered_Dr,';
1226 l_sql:=l_sql||' Entered_Cr,';
1227 l_sql:=l_sql||' Accounted_Dr,';
1228 l_sql:=l_sql||' Accounted_Cr,';
1229 l_sql:=l_sql||' Status,';
1230 l_sql:=l_sql||' Created_By,';
1231 l_sql:=l_sql||' Creation_Date,';
1232 l_sql:=l_sql||' Last_Updated_By,';
1233 l_sql:=l_sql||' Last_Update_Date,';
1234 l_sql:=l_sql||' Last_Update_Login,';
1235 l_sql:=l_sql||' Populate_Code,';
1236 l_sql:=l_sql||' Request_Id,';
1237 --for test
1238 -- l_sql:='';
1239 l_sql:=l_sql||' Journal_Created_By,';
1240 l_sql:=l_sql||' Journal_Posted_By)';
1241 l_sql:=l_sql||' SELECT /*+index(ael,xla_ae_lines_n4)+*/';
1242 l_sql:=l_sql||' Jel.Je_Header_Id Je_Header_Id,';
1243 l_sql:=l_sql||' Jeh.Ledger_Id Ledger_Id,';
1244 l_sql:=l_sql||' Jop.Legal_Entity_Id Legal_Entity_Id,';
1245 l_sql:=l_sql||' To_Number(NULL) Journal_Number,';
1246 l_sql:=l_sql||' Jeh.Je_Category Je_Category,';
1247 l_sql:=l_sql||' Jeh.Default_Effective_Date Default_Effective_Date,';
1248 l_sql:=l_sql||' Jeh.Period_Name Period_Name,';
1249 l_sql:=l_sql||' Jeh.Currency_Code Currency_Code,';
1250 l_sql:=l_sql||' Jeh.Currency_Conversion_Rate Currency_Conversion_Rate,';
1251 l_sql:=l_sql||' Jel.Je_Line_Num Je_Line_Num,';
1252 l_sql:=l_sql||' To_Number(NULL) Line_Number,';
1253 l_sql:=l_sql||' Nvl(Ael.Description, Nvl(Jel.Description, Jeh.Description)) Description,';
1254 l_sql:=l_sql||' Jcc.Company_Segment Company_Segment,';
1255 l_sql:=l_sql||' Jcc.Code_Combination_Id Code_Combination_Id,';
1256 l_sql:=l_sql||' Jcc.Cost_Segment Cost_Segment,';
1257 l_sql:=l_sql||' decode(pay_group_lookup_code, ';
1258 l_sql:=l_sql||'''EMPLOYEE'''||',';
1259 l_sql:=l_sql||' to_number(null),';
1260 l_sql:=l_sql||' ael.party_id) Third_Party_Id,';
1261 -- l_sql:=l_sql||' to_char(null) third_party_number,';
1262 l_sql:=l_sql||' Decode(Nvl(Ael.Party_Type_Code, ';
1263 -- l_sql:='';
1264 l_sql:=l_sql||'''D'''||'), ';
1265 l_sql:=l_sql||'''C'''||',Part.Party_Number, ';
1266 l_sql:=l_sql||'''S'''||',Sup.Segment1,To_Char(NULL)) Third_Party_Number, ';
1267 l_sql:=l_sql||' decode(pay_group_lookup_code,';
1268 l_sql:=l_sql||' ''EMPLOYEE'''||',';
1269 l_sql:=l_sql||' to_char(null),';
1270 l_sql:=l_sql||' ael.party_type_code) third_party_type,';
1271 l_sql:=l_sql||' decode(pay_group_lookup_code,';
1272 l_sql:=l_sql||'''EMPLOYEE'''||',';
1273 l_sql:=l_sql||' sup.employee_id,';
1274 l_sql:=l_sql||' to_number(null)) Personnel_Id,';
1275 l_sql:=l_sql||' to_char(null) Personnel_Number,';
1276 l_sql:=l_sql||' acs.ac'||l_grouping_order;
1277 l_sql:=l_sql||' Project_Number,'''||l_Project_Option||''' Project_Source,';
1278 l_sql:=l_sql||' Jcc.Account_Segment Account_Segment,';
1279 l_sql:=l_sql||' Ael.Entered_Dr,';
1280 l_sql:=l_sql||' Ael.Entered_Cr,';
1281 l_sql:=l_sql||' Ael.Accounted_Dr,';
1282 l_sql:=l_sql||' Ael.Accounted_Cr,';
1283 l_sql:=l_sql||'''U'''||' Status,';
1284 l_sql:=l_sql||' Fnd_Global.User_Id Created_Gy,';
1285 l_sql:=l_sql||' SYSDATE Creation_Date,';
1286 l_sql:=l_sql||' Fnd_Global.User_Id Last_Updated_By,';
1287 l_sql:=l_sql||' SYSDATE Last_Update_Date,';
1288 l_sql:=l_sql||' Fnd_Global.Login_Id Last_Update_Login,';
1289 l_sql:=l_sql||'''FSAH'''||',';
1290 l_sql:=l_sql||l_Request_Id||',';
1291 l_sql:=l_sql||' Jeh.Created_By,';
1292 l_sql:=l_sql||' jeb.posted_by';
1293 /* for bug 6654734
1294 l_sql:=l_sql||' Decode(Nvl(Jeh.Accrual_Rev_Status, '||'''N'''||'),';
1295 l_sql:=l_sql||'''R'''||',';
1296 ---for test
1297 -- l_sql:='';
1298
1299 l_sql:=l_sql||' To_Number(NULL),';
1300 l_sql:=l_sql||' Jeh.Last_Updated_By)';*/
1301 l_sql:=l_sql||' FROM Gl_Je_Lines Jel,';
1302 l_sql:=l_sql||' Gl_Je_Headers Jeh,';
1303 l_sql:=l_sql||' Gl_Je_Batches Jeb,';
1304 l_sql:=l_sql||' Xla_Ae_Lines Ael,';
1305 l_sql:=l_sql||' Xla_Ae_Headers Aeh,';
1306 l_sql:=l_sql||' Gl_Import_References Gir,';
1307 l_sql:=l_sql||' Ja_Cn_Code_Combination_v Jcc,';
1308 l_sql:=l_sql||' xla_ae_line_acs acs,';
1309 l_sql:=l_sql||' ap_suppliers sup,';
1310 l_sql:=l_sql||' Ja_Cn_Journals_Of_Period Jop,';
1311 l_sql:=l_sql||' Hz_Cust_Accounts Cust,';
1312 l_sql:=l_sql||' Hz_Parties Part ';
1313 l_sql:=l_sql||' WHERE Jel.Je_Header_Id = Jeh.Je_Header_Id';
1314 l_sql:=l_sql||' AND Jeb.Je_Batch_Id=jeh.je_batch_id ';
1315 l_sql:=l_sql||' AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id';
1316 l_sql:=l_sql||' AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table';
1317 l_sql:=l_sql||' AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id';
1318 l_sql:=l_sql||' AND Gir.Je_Header_Id = Jeh.Je_Header_Id';
1319 l_sql:=l_sql||' and sup.vendor_id(+) = ael.party_id';
1320 l_sql:=l_sql||' AND Gir.Je_Line_Num = Jel.Je_Line_Num';
1321 --for test
1322 -- l_sql:='';
1323 l_sql:=l_sql||' AND Jop.Je_Header_Id = Jel.Je_Header_Id';
1324 l_sql:=l_sql||' AND Jop.Je_Line_Num = Jel.Je_Line_Num';
1325 l_sql:=l_sql||' AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id';
1326 l_sql:=l_sql||' and jop.request_id = '||l_request_id;
1327 l_sql:=l_sql||' AND Acs.Ae_Header_Id(+) = Ael.Ae_Header_Id';
1328 l_sql:=l_sql||' and jcc.ledger_id=jeh.ledger_id ';
1329 l_sql:=l_sql||' AND Acs.Ae_Line_Num(+) = Ael.Ae_Line_Num ';
1330 l_sql:=l_sql||' and cust.cust_account_id(+)=ael.party_id ';
1331 l_sql:=l_sql||' and cust.party_id=part.party_id(+) ';
1332 l_sql:=l_sql||' AND Acs.analytical_criterion_code(+)='''||l_Project_Ac_Code||''' ';
1333
1334 execute immediate l_sql;
1335 else
1336 INSERT INTO Ja_Cn_Journal_Lines_Req
1337 (Je_Header_Id,
1338 Ledger_Id,
1339 Legal_Entity_Id,
1340 Journal_Number,
1341 Je_Category,
1342 Default_Effective_Date,
1343 Period_Name,
1344 Currency_Code,
1345 Currency_Conversion_Rate,
1346 Je_Line_Num,
1347 Line_Number,
1348 Description,
1349 Company_Segment,
1350 Code_Combination_Id,
1351 Cost_Center,
1352 Third_Party_Id,
1353 Third_Party_Type,
1354 Third_Party_Number,
1355 Personnel_Id,
1356 Personnel_Number,
1357 Project_Number,
1358 Project_Source,
1359 Account_Segment,
1360 Entered_Dr,
1361 Entered_Cr,
1362 Accounted_Dr,
1363 Accounted_Cr,
1364 Status,
1365 Created_By,
1366 Creation_Date,
1367 Last_Updated_By,
1368 Last_Update_Date,
1369 Last_Update_Login,
1370 Populate_Code,
1371 Request_Id,
1372 Journal_Created_By,
1373 Journal_Posted_By)
1374 SELECT /*+index(ael,xla_ae_lines_n4)+*/
1375 Jel.Je_Header_Id Je_Header_Id
1376 ,Jeh.Ledger_Id Ledger_Id
1377 ,Jop.Legal_Entity_Id Legal_Entity_Id
1378 ,To_Number(NULL) Journal_Number
1379 ,Jeh.Je_Category Je_Category
1380 ,Jeh.Default_Effective_Date Default_Effective_Date
1381 ,Jeh.Period_Name Period_Name
1382 ,Jeh.Currency_Code Currency_Code
1383 ,Jeh.Currency_Conversion_Rate Currency_Conversion_Rate
1384 ,Jel.Je_Line_Num Je_Line_Num
1385 ,To_Number(NULL) Line_Number
1386 ,Nvl(Ael.Description,
1387 Nvl(Jel.Description,
1388 Jeh.Description)) Description
1389 ,Jcc.Company_Segment Company_Segment
1390 ,Jcc.Code_Combination_Id Code_Combination_Id
1391 ,Jcc.Cost_Segment Cost_Segment
1392 ,Decode(Pay_Group_Lookup_Code,
1393 'EMPLOYEE',
1394 To_Number(NULL),
1395 Ael.Party_Id) Third_Party_Id
1396 ,Decode(Pay_Group_Lookup_Code,
1397 'EMPLOYEE',
1398 To_Char(NULL),
1399 Ael.Party_Type_Code) Third_Party_Type
1400 ,Decode(Nvl(Ael.Party_Type_Code,
1401 'D'),
1402 'C',
1403 Part.Party_Number,
1404 'S',
1405 Sup.Segment1,
1406 To_Char(NULL)) Third_Party_Number
1407 ,Decode(Pay_Group_Lookup_Code,
1408 'EMPLOYEE',
1409 Sup.Employee_Id,
1410 To_Number(NULL)) Personnel_Id
1411 ,To_Char(NULL) Personnel_Number
1412 ,Decode(Nvl(l_Project_Option,
1413 'N'),
1414 'N',
1415 To_Char(NULL),
1416 'COA',
1417 Jcc.Project_Number,
1418 To_Char(NULL)) Project_Number
1419 ,Nvl(l_Project_Option,
1420 'N') Project_Source
1421 ,Jcc.Account_Segment Account_Segment
1422 ,Ael.Entered_Dr
1423 ,Ael.Entered_Cr
1424 ,Ael.Accounted_Dr
1425 ,Ael.Accounted_Cr
1426 ,'U' Status
1427 ,Fnd_Global.User_Id Created_Gy
1428 ,SYSDATE Creation_Date
1429 ,Fnd_Global.User_Id Last_Updated_By
1430 ,SYSDATE Last_Update_Date
1431 ,Fnd_Global.Login_Id Last_Update_Login
1432 ,'FSAH'
1433 ,l_Request_Id
1434 ,Jeh.Created_By
1435 ,jeb.posted_by
1436 ----deleted by lyb, for bug 6654734
1437 -- ,Decode(Nvl(Jeh.Accrual_Rev_Status,
1438 -- 'N'),
1439 -- 'R',
1440 -- To_Number(NULL),
1441 -- Jeh.Last_Updated_By)
1442 FROM Gl_Je_Lines Jel
1443 ,Gl_Je_Headers Jeh
1444 ,Gl_Je_Batches Jeb --added by lyb, for bug 6654734
1445 ,Xla_Ae_Lines Ael
1446 ,Xla_Ae_Headers Aeh
1447 ,Gl_Import_References Gir
1448 ,Ja_Cn_Code_Combination_v Jcc
1449 ,Ap_Suppliers Sup
1450 ,
1451 -- per_all_people_f per,
1452 Ja_Cn_Journals_Of_Period Jop
1453 ,Hz_Cust_Accounts Cust
1454 ,Hz_Parties Part
1455 WHERE Jel.Je_Header_Id = Jeh.Je_Header_Id
1456 AND Jeb.Je_Batch_Id=jeh.je_batch_id --added by lyb, for bug 6654734
1457 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
1458 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
1459 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
1460 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
1461 AND Sup.Vendor_Id(+) = Ael.Party_Id
1462 -- and sup.pay_group_lookup_code='EMPLOYEE'
1463 -- AND nvl(pv.employee_id, -1) = per.person_id(+)
1464 AND Gir.Je_Line_Num = Jel.Je_Line_Num
1465 AND Jop.Je_Header_Id = Jel.Je_Header_Id
1466 AND Jop.Je_Line_Num = Jel.Je_Line_Num
1467 AND Jcc.Ledger_Id = Jeh.Ledger_Id
1468 AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id
1469 AND Jop.Request_Id = l_Request_Id
1470 AND Cust.Cust_Account_Id(+) = Ael.Party_Id
1471 AND Cust.Party_Id = Part.Party_Id(+);
1472
1473 end if;
1474
1475 EXCEPTION
1476 WHEN OTHERS THEN
1477 IF (l_Proc_Level >= l_Dbg_Level) THEN
1478 Fnd_Log.STRING(l_Proc_Level,
1479 l_Module_Prefix || '.' || l_Proc_Name ||
1480 '. Other_Exception ',
1481 SQLCODE || ':' || SQLERRM);
1482 END IF; --(l_proc_level >= l_dbg_level)
1483 RAISE;
1484 END Itemize_Journals_Sla;
1485
1486 --=========================================================================
1487 -- PROCEDURE NAME:
1488 -- transfer_gl_sla_to_cnao Public
1489 --
1490 -- DESCRIPTION:
1491 -- This is main procedure through which other procedures are called
1492 -- according to source and category of journal.Then call generate
1493 -- journal number and journal line number procedure and call post
1494 -- program
1495 -- PARAMETERS:
1496 -- Out: errbuf Mandatory parameter for PL/SQL concurrent programs
1497 -- Out: retcode Mandatory parameter for PL/SQL concurrent programs
1498 -- In: p_period_name Accounting period name
1499 -- In: p_legal_entity_ID Legal entity id
1500 --
1501 -- DESIGN REFERENCES:
1502 -- None
1503 --
1504 -- CHANGE HISTORY:
1505 -- 02/21/2006 Qingjun Zhao Created
1506 --===========================================================================
1507
1508 PROCEDURE Transfer_Gl_Sla_To_Cnao(Errbuf OUT NOCOPY VARCHAR2,
1509 Retcode OUT NOCOPY VARCHAR2,
1510 p_Chart_Of_Accounts_Id IN NUMBER,
1511 p_Ledger_Id IN NUMBER,
1512 p_Legal_Entity_Id IN NUMBER,
1513 p_Period_Name IN VARCHAR2) IS
1514
1515 l_Error_Msg VARCHAR2(2000);
1516 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1517 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1518 l_Proc_Name VARCHAR2(100) := 'transfer_gl_sla_to_cnao';
1519 l_Phase VARCHAR2(100);
1520 l_Status VARCHAR2(100);
1521 l_Dev_Phase VARCHAR2(100);
1522 l_Dev_Status VARCHAR2(100);
1523 l_Message VARCHAR2(100);
1524 l_Till_Period_Name Gl_Periods.Period_Name%TYPE;
1525 l_Period_Name Gl_Periods.Period_Name%TYPE;
1526 l_Start_Period_Name Gl_Periods.Period_Name%TYPE;
1527 l_End_Period_Name Gl_Periods.Period_Name%TYPE;
1528 l_Conc_Succ BOOLEAN;
1529 l_Request_Id NUMBER;
1530 l_Chart_Of_Account_Id NUMBER;
1531 l_Ledger_Id NUMBER;
1532 l_Result BOOLEAN;
1533 l_Submit_Fail_Module VARCHAR2(100);
1534 l_Execution_Fail_Module VARCHAR2(100);
1535 l_Post_Con_Req_Id NUMBER := 0;
1536 l_Phase_Code Fnd_Lookup_Values.Lookup_Code%TYPE;
1537 l_Status_Code Fnd_Lookup_Values.Lookup_Code%TYPE;
1538 l_Request_Submit_Fail EXCEPTION;
1539 l_Request_Execution_Fail EXCEPTION;
1540 l_Post_Fail EXCEPTION;
1541
1542 CURSOR c_Project_Option IS
1543 SELECT Project_Source_Flag
1544 FROM Ja_Cn_Sub_Acc_Sources_All
1545 WHERE Chart_Of_Accounts_Id = l_Chart_Of_Accounts_Id;
1546
1547 CURSOR c_Period_Name IS
1548 SELECT Gp.Period_Name
1549 FROM Gl_Periods Gp, Gl_Ledgers Led
1550 WHERE Led.Ledger_Id = l_Ledger_Id
1551 AND Led.Period_Set_Name = Gp.Period_Set_Name
1552 AND Led.Accounted_Period_Type = Gp.Period_Type
1553 AND Gp.Start_Date BETWEEN
1554 (SELECT Start_Date
1555 FROM Gl_Periods Gp
1556 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
1557 AND Led.Accounted_Period_Type = Gp.Period_Type
1558 AND Gp.Period_Name = l_Start_Period_Name)
1559 AND (SELECT Start_Date
1560 FROM Gl_Periods Gp
1561 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
1562 AND Led.Accounted_Period_Type = Gp.Period_Type
1563 AND Gp.Period_Name = l_End_Period_Name)
1564 ORDER BY Gp.Start_Date;
1565 BEGIN
1566
1567 --log for debug
1568 IF (l_Proc_Level >= l_Dbg_Level) THEN
1569 Fnd_Log.STRING(l_Proc_Level,
1570 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
1571 'Enter procedure');
1572 Fnd_Log.STRING(l_Proc_Level,
1573 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
1574 'p_period_name:' || p_Period_Name);
1575 Fnd_Log.STRING(l_Proc_Level,
1576 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
1577 'p_legal_entity_id:' || p_Legal_Entity_Id);
1578 END IF; --(l_proc_level >= l_dbg_level)
1579
1580 --call JA_CN_UTILITY.Check_Profile, if it doesn't return true, exit
1581 /* IF Ja_Cn_Utility.Check_Profile() <> TRUE THEN
1582
1583 --log for debug
1584 IF (l_Proc_Level >= l_Dbg_Level) THEN
1585 Fnd_Log.STRING(l_Proc_Level, l_Dbg_Level, 'Check profile failed!');
1586 END IF; --(l_proc_level >= l_dbg_level)
1587 l_Conc_Succ := Fnd_Concurrent.Set_Completion_Status(Status => 'WARNING',
1588 Message => '');
1589 RETURN;
1590 END IF; */ --JA_CN_UTILITY.Check_Profile() != TRUE
1591
1592 l_Till_Period_Name := p_Period_Name;
1593 l_Legal_Entity_Id := p_Legal_Entity_Id;
1594 l_ledger_id := p_Ledger_Id;
1595
1596 --log for debug
1597 IF (l_Proc_Level >= l_Dbg_Level) THEN
1598 Fnd_Log.STRING(l_Proc_Level,
1599 l_Module_Prefix || '.' || l_Proc_Name ||
1600 '.current set of books id',
1601 'set of book id is ' || l_Ledger_Id);
1602 END IF; --l_exception_level >= l_runtime_level
1603
1604 l_chart_of_accounts_id := p_chart_of_accounts_id;
1605 --Get "Project" definition in global_attribute1 of led
1606 OPEN c_Project_Option;
1607 FETCH c_Project_Option
1608 INTO l_Project_Option;
1609
1610 --if "Project" isn't defined,then consider "Project"
1611 --as "Project Not considered"--'N'
1612 IF (c_Project_Option%NOTFOUND) THEN
1613 l_Project_Option := 'N';
1614 END IF; --(c_project_option%NOTFOUND)
1615
1616 --log for debug
1617 IF (l_Proc_Level >= l_Dbg_Level) THEN
1618 Fnd_Log.STRING(l_Proc_Level,
1619 l_Module_Prefix || '.' || l_Proc_Name ||
1620 '.setup information',
1621 'project option is ' || l_Project_Option);
1622 END IF; --l_exception_level >= l_runtime_level
1623
1624 CLOSE c_Project_Option;
1625
1626 --generate current session identifier
1627 SELECT Ja_Cn_Journal_Lines_Req_s.NEXTVAL INTO l_Request_Id FROM Dual;
1628
1629 --generate code combination view
1630 Generate_Code_Combination_View(p_ledger_id => l_ledger_id);
1631
1632 --Get effective begin period and end period;
1633 Get_Period_Range(p_Period_Name => l_Till_Period_Name,
1634 p_ledger_id => l_ledger_id,
1635 p_Start_Period_Name => l_Start_Period_Name,
1636 p_End_Period_Name => l_End_Period_Name);
1637
1638 --Populate journal lines which will be itemized by possible subsidiary
1639 -- between start period and end period
1640
1641 Populate_Journal_Of_Period(p_Start_Period => l_Start_Period_Name,
1642 p_ledger_id => l_ledger_id,
1643 p_legal_entity_id => l_legal_entity_id,
1644 p_End_Period => l_End_Period_Name,
1645 p_Request_Id => l_Request_Id);
1646
1647 Itemize_Journals_Sla(p_chart_of_accounts_id => l_chart_of_accounts_id,
1648 p_request_id => l_request_id);
1649 Purge_Unmatch_Lines(p_Request_Id => l_Request_Id);
1650
1651 --Transfer directly journal lines, which cannt be itmized by above
1652 --Itemization concurrent programs, into CNAO system
1653 Unitemize_Journal_Lines(p_Request_Id => l_Request_Id,
1654 p_Project_Option => l_Project_Option);
1655 --get approver of journals that have been approved in General Ledger
1656 Get_Journal_Approver(p_Request_Id => l_Request_Id);
1657
1658 --get creator's name of journal
1659 UPDATE JA_CN_JOURNAL_LINES_REQ REQ
1660 SET REQ.JOURNAL_CREATOR = (SELECT LAST_NAME || FIRST_NAME FULL_NAME
1661 FROM PER_ALL_PEOPLE_F
1662 WHERE PERSON_ID =
1663 (SELECT EMPLOYEE_ID
1664 FROM FND_USER
1665 WHERE USER_ID =
1666 REQ.JOURNAL_CREATED_BY)
1667 AND REQ.DEFAULT_EFFECTIVE_DATE BETWEEN
1668 EFFECTIVE_START_DATE AND
1669 EFFECTIVE_END_DATE)
1670 WHERE REQ.REQUEST_ID = L_REQUEST_ID
1671 AND REQ.JE_HEADER_ID > 0;
1672 --get poster's name of journals
1673 UPDATE JA_CN_JOURNAL_LINES_REQ REQ
1674 SET REQ.JOURNAL_POSTER = (SELECT LAST_NAME || FIRST_NAME FULL_NAME
1675 FROM PER_ALL_PEOPLE_F
1676 WHERE PERSON_ID =
1677 (SELECT EMPLOYEE_ID
1678 FROM FND_USER
1679 WHERE USER_ID =
1680 REQ.JOURNAL_POSTED_BY)
1681 AND REQ.DEFAULT_EFFECTIVE_DATE BETWEEN
1682 EFFECTIVE_START_DATE AND
1683 EFFECTIVE_END_DATE)
1684 WHERE REQ.REQUEST_ID = L_REQUEST_ID
1685 AND REQ.JE_HEADER_ID > 0;
1686 COMMIT;
1687
1688 --update journal line into status of itemizated
1689 UPDATE Gl_Je_Lines Jel
1690 SET Jel.Global_Attribute2 = 'P'
1691 WHERE Jel.Je_Line_Num IN
1692 (SELECT Je_Line_Num
1693 FROM Ja_Cn_Journals_Of_Period
1694 WHERE Request_Id = l_Request_Id
1695 AND Je_Header_Id = Jel.Je_Header_Id)
1696 AND Jel.Je_Header_Id IN
1697 (SELECT Je_Header_Id
1698 FROM Ja_Cn_Journals_Of_Period
1699 WHERE Request_Id = l_Request_Id);
1700
1701 --transfer itemized data into ja_cn_journal_lines in this session
1702 INSERT INTO Ja_Cn_Journal_Lines
1703 (Je_Header_Id,
1704 Ledger_Id,
1705 Legal_Entity_Id,
1706 Journal_Number,
1707 Je_Category,
1708 Default_Effective_Date,
1709 Period_Name,
1710 Currency_Code,
1711 Currency_Conversion_Rate,
1712 Je_Line_Num,
1713 Line_Number,
1714 Description,
1715 Company_Segment,
1716 Code_Combination_Id,
1717 Cost_Center,
1718 Third_Party_Id,
1719 Third_Party_Number,
1720 Third_Party_Type,
1721 Personnel_Id,
1722 Personnel_Number,
1723 Project_Number,
1724 Project_Source,
1725 Account_Segment,
1726 Entered_Dr,
1727 Entered_Cr,
1728 Accounted_Dr,
1729 Accounted_Cr,
1730 Status,
1731 Created_By,
1732 Creation_Date,
1733 Last_Updated_By,
1734 Last_Update_Date,
1735 Last_Update_Login,
1736 Populate_Code,
1737 Journal_Creator,
1738 Journal_Approver,
1739 Journal_Poster)
1740 SELECT Je_Header_Id,
1741 Ledger_Id,
1742 Legal_Entity_Id,
1743 Journal_Number,
1744 Je_Category,
1745 Default_Effective_Date,
1746 Period_Name,
1747 Currency_Code,
1748 Currency_Conversion_Rate,
1749 Je_Line_Num,
1750 Line_Number,
1751 Description,
1752 Company_Segment,
1753 Code_Combination_Id,
1754 Cost_Center,
1755 Third_Party_Id,
1756 Third_Party_Number,
1757 Third_Party_Type,
1758 Personnel_Id,
1759 Personnel_Number,
1760 Project_Number,
1761 Project_Source,
1762 Account_Segment,
1763 Entered_Dr,
1764 Entered_Cr,
1765 Accounted_Dr,
1766 Accounted_Cr,
1767 Status,
1768 Created_By,
1769 Creation_Date,
1770 Last_Updated_By,
1771 Last_Update_Date,
1772 Last_Update_Login,
1773 Populate_Code,
1774 Journal_Creator,
1775 Journal_Approver,
1776 Journal_Poster
1777 FROM Ja_Cn_Journal_Lines_Req
1778 WHERE Request_Id = l_Request_Id
1779 AND Je_Header_Id > 0;
1780
1781 --get journal approver for itemized journal lines
1782 --which journal source need been approved
1783
1784 --generate journal number and journal line number
1785 --based on legal entity level
1786 OPEN c_Period_Name;
1787
1788 LOOP
1789 FETCH c_Period_Name
1790 INTO l_Period_Name;
1791 EXIT WHEN c_Period_Name%NOTFOUND;
1792 -- generate journal number and journal line number
1793 Generate_Journal_Num(p_Period_Name => l_Period_Name,
1794 p_Request_Id => l_Request_Id,
1795 p_ledger_id =>p_ledger_id,
1796 p_legal_entity_id =>p_legal_entity_id);
1797
1798 END LOOP;
1799
1800 --commit itemized journal lines with journal number and journal line number
1801 COMMIT;
1802
1803 --Delete temparory data of current session
1804 /* DELETE FROM Ja_Cn_Journals_Of_Period
1805 WHERE Request_Id = l_Request_Id
1806 AND Je_Header_Id > 0;*/
1807
1808 DELETE FROM Ja_Cn_Journal_Lines_Req
1809 WHERE Request_Id = l_Request_Id
1810 AND Je_Header_Id > 0;
1811 COMMIT;
1812 Fnd_File.Put_Line(Fnd_File.Log, 'l_period_name:' || l_Period_Name);
1813 Fnd_File.Put_Line(Fnd_File.Log, 'l_ledger_id:' || l_Ledger_Id);
1814 Fnd_File.Put_Line(Fnd_File.Log,
1815 'l_legal_entity_id:' || l_Legal_Entity_Id);
1816 --call post program to post these journals itemized
1817 l_Post_Con_Req_Id := Fnd_Request.Submit_Request('JA',
1818 'JACNPOST',
1819 NULL,
1820 To_Date(NULL),
1821 FALSE,
1822 l_Period_Name,
1823 l_Ledger_Id,
1824 l_Legal_Entity_Id);
1825
1826 IF (l_Post_Con_Req_Id = 0) THEN
1827 Errbuf := Fnd_Message.Get;
1828 Retcode := 2;
1829 l_Submit_Fail_Module := 'General Legder';
1830 RAISE l_Post_Fail;
1831 END IF; --(l_gl_con_req_id = 0)
1832 --submit post request
1833 COMMIT;
1834 IF l_Post_Con_Req_Id <> 0 THEN
1835 l_Result := Fnd_Concurrent.Wait_For_Request(l_Post_Con_Req_Id,
1836 60,
1837 -1,
1838 l_Phase,
1839 l_Status,
1840 l_Dev_Phase,
1841 l_Dev_Status,
1842 l_Message);
1843
1844 IF l_Result = FALSE THEN
1845 Errbuf := Fnd_Message.Get;
1846 Retcode := 2;
1847 l_Execution_Fail_Module := 'Post';
1848
1849 --log for debug
1850 IF (l_Proc_Level >= l_Dbg_Level) THEN
1851 Fnd_Log.STRING(l_Proc_Level,
1852 l_Module_Prefix || '.' || l_Proc_Name ||
1853 '.JACNGLJT.EXECUTION',
1854 'l_status.' || l_Status || '--' || 'l_phase.' ||
1855 l_Phase);
1856 END IF; --l_exception_level >= l_runtime_level
1857
1858 END IF; --l_result = FALSE
1859
1860 END IF; --l_post_con_req_id <> 0
1861
1862 --log for debug
1863 IF (l_Proc_Level >= l_Dbg_Level) THEN
1864 Fnd_Log.STRING(l_Proc_Level,
1865 l_Module_Prefix || '.' || l_Proc_Name || '.end',
1866 'Exit procedure');
1867 END IF; --( l_proc_level >= l_dbg_level )
1868
1869 EXCEPTION
1870 WHEN l_Post_Fail THEN
1871 -- dbms_output.put_line('Post Program fails,please connect your system ');
1872 --log for debug
1873 IF (l_Proc_Level >= l_Dbg_Level) THEN
1874 Fnd_Log.STRING(l_Proc_Level,
1875 l_Module_Prefix || '.' || l_Proc_Name ||
1876 '. Other_Exception ',
1877 SQLCODE || ':' || SQLERRM);
1878 END IF; --(l_proc_level >= l_dbg_level)
1879 l_Conc_Succ := Fnd_Concurrent.Set_Completion_Status(Status => 'ERROR',
1880 Message => SQLCODE || ':' ||
1881 SQLERRM);
1882 WHEN OTHERS THEN
1883 ROLLBACK;
1884 DELETE FROM Ja_Cn_Journals_Of_Period
1885 WHERE Request_Id = l_Request_Id
1886 AND Je_Header_Id > 0;
1887
1888 DELETE FROM Ja_Cn_Journal_Lines_Req
1889 WHERE Request_Id = l_Request_Id
1890 AND Je_Header_Id > 0;
1891 COMMIT;
1892 Fnd_File.Put_Line(Fnd_File.Log, SQLCODE || ':' || SQLERRM);
1893 l_Conc_Succ := Fnd_Concurrent.Set_Completion_Status(Status => 'ERROR',
1894 Message => SQLCODE || ':' ||
1895 SQLERRM);
1896 --log for debug
1897 IF (l_Proc_Level >= l_Dbg_Level) THEN
1898 Fnd_Log.STRING(l_Proc_Level,
1899 l_Module_Prefix || '.' || l_Proc_Name ||
1900 '. Other_Exception ',
1901 SQLCODE || ':' || SQLERRM);
1902 END IF; --(l_proc_level >= l_dbg_level)
1903
1904 END Transfer_Gl_Sla_To_Cnao;
1905
1906 END Ja_Cn_Acc_Je_Itemization_Pkg;
1907
1908
1909
1910