[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.20 2011/04/15 05:37:49 jiachi ship $
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 --| 07/09/2009 Chaoqun Wu Fixing bug#8670470 |
35 --| 10/10/2009 Chaoqun Wu Fix bug 8970684, "CHART OF ACCOUNTS |
36 --| EXPORT - SUBSIDIARY ACCOUNT - |
37 --| PERSONNEL" IS NULL |
38 --| 01/20/2010 Chaoqun Wu Updated for CNAOV2 solution |
39 --| 05/24/2010 Chaoqun Wu Fixed bug 9590798, exchange rate not |
40 --| be populated correctly |
41 --| 07/14/2010 Chaoqun Wu Fixed bug 9872212, exchange rate type|
42 --| should be popluated from SLA table |
43 --| 08/03/2010 Chaoqun Wu Fixed bug 9962344, consider 'Employee'|
44 --| as a new subsidiary conext for |
45 --| subsidiary source SLA. |
46 --| 01/12/2010 Chaoqun/ChuanLing Fixed bug 10316599 , add a condition |
47 --| 'ACTUAL_FLAG <> B 'to filter budget |
48 --| journals when these information has |
49 --| been exported in journal section of GL|
50 --| 01/11/2011 Chongwu Li CNAO V1 to V2 Upgrade |
51 --| 15/04/2011 Jianchao Chi Updated for Zero Downtime Patching, |
52 --| bug 12363301 |
53 --+========================================================================
54 l_Module_Prefix VARCHAR2(100) := 'JA_CN_ACC_JE_ITEMIZATION_PKG';
55 -- l_Ledger_Id NUMBER;
56 l_Chart_Of_Accounts_Id NUMBER;
57 l_Legal_Entity_Id NUMBER;
58 l_ledger_id number;
59 l_Project_Option VARCHAR2(240);
60 PROCEDURE Test_Concurrent_Conflict IS
61 l_Message VARCHAR2(1000) := 'This concurrent is used to test both concurrents call procedure in same package';
62 BEGIN
63 Fnd_File.Put_Line(Fnd_File.Log, l_Message);
64 END;
65 --==========================================================================
66 -- PROCEDURE NAME:
67 -- get_journal_approver Private
68 --
69 -- DESCRIPTION:
70 -- This procedure is used to get approver of journal in General Ledger
71 -- if the journal has been appoved
72 -- PARAMETERS:
73 -- In: p_request_id identifier of current session
74 --
75 --
76 -- DESIGN REFERENCES:
77 -- None
78 --
79 -- CHANGE HISTORY:
80 -- 05/16/2006 Qingjun Zhao Created
81 -- 04/29/2006 Qingjun Zhao Change for
82 --==========================================================================
83 PROCEDURE Get_Journal_Approver(p_Request_Id IN NUMBER) IS
84 l_Request_Id NUMBER := p_Request_Id;
85 l_Je_Header_Id NUMBER;
86 l_Batch_Name Gl_Je_Batches.NAME%TYPE;
87 l_Period_Name Gl_Periods.Period_Name%TYPE;
88 l_Approver VARCHAR2(100);
89 l_Approval_Status VARCHAR2(1);
90 l_Approver_Name VARCHAR2(100);
91 l_Step VARCHAR2(100);
92 l_Proc_Name VARCHAR2(30) := 'get_journal_approver';
93 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
94 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
95 l_Statement_Level NUMBER := Fnd_Log.Level_Statement;
96 l_Exception_Level NUMBER := Fnd_Log.Level_Exception;
97 CURSOR c_Journal IS
98 SELECT DISTINCT Jop.Je_Header_Id
99 FROM Ja_Cn_Journals_Of_Period Jop, Gl_Je_Sources_Tl Gjs
100 WHERE Jop.Request_Id = l_Request_Id
101 AND Gjs.Je_Source_Name = Jop.Je_Source
102 AND Gjs.Source_Lang = Userenv('LANG')
103 AND Gjs.LANGUAGE = Userenv('LANG')
104 AND Gjs.Journal_Approval_Flag = 'Y';
105 CURSOR c_Batch_Status IS
106 SELECT Jeb.Default_Period_Name, Jeb.NAME, Jeb.Approval_Status_Code
107 FROM Gl_Je_Headers Jeh, Gl_Je_Batches Jeb
108 WHERE Jeb.Je_Batch_Id = Jeh.Je_Batch_Id
109 AND Jeh.Je_Header_Id = l_Je_Header_Id;
110
111 CURSOR c_Batch_Approver IS
112 SELECT d.Text_Value
113 FROM Wf_Items t, Wf_Item_Attribute_Values d
114 WHERE d.Item_Key = t.Item_Key
115 AND d.NAME = 'APPROVER_NAME'
116 AND t.User_Key = l_Batch_Name
117 AND d.Item_Type = 'GLBATCH'
118 AND t.Begin_Date IN
119 (SELECT MAX(It.Begin_Date)
120 FROM Wf_Items It,
121 Wf_Item_Attribute_Values T1,
122 Wf_Item_Attribute_Values t
123 WHERE It.User_Key = l_Batch_Name
124 AND It.Item_Key = t.Item_Key
125 AND T1.Item_Type = 'GLBATCH'
126 AND T1.Item_Key = t.Item_Key
127 AND t.Item_Type = 'GLBATCH'
128 AND t.NAME = 'BATCH_NAME'
129 AND t.Text_Value = l_Batch_Name
130 AND T1.NAME = 'PERIOD_NAME'
131 AND T1.Text_Value = l_Period_Name);
132 -- group by it.item_key);
133
134 CURSOR c_Approver_Name IS
135 SELECT Last_Name || First_Name Full_Name
136 FROM Per_All_People_f
137 WHERE Person_Id =
138 (SELECT Employee_Id FROM Fnd_User WHERE User_Name = l_Approver);
139
140 BEGIN
141 --log
142 IF (l_Proc_Level >= l_Dbg_Level) THEN
143 Fnd_Log.STRING(l_Proc_Level,
144 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
145 'begin procedure');
146
147 END IF; --l_procedure_level >= l_runtime_level
148
149 OPEN c_Journal;
150 LOOP
151 FETCH c_Journal
152 INTO l_Je_Header_Id;
153 EXIT WHEN c_Journal%NOTFOUND;
154 --get batch name and period name
155 l_Step := To_Char(l_Je_Header_Id);
156 OPEN c_Batch_Status;
157 FETCH c_Batch_Status
158 INTO l_Period_Name, l_Batch_Name, l_Approval_Status;
159 l_Step := l_Period_Name || l_Batch_Name || l_Approval_Status;
160
161 IF Nvl(l_Approval_Status, 'N') = 'A' THEN
162 -- get approver
163 OPEN c_Batch_Approver;
164 FETCH c_Batch_Approver
165 INTO l_Approver;
166 l_Step := l_Approver;
167
168 IF c_Batch_Approver%FOUND THEN
169
170 OPEN c_Approver_Name;
171 FETCH c_Approver_Name
172 INTO l_Approver_Name;
173
174 IF c_Approver_Name%FOUND THEN
175 UPDATE Ja_Cn_Journal_Lines_Req t
176 SET t.Journal_Approver = l_Approver_Name
177 WHERE t.Je_Header_Id = l_Je_Header_Id;
178 END IF; --c_approver_name%found
179
180 CLOSE c_Approver_Name;
181 END IF; --c_batch_approver%found
182
183 CLOSE c_Batch_Approver;
184 END IF; --nvl(l_approval_status,'N') = 'A'
185 CLOSE c_Batch_Status;
186 END LOOP; --c_journal
187
188 CLOSE c_Journal;
189 -- log for debug
190 IF (l_Proc_Level >= l_Dbg_Level) THEN
191 Fnd_Log.STRING(l_Proc_Level,
192 l_Module_Prefix || '.' || l_Proc_Name || '.end',
193 'Enter procedure');
194 END IF; --(l_proc_level >= l_dbg_level)
195 EXCEPTION
196 WHEN OTHERS THEN
197 Fnd_File.Put_Line(Fnd_File.Log, 'l_step:' || l_Step);
198 --log for debug
199 IF (l_Proc_Level >= l_Dbg_Level) THEN
200 Fnd_Log.STRING(l_Proc_Level,
201 l_Module_Prefix || '.' || l_Proc_Name ||
202 '. Other_Exception ',
203 SQLCODE || ':' || SQLERRM);
204 END IF; --(l_proc_level >= l_dbg_level)
205 RAISE;
206 END Get_Journal_Approver;
207 --==========================================================================
208 -- FUNCTION NAME:
209 -- get_lookup_code Private
210 --
211 -- DESCRIPTION:
212 -- This function is used to get lookup code of lookup meaning,
213 -- PARAMETERS:
214 -- In: p_lookup_meaning lookup meaning
215 -- p_lookup_type lookup code
216 -- p_view_application_id view application, DEFAULT 0
217 -- p_security_group_id security group
218 --
219 --
220 -- DESIGN REFERENCES:
221 -- None
222 --
223 -- CHANGE HISTORY:
224 -- 02/21/2006 Qingjun Zhao Created
225 --==========================================================================
226 FUNCTION Get_Lookup_Code(p_Lookup_Meaning IN VARCHAR2,
227 p_Lookup_Type IN VARCHAR2,
228 p_View_Application_Id IN NUMBER DEFAULT 0,
229 p_Security_Group_Id IN NUMBER DEFAULT 0)
230 RETURN VARCHAR2 IS
231
232 l_Procedure_Name VARCHAR2(30) := 'get_lookup_code';
233 l_Lookup_Code Fnd_Lookup_Values.Lookup_Code%TYPE := NULL;
234 l_Runtime_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
235 l_Procedure_Level NUMBER := Fnd_Log.Level_Procedure;
236 l_Statement_Level NUMBER := Fnd_Log.Level_Statement;
237 l_Exception_Level NUMBER := Fnd_Log.Level_Exception;
238
239 -- this cursor is to get looup_meaning under some lookup_code
240 CURSOR c_Lookup IS
241 SELECT Flv.Lookup_Code
242 FROM Fnd_Lookup_Values Flv
243 WHERE Flv.LANGUAGE = Userenv('LANG')
244 AND Flv.Lookup_Type = p_Lookup_Type
245 AND Flv.Meaning = p_Lookup_Meaning
246 AND Flv.View_Application_Id = p_View_Application_Id
247 AND Flv.Security_Group_Id = p_Security_Group_Id;
248
249 BEGIN
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 || '.begin',
254 'begin procedure');
255 END IF; --l_procedure_level >= l_runtime_level
256
257 IF p_Lookup_Meaning IS NULL THEN
258 l_Lookup_Code := NULL;
259 ELSE
260 OPEN c_Lookup;
261 FETCH c_Lookup
262 INTO l_Lookup_Code;
263 IF c_Lookup%NOTFOUND THEN
264 l_Lookup_Code := NULL;
265 END IF;
266 CLOSE c_Lookup;
267 END IF; --IF p_lookup_code IS NULL
268
269 --log
270 IF (l_Procedure_Level >= l_Runtime_Level) THEN
271 Fnd_Log.STRING(l_Procedure_Level,
272 l_Module_Prefix || '.' || l_Procedure_Name || '.end',
273 'end procedure');
274 END IF; --l_procedure_level >= l_runtime_level
275
276 RETURN l_Lookup_Code;
277
278 END Get_Lookup_Code;
279 --==========================================================================
280 -- PROCEDURE NAME:
281 -- populate_journal_of_period Private
282 --
283 -- DESCRIPTION:
284 -- This procedure is used to populate journal with period ,journal
285 -- soruce and category, which company segment is possessed by current
286 -- legal entity into table JA_CN_JOURNALS_OF_PERIOD
287 -- PARAMETERS:
288 -- In: p_start_period the start period name from which
289 -- current SOB is start-up
290 -- p_end_period the till period name to which
291 -- the CNAO journal should be processed
292 -- p_request_id identifier of current session
293 --
294 --
295 -- DESIGN REFERENCES:
296 -- None
297 --
298 -- CHANGE HISTORY:
299 -- 02/21/2006 Qingjun Zhao Created
300 -- 07/09/2009 Chaoqun Wu Fixing bug#8670470
301 --==========================================================================
302 PROCEDURE Populate_Journal_Of_Period(p_Start_Period IN VARCHAR2,
303 p_ledger_id in number,
304 p_legal_entity_id in number,
305 p_End_Period IN VARCHAR2,
306 p_Request_Id IN NUMBER) IS
307 l_Populate_Journal_Sql VARCHAR2(4000);
308 l_Company_Column_Name VARCHAR2(30);
309 l_Start_Period VARCHAR2(15);
310 l_End_Period VARCHAR2(15);
311 l_Request_Id NUMBER;
312 l_ledger_id number;
313 l_legal_entity_id number;
314 -- l_Populate_Bsv_Flag number;
315 -- l_Populate_Bsv_F varchar2(1);
316 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
317 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
318 l_Proc_Name VARCHAR2(100) := 'populate_journal_of_period';
319 l_Populate_Bsv_Flag VARCHAR2(1);
320 BEGIN
321
322 --log for debug
323 IF (l_Proc_Level >= l_Dbg_Level) THEN
324 Fnd_Log.STRING(l_Proc_Level,
325 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
326 'Enter procedure');
327 Fnd_Log.STRING(l_Proc_Level,
328 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
329 'p_start_period: ' || p_Start_Period);
330 Fnd_Log.STRING(l_Proc_Level,
331 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
332 'p_end_period: ' || p_End_Period);
333 Fnd_Log.STRING(l_Proc_Level,
334 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
335 'p_request_id: ' || p_Request_Id);
336 END IF; --(l_proc_level >= l_dbg_level)
337
338 l_Start_Period := p_Start_Period;
339 l_End_Period := p_End_Period;
340 l_Request_Id := p_Request_Id;
341 l_ledger_id := p_ledger_id;
342 l_legal_entity_id := P_legal_entity_id;
343 --populate BSV for current legal entity and ledger
344 l_Populate_Bsv_Flag := Ja_Cn_Utility.Populate_Ledger_Le_Bsv_Gt(l_Ledger_Id,
345 l_Legal_Entity_Id);
346 IF l_Populate_Bsv_Flag = 'F' THEN
347 IF (l_Proc_Level >= l_Dbg_Level) THEN
348 Fnd_Log.STRING(l_Proc_Level,
349 l_Module_Prefix || '.' || l_Proc_Name,
350 'fail to populate BSV');
351 END IF; --(l_proc_level >= l_dbg_level)
352 END IF;
353
354 --get application column name of company segment
355 SELECT Fsav.Application_Column_Name
356 INTO l_Company_Column_Name
357 FROM Fnd_Id_Flex_Segments Fifs,
358 Fnd_Segment_Attribute_Values Fsav,
359 Gl_Ledgers Led
360 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
361 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
362 AND Fsav.Segment_Attribute_Type = 'GL_BALANCING'
363 AND Fsav.Attribute_Value = 'Y'
364 AND Fifs.Application_Id = 101
365 and fifs.id_flex_code = fsav.id_flex_code
366 and fifs.id_flex_code = 'GL#'
367 AND Fifs.Application_Id = Fsav.Application_Id
368 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
369 AND Led.Ledger_Id = l_Ledger_Id;
370
371 --generate dynamic sql to populate journal into ja_cn_journals_of_period,
372 --which will be itemized.
373 l_Populate_Journal_Sql := 'INSERT INTO ja_cn_journals_of_period' ||
374 '(je_header_id' || ',je_line_num' ||
375 ',period_name' || ',je_category' ||
376 ',je_source' || ',legal_entity_id' ||
377 ',request_id,effective_date)' ||
378 'SELECT /*+index(jop,ja_cn_journals_of_period_n3)+*/ ' ||
379 ' jeh.je_header_id' ||
380 ' ,jel.je_line_num' ||
381 ' ,jeh.period_name' ||
382 ' ,jeh.je_category' ||
383 ' ,jeh.je_source' ||
384 ' ,bsv.legal_entity_id' || ',' ||
385 l_Request_Id ||
386 ',jeh.default_effective_date ' ||
387 ' FROM gl_je_headers jeh' ||
388 ' ,gl_je_lines jel' ||
389 ' ,gl_code_combinations gcc' ||
390 ' ,gl_periods gp' ||
391 ' ,gl_ledgers led' ||
392 ' ,ja_cn_ledger_le_bsv_gt bsv' ||
393 ' WHERE jeh.je_header_id = jel.je_header_id' ||
394 ' AND jeh.status = ''P''' ||
395 ' AND jeh.period_name = gp.period_name' ||
396 ' AND jel.code_combination_id = gcc.code_combination_id' ||
397 ' AND jeh.LEDGER_ID = ' || l_Ledger_Id ||
398 ' AND gcc.' || l_Company_Column_Name ||
399 ' = bsv.BAL_SEG_VALUE' ||
400 ' AND bsv.legal_entity_id = ' ||
401 l_Legal_Entity_Id ||
402 ' AND gp.start_date BETWEEN' ||
403 ' (SELECT start_date' ||
404 ' FROM gl_periods' ||
405 ' WHERE period_name =''' ||
406 l_Start_Period || '''' ||
407 ' AND period_set_name = led.period_set_name)' ||
408 ' AND (SELECT start_date' ||
409 ' FROM gl_periods' ||
410 ' WHERE period_name =''' ||
411 l_End_Period || '''' ||
412 ' AND period_set_name = led.period_set_name)' ||
413 ' AND gp.period_set_name = led.period_set_name' ||
414 ' AND gp.period_type = led.accounted_period_type' ||
415 ' AND led.ledger_id = jeh.ledger_id' ||
416 ' AND nvl(jel.global_attribute7' || --updated for CNAOV2 solution by chaoqun on 13-May-2010
417 ' ,''U'') <> ''P''' ||
418 ' AND jeh.ACTUAL_FLAG <> ''B''' || --Added for fixing bug#10316599 01-December-2010
419 ' AND jeh.ACTUAL_FLAG <> ''E'''; --Added for fixing bug#8670470 by Chaoqun on 09-JUL-2009
420 --log for debug
421 IF (l_Proc_Level >= l_Dbg_Level) THEN
422 Fnd_Log.STRING(l_Proc_Level,
423 l_Module_Prefix || '.' || l_Proc_Name,
424 'l_populate_journal_sql:' || l_Populate_Journal_Sql);
425 END IF; --(l_proc_level >= l_dbg_level)
426
427 EXECUTE IMMEDIATE l_Populate_Journal_Sql;
428 COMMIT;
429 -- log for debug
430 IF (l_Proc_Level >= l_Dbg_Level) THEN
431 Fnd_Log.STRING(l_Proc_Level,
432 l_Module_Prefix || '.' || l_Proc_Name || '.end',
433 'Enter procedure');
434 END IF; --(l_proc_level >= l_dbg_level)
435 EXCEPTION
436 WHEN OTHERS THEN
437
438 --log for debug
439 IF (l_Proc_Level >= l_Dbg_Level) THEN
440 Fnd_Log.STRING(l_Proc_Level,
441 l_Module_Prefix || '.' || l_Proc_Name ||
442 '. Other_Exception ',
443 SQLCODE || ':' || SQLERRM);
444 END IF; --(l_proc_level >= l_dbg_level)
445 RAISE;
446 END Populate_Journal_Of_Period;
447
448 --==========================================================================
449 -- PROCEDURE NAME:
450 -- generate_code_combination_view private
451 --
452 -- DESCRIPTION:
453 -- This procedure is used to populate account segment, company segment,
454 -- and all other COA segments defined in subsidiary account source form
455 -- into view JA_CN_CODE_COMBINATION_V
456 -- PARAMETERS:
457 --
458 --
459 --
460 -- DESIGN REFERENCES:
461 -- None
462 --
463 -- CHANGE HISTORY:
464 -- 02/21/2006 Qingjun Zhao Created
465 -- 04/10/2006 Qingjun Zhao Deal with this situation which Cost
466 -- segment is NULL in current Chart of
467 -- account
468 -- 19/01/2010 Chaoqun Wu Updated for CNAOV2 solution
469 -- 15/04/2011 Jianchao Chi Updated for Zero Downtime Patching, bug 12363301
470 --===========================================================================
471 PROCEDURE Generate_Code_Combination_View(p_ledger_id in number) IS
472
473 l_Create_View_Sql VARCHAR2(4000);
474 l_Company_Column_Name VARCHAR2(30);
475 l_Account_Column_Name VARCHAR2(30);
476 l_Cost_Column_Name VARCHAR2(30);
477 l_Project_Column_Name VARCHAR2(30);
478 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
479 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
480 l_Proc_Name VARCHAR2(100) := 'generate_code_combination_view';
481 l_Second_Track_Col_Name VARCHAR2(30);
482 l_Other_Cols_Name VARCHAR2(200);
483 l_ledger_id number;
484
485 --Begin: Chaoqun added for CNAOV2 on 01-19-2010
486 --Get COA segments and their assocaiated subsidiary segments defined in subsidiary account source form
487 CURSOR c_sub_coa_segment IS
488 SELECT NULL CONTEXT_CODE,
489 FLV.LOOKUP_CODE SUBSIDIARY_SEGMENT_CODE
490 FROM FND_LOOKUP_VALUES FLV
491 WHERE FLV.LOOKUP_TYPE = 'JA_CN_SUB_ACC_SEGMENT'
492 AND FLV.LANGUAGE = USERENV('LANG')
493 AND NOT EXISTS(
494 SELECT * FROM JA_CN_SUB_ACC_MAPPING SAM,
495 GL_LEDGERS LED
496 WHERE SAM.SOURCES_CODE = 'COA'
497 AND SAM.CHART_OF_ACCOUNTS_ID = LED.CHART_OF_ACCOUNTS_ID
498 AND LED.LEDGER_ID = l_Ledger_Id
499 AND SAM.SUBSIDIARY_SEGMENT_CODE = FLV.LOOKUP_CODE)
500 UNION
501 SELECT SAM.CONTEXT_CODE,
502 SAM.SUBSIDIARY_SEGMENT_CODE
503 FROM JA_CN_SUB_ACC_MAPPING SAM,
504 GL_LEDGERS LED
505 WHERE SAM.SOURCES_CODE = 'COA'
506 AND SAM.CHART_OF_ACCOUNTS_ID = LED.CHART_OF_ACCOUNTS_ID
507 AND LED.LEDGER_ID = l_Ledger_Id;
508
509 --End: Chaoqun added for CNAOV2 on 01-19-2010
510
511 --Begin: Delete by Chaoqun for CNAOV2
512 /*cursor c_company_segment is
513 SELECT led.bal_seg_column_name
514 from gl_ledgers led
515 where Led.Ledger_Id = l_Ledger_Id;
516
517 CURSOR c_Cost_Center IS
518 SELECT Fsav.Application_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 = 'FA_COST_CTR'
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
533 --jogen
534 CURSOR c_Segements IS
535 SELECT Fsav.Application_Column_Name
536 FROM Fnd_Id_Flex_Segments Fifs,
537 Fnd_Segment_Attribute_Values Fsav,
538 Gl_Ledgers Led
539 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
540 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
541 AND Fsav.Segment_Attribute_Type = 'GL_GLOBAL'
542 AND Fsav.Attribute_Value = 'Y'
543 AND Fifs.Application_Id = 101
544 and fifs.id_flex_code = fsav.id_flex_code
545 and fifs.id_flex_code = 'GL#'
546 AND Fifs.Application_Id = Fsav.Application_Id
547 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
548 AND Led.Ledger_Id = l_Ledger_Id;
549
550 --jogen*/
551 --End: Delete by Chaoqun for CNAOV2
552
553 BEGIN
554
555 --log for debug
556 IF (l_Proc_Level >= l_Dbg_Level) THEN
557 Fnd_Log.STRING(l_Proc_Level,
558 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
559 'Enter procedure');
560 END IF; --(l_proc_level >= l_dbg_level)
561 l_ledger_id := p_ledger_id;
562
563
564 --Begin: Chaoqun updated for CNAOV2 on 01-19-2010
565
566 --get application column name of company segment
567 SELECT led.bal_seg_column_name
568 INTO l_Company_Column_Name
569 from gl_ledgers led
570 where Led.Ledger_Id = l_Ledger_Id;
571
572 --get application column name of account segment
573 SELECT Fsav.Application_Column_Name
574 INTO l_Account_Column_Name
575 FROM Fnd_Id_Flex_Segments Fifs,
576 Fnd_Segment_Attribute_Values Fsav,
577 Gl_Ledgers Led
578 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
579 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
580 AND Fsav.Segment_Attribute_Type = 'GL_ACCOUNT'
581 AND Fsav.Attribute_Value = 'Y'
582 AND Fifs.Application_Id = 101
583 and fsav.id_flex_code = fifs.id_flex_code
584 and fsav.id_flex_code = 'GL#'
585 AND Fifs.Application_Id = Fsav.Application_Id
586 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
587 AND Led.Ledger_Id = l_Ledger_Id;
588
589 l_Create_View_Sql := 'select GCC.CODE_COMBINATION_ID,led.ledger_id,' ||
590 'gcc.' || l_Company_Column_Name ||
591 ' company_segment,';
592 l_Create_View_Sql := l_Create_View_Sql || 'gcc.' ||
593 l_Account_Column_Name || ' account_segment';
594
595
596 FOR l_sub_coa_segment IN c_sub_coa_segment
597 LOOP
598
599 IF l_sub_coa_segment.CONTEXT_CODE IS NOT NULL THEN
600 l_Create_View_Sql := l_Create_View_Sql || ', gcc.' ||
601 l_sub_coa_segment.CONTEXT_CODE || ' ' ||
602 l_sub_coa_segment.SUBSIDIARY_SEGMENT_CODE;
603 ELSE
604 l_Create_View_Sql := l_Create_View_Sql || ', NULL '||
605 l_sub_coa_segment.SUBSIDIARY_SEGMENT_CODE;
606 END IF;
607
608 END LOOP;
609
610
611 l_Create_View_Sql := 'create or replace view ja_cn_code_combination_v as ' ||
612 l_Create_View_Sql || ' from gl_code_combinations gcc,' ||
613 ' GL_LEDGERS led where led.chart_of_accounts_id ' ||
614 ' = gcc.chart_of_accounts_id';
615 --Updated by Jianchao Chi for bug 12363301, Zero Downtime Patching can't use EXECUTE IMMEDIATE
616 --to create view, so replace it with AD_DLL.DO_DLL.
617 --EXECUTE IMMEDIATE l_Create_View_Sql;
618 AD_DDL.DO_DDL('APPS','JA',AD_DDL.create_view,l_Create_View_Sql,'ja_cn_code_combination_v');
619
620
621 /* --get application column name of cost center segment
622 OPEN c_Cost_Center;
623 FETCH c_Cost_Center
624 INTO l_Cost_Column_Name;
625
626 IF c_Cost_Center%NOTFOUND THEN
627 CLOSE c_Cost_Center;
628 l_Create_View_Sql := l_Create_View_Sql ||
629 ' to_char(null) cost_segment,';
630 ELSE
631 l_Create_View_Sql := l_Create_View_Sql || 'gcc.' ||
632 l_Cost_Column_Name || ' cost_segment,';
633 CLOSE c_Cost_Center;
634 END IF; --c_cost_center%NOTFOUND
635
636 IF l_Project_Option = 'COA' THEN
637 --get application column name of project segment
638 SELECT Coa_Segment
639 INTO l_Project_Column_Name
640 FROM Ja_Cn_Sub_Acc_Sources_All
641 WHERE Chart_Of_Accounts_Id = l_Chart_Of_Accounts_Id;
642 l_Create_View_Sql := l_Create_View_Sql || 'gcc.' ||
643 l_Project_Column_Name || ' project_number,';
644 ELSE
645 l_Create_View_Sql := l_Create_View_Sql || 'to_char(null)' ||
646 ' project_number,';
647 END IF; --l_project_option = 'COA'
648
649 ---jogen
650 BEGIN
651 SELECT Fsav.Application_Column_Name
652 INTO l_Second_Track_Col_Name
653 FROM Fnd_Id_Flex_Segments Fifs,
654 Fnd_Segment_Attribute_Values Fsav,
655 Gl_Ledgers Led
656 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
657 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
658 AND Fsav.Segment_Attribute_Type = 'GL_SECONDARY_TRACKING'
659 AND Fsav.Attribute_Value = 'Y'
660 AND Fifs.Application_Id = 101
661 and fifs.id_flex_code = fsav.id_flex_code
662 and fsav.id_flex_code = 'GL#'
663 AND Fifs.Application_Id = Fsav.Application_Id
664 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
665 AND Led.Ledger_Id = l_Ledger_Id;
666 EXCEPTION
667 WHEN No_Data_Found THEN
668 NULL;
669 END;
670
671 IF l_Second_Track_Col_Name IS NULL THEN
672 l_Second_Track_Col_Name := 'NULL';
673 END IF;
674
675 FOR Rec_Segment IN c_Segements LOOP
676 IF Rec_Segment.Application_Column_Name NOT IN
677 (l_Company_Column_Name, l_Account_Column_Name, l_Cost_Column_Name,
678 l_Second_Track_Col_Name) THEN
679 l_Other_Cols_Name := l_Other_Cols_Name || '||''.''||' ||
680 Rec_Segment.Application_Column_Name;
681 END IF;
682 END LOOP;
683
684 IF l_Other_Cols_Name IS NULL THEN
685 l_Other_Cols_Name := 'NULL';
686 ELSE
687 l_Other_Cols_Name := Substr(l_Other_Cols_Name, 8);
688 END IF;
689
690 l_Create_View_Sql := l_Create_View_Sql || l_Second_Track_Col_Name ||
691 ' second_tracking_col,' || l_Other_Cols_Name ||
692 ' other_columns,';
693 --jogen
694 l_Create_View_Sql := l_Create_View_Sql ||
695 'to_number(null) project_id from gl_code_combinations gcc,' ||
696 ' GL_LEDGERS led where led.chart_of_accounts_id ' ||
697 ' = gcc.chart_of_accounts_id';
698
699 l_Create_View_Sql := 'create or replace view ja_cn_code_combination_v as ' ||
700 l_Create_View_Sql;
701
702 --log for debug*/
703
704 IF (l_Proc_Level >= l_Dbg_Level) THEN
705 Fnd_Log.STRING(l_Proc_Level,
706 l_Module_Prefix || '.' || l_Proc_Name,
707 'l_create_view_sql:' || l_Create_View_Sql);
708 END IF; --(l_proc_level >= l_dbg_level)
709
710 --EXECUTE IMMEDIATE l_Create_View_Sql; --Chaoqun deleted for CNAOV2 on 01-19-2010
711
712 --log for dubug
713 IF (l_Proc_Level >= l_Dbg_Level) THEN
714 Fnd_Log.STRING(l_Proc_Level,
715 l_Module_Prefix || '.' || l_Proc_Name || '.end',
716 'Enter procedure');
717 END IF; --(l_proc_level >= l_dbg_level)
718
719 --End: Chaoqun updated for CNAOV2 on 01-19-2010
720
721 EXCEPTION
722 WHEN OTHERS THEN
723 FND_FILE.put_line(FND_FILE.OUTPUT,SQLCODE || ':' || SQLERRM);
724 --log for debug
725 IF (l_Proc_Level >= l_Dbg_Level) THEN
726 Fnd_Log.STRING(l_Proc_Level,
727 l_Module_Prefix || '.' || l_Proc_Name ||
728 '. Other_Exception ',
729 SQLCODE || ':' || SQLERRM);
730 END IF; --(l_proc_level >= l_dbg_level)
731 RAISE;
732 END Generate_Code_Combination_View;
733 --==========================================================================
734 -- PROCEDURE NAME:
735 -- get_period_range private
736 --
737 -- DESCRIPTION:
738 -- This procedure is used to get range of period in which journal lines
739 -- will be itemized
740 -- PARAMETERS:
741 -- p_period_name period inputted by user
742 -- p_start_period_name start period
743 -- p_end_period_name end period
744 --
745 -- DESIGN REFERENCES:
746 -- None
747 --
748 -- CHANGE HISTORY:
749 -- 02/21/2006 Qingjun Zhao Created
750 --===========================================================================
751 PROCEDURE Get_Period_Range(p_Period_Name IN VARCHAR2,
752 p_ledger_id in number,
753 p_Start_Period_Name OUT NOCOPY VARCHAR2,
754 p_End_Period_Name OUT NOCOPY VARCHAR2) IS
755
756 l_Period_Name VARCHAR2(15);
757 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
758 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
759 l_Proc_Name VARCHAR2(100) := 'get_period_range';
760 l_ledger_id number;
761 BEGIN
762
763 --log for debug
764 IF (l_Proc_Level >= l_Dbg_Level) THEN
765 Fnd_Log.STRING(l_Proc_Level,
766 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
767 'Enter procedure');
768 Fnd_Log.STRING(l_Proc_Level,
769 l_Module_Prefix || '.' || l_Proc_Name || '.prameter',
770 'P_period_name:' || p_Period_Name);
771 END IF; --(l_proc_level >= l_dbg_level)
772
773 l_Period_Name := p_Period_Name;
774 l_ledger_id := p_ledger_id;
775 --get the first period of current led
776 SELECT Gp.Period_Name
777 INTO p_Start_Period_Name
778 FROM Gl_Periods Gp, Gl_Ledgers Led
779 WHERE Led.Ledger_Id = l_Ledger_Id
780 AND Led.Period_Set_Name = Gp.Period_Set_Name
781 AND Led.Accounted_Period_Type = Gp.Period_Type
782 AND Gp.Start_Date IN
783 (SELECT MIN(Start_Date)
784 FROM Gl_Periods Gp
785 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
786 AND Led.Accounted_Period_Type = Gp.Period_Type);
787
788 -- if parameter period is null then pick up last open period as end period
789 IF l_Period_Name IS NULL THEN
790 SELECT Gp.Period_Name
791 INTO p_End_Period_Name
792 FROM Gl_Periods Gp, Gl_Ledgers Led
793 WHERE Led.Ledger_Id = l_Ledger_Id
794 AND Led.Period_Set_Name = Gp.Period_Set_Name
795 AND Led.Accounted_Period_Type = Gp.Period_Type
796 AND Gp.Start_Date IN
797 (SELECT MAX(Start_Date)
798 FROM Gl_Periods Gp
799 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
800 AND Led.Accounted_Period_Type = Gp.Period_Type);
801 ELSE
802 p_End_Period_Name := l_Period_Name;
803 END IF; --l_period_name IS NULL
804
805 --log for debug
806 IF (l_Proc_Level >= l_Dbg_Level) THEN
807 Fnd_Log.STRING(l_Proc_Level,
808 l_Module_Prefix || '.' || l_Proc_Name || '.parameter',
809 'p_start_period_name: ' || p_Start_Period_Name);
810 Fnd_Log.STRING(l_Proc_Level,
811 l_Module_Prefix || '.' || l_Proc_Name || '.parameter',
812 'p_end_period_name: ' || p_End_Period_Name);
813 Fnd_Log.STRING(l_Proc_Level,
814 l_Module_Prefix || '.' || l_Proc_Name || '.end',
815 'Exit procedure');
816 END IF; --( l_proc_level >= l_dbg_level )
817
818 EXCEPTION
819 WHEN OTHERS THEN
820
821 --log for debug
822 IF (l_Proc_Level >= l_Dbg_Level) THEN
823 Fnd_Log.STRING(l_Proc_Level,
824 l_Module_Prefix || '.' || l_Proc_Name ||
825 '. Other_Exception ',
826 SQLCODE || ':' || SQLERRM);
827 END IF; --(l_proc_level >= l_dbg_level)
828 RAISE;
829 END Get_Period_Range;
830
831 --==========================================================================
832 -- PROCEDURE NAME:
833 -- purge_unmatch_lines Private
834 --
835 -- DESCRIPTION:
836 -- This procedure is used to populate the journals which cannot be
837 -- drill down into possible sub legder or which is inputed directly
838 -- in manual way in Oracle General Ledger Module
839 -- PARAMETERS:
840 -- In: p_request_id identifier of current session
841 --
842 -- DESIGN REFERENCES:
843 -- None
844 --
845 -- CHANGE HISTORY:
846 -- 02/21/2006 Qingjun Zhao Created
847 --===========================================================================
848 PROCEDURE Purge_Unmatch_Lines(p_Request_Id IN NUMBER) IS
849 l_Request_Id NUMBER;
850 l_Je_Line_Num NUMBER;
851 l_Je_Header_Id NUMBER;
852 l_Error_Msg VARCHAR2(2000);
853 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
854 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
855 l_Proc_Name VARCHAR2(100) := 'purge_unmatch_lines';
856 CURSOR c_Unmatch_Lines IS
857 SELECT /*+ index(jel,gl_je_lines_u1)*/
858 Req.Je_Header_Id, Req.Je_Line_Num
859 FROM (SELECT SUM(Nvl(Req.Accounted_Dr, 0) - Nvl(Req.Accounted_Cr, 0)) Accounted_Amount,
860 SUM(Nvl(Req.Entered_Dr, 0) - Nvl(Req.Entered_Cr, 0)) Entered_Amount,
861 Req.Je_Header_Id,
862 Req.Je_Line_Num
863 FROM Ja_Cn_Journal_Lines_Req Req
864 WHERE Req.Request_Id = l_Request_Id
865 GROUP BY Req.Je_Header_Id, Req.Je_Line_Num) Req,
866 Gl_Je_Lines Jel
867 WHERE (Nvl(Jel.Accounted_Dr, 0) - Nvl(Jel.Accounted_Cr, 0) <>
868 Req.Accounted_Amount OR
869 Nvl(Jel.Entered_Dr, 0) - Nvl(Jel.Entered_Cr, 0) <>
870 Req.Entered_Amount)
871 AND Jel.Je_Line_Num = Req.Je_Line_Num
872 AND Jel.Je_Header_Id = Req.Je_Header_Id;
873 BEGIN
874 --log for debug
875 IF (l_Proc_Level >= l_Dbg_Level) THEN
876 Fnd_Log.STRING(l_Proc_Level,
877 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
878 'Enter procedure');
879 Fnd_Log.STRING(l_Proc_Level,
880 l_Module_Prefix || '.' || l_Proc_Name || '.prameter',
881 'P_request_id:' || p_Request_Id);
882 END IF; --(l_proc_level >= l_dbg_level)
883
884 l_Request_Id := p_Request_Id;
885 OPEN c_Unmatch_Lines;
886
887 LOOP
888 FETCH c_Unmatch_Lines
889 INTO l_Je_Header_Id, l_Je_Line_Num;
890 EXIT WHEN c_Unmatch_Lines%NOTFOUND;
891 --Raise error message for caller
892 Fnd_Message.Set_Name(Application => 'JA', NAME => 'JA_CN_XXXX');
893 Fnd_Message.Set_Token('header id', l_Je_Header_Id, TRUE);
894 Fnd_Message.Set_Token('line num', l_Je_Line_Num, TRUE);
895 l_Error_Msg := Fnd_Message.Get;
896
897 --Output error message
898 Fnd_File.Put_Line(Fnd_File.Log, l_Error_Msg);
899
900 -- log for debug
901 IF (l_Proc_Level >= l_Dbg_Level) THEN
902 Fnd_Log.STRING(l_Proc_Level,
903 l_Module_Prefix || '.' || l_Proc_Name,
904 l_Error_Msg);
905 END IF; --(l_proc_level >= l_dbg_level)
906
907 DELETE FROM Ja_Cn_Journal_Lines_Req
908 WHERE Je_Header_Id = l_Je_Header_Id
909 AND Je_Line_Num = l_Je_Line_Num;
910
911 END LOOP;
912
913 CLOSE c_Unmatch_Lines;
914
915 -- log for debug
916 IF (l_Proc_Level >= l_Dbg_Level) THEN
917 Fnd_Log.STRING(l_Proc_Level,
918 l_Module_Prefix || '.' || l_Proc_Name || '.end',
919 'Enter procedure');
920 END IF; --(l_proc_level >= l_dbg_level)
921 EXCEPTION
922 WHEN OTHERS THEN
923
924 --log for debug
925 IF (l_Proc_Level >= l_Dbg_Level) THEN
926 Fnd_Log.STRING(l_Proc_Level,
927 l_Module_Prefix || '.' || l_Proc_Name ||
928 '. Other_Exception ',
929 SQLCODE || ':' || SQLERRM);
930 END IF; --(l_proc_level >= l_dbg_level)
931 RAISE;
932 END Purge_Unmatch_Lines;
933 --==========================================================================
934 -- PROCEDURE NAME:
935 -- unitemize_journal_lines Private
936 --
937 -- DESCRIPTION:
938 -- This procedure is used to populate the journals which cannot be
939 -- drill down into possible sub legder or which is inputed directly
940 -- in manual way in Oracle General Ledger Module
941 -- PARAMETERS:
942 -- In: p_project_option porject option
943 -- p_request_id identifier of current session
944 --
945 -- DESIGN REFERENCES:
946 -- None
947 --
948 -- CHANGE HISTORY:
949 -- 02/21/2006 Qingjun Zhao Created
950 -- 07/12/2007 Yanbo Liu Updated
951 -- 20/01/2010 Chaoqun Wu Updated for CNAOV2
952 -- 07/14/2010 Chaoqun Wu Fixed bug 9872212
953 --===========================================================================
954
955 PROCEDURE Unitemize_Journal_Lines(p_chart_of_accounts_id in number,
956 --p_Project_Option IN VARCHAR2, --Deleted by Chaoqun for CNAOV2
957 p_Request_Id IN NUMBER) IS
958
959 l_Request_Id NUMBER;
960 l_Project_Option Ja_Cn_Sub_Acc_Sources_All.Project_Source_Flag%TYPE;
961 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
962 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
963 l_Proc_Name VARCHAR2(100) := 'unitemize_journal_lines';
964
965 --Begin: Chaoqun updated for CNAOV2 on 01-20-2010
966
967 l_sql varchar2(8000) :='';
968 l_Insert_Header_Sql VARCHAR2(4000) := '';
969 l_Insert_Values_Sql VARCHAR2(4000) := '';
970 l_Chart_Of_Accounts_Id number := p_chart_of_accounts_id;
971 l_SM_Query_Header_Sql VARCHAR2(4000) := ''; --for settlement method
972
973 CURSOR c_Sub_Segment IS
974 SELECT SAM.SUBSIDIARY_SEGMENT_CODE,
975 SAM.SOURCES_CODE,
976 SAM.CONTEXT_CODE
977 FROM JA_CN_SUB_ACC_MAPPING SAM
978 WHERE SAM.CHART_OF_ACCOUNTS_ID = l_Chart_Of_Accounts_Id;
979
980 --End: Chaoqun added for CNAOV2 on 01-20-2010
981
982 BEGIN
983
984 --log for debug
985 IF (l_Proc_Level >= l_Dbg_Level) THEN
986 Fnd_Log.STRING(l_Proc_Level,
987 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
988 'Enter procedure');
989 Fnd_Log.STRING(l_Proc_Level,
990 l_Module_Prefix || '.' || l_Proc_Name || '.prameter',
991 'P_request_id:' || p_Request_Id);
992 Fnd_Log.STRING(l_Proc_Level, --Updated by Chaoqun for CNAOV2
993 l_Module_Prefix || '.' || l_Proc_Name || '.prameter',
994 'p_chart_of_accounts_id:' || p_chart_of_accounts_id);
995
996 END IF; --(l_proc_level >= l_dbg_level)
997
998 l_Request_Id := p_Request_Id;
999
1000 --Begin: Chaoqun updated for CNAOV2 on 01-20-2010
1001
1002 --l_Project_Option := p_Project_Option;
1003
1004 FOR l_Sub_Segment IN c_Sub_Segment
1005 LOOP
1006
1007 l_Insert_Header_Sql := l_Insert_Header_Sql || l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ';
1008
1009 IF l_Sub_Segment.SOURCES_CODE = 'COA' THEN
1010
1011 l_Insert_Values_Sql := l_Insert_Values_Sql || 'jcc.' ||
1012 l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ';
1013
1014 ELSE
1015 l_Insert_Values_Sql := l_Insert_Values_Sql ||
1016 'To_Char(NULL) ' || l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ' ;
1017
1018 END IF;
1019
1020 END LOOP;
1021
1022 --Fetching settlement method from AGIS and GL
1023 l_SM_Query_Header_Sql := ' decode(jeh.JE_SOURCE,''Payables'', NULL,'||
1024 ' ''Receivables'', NULL,'||
1025 ' ''Global Intercompany'','||
1026 ' (Select nvl(DECODE(DFFA.ATTRIBUTE_COLUMN, ''ATTRIBUTE1'',TRLHDR.attribute1, ''ATTRIBUTE2'',TRLHDR.attribute2,'||
1027 ' ''ATTRIBUTE3'',TRLHDR.attribute3, ''ATTRIBUTE4'',TRLHDR.attribute4, ''ATTRIBUTE5'',TRLHDR.attribute5,'||
1028 ' ''ATTRIBUTE6'',TRLHDR.attribute6, ''ATTRIBUTE7'',TRLHDR.attribute7, ''ATTRIBUTE8'',TRLHDR.attribute8,'||
1029 ' ''ATTRIBUTE9'',TRLHDR.attribute9, ''ATTRIBUTE10'',TRLHDR.attribute10, ''ATTRIBUTE11'',TRLHDR.attribute11,'||
1030 ' ''ATTRIBUTE12'',TRLHDR.attribute12, ''ATTRIBUTE13'',TRLHDR.attribute13, ''ATTRIBUTE14'',TRLHDR.attribute14,'||
1031 ' ''ATTRIBUTE15'',TRLHDR.attribute15)'||
1032 ' , '''')'||
1033 ' FROM GL_IMPORT_REFERENCES GIR,'||
1034 ' FUN_TRX_HEADERS TRLHDR,'||
1035 ' JA_CN_DFF_ASSIGNMENTS DFFA'||
1036 ' WHERE GIR.REFERENCE_1 = ''Intercompany Transaction'''||
1037 ' AND GIR.JE_HEADER_ID = JEH.JE_HEADER_ID'||
1038 ' AND GIR.JE_LINE_NUM = JEL.JE_LINE_NUM'||
1039 ' AND GIR.REFERENCE_3 = TO_CHAR(TRLHDR.TRX_ID)'||
1040 ' AND DFFA.DFF_TITLE_CODE = ''IITH'''||
1041 ' AND DFFA.APPLICATION_ID = 435 AND DFFA.chart_of_accounts_id = '||p_chart_of_accounts_id||'),'||
1042 ' (SELECT decode(jeh.context, dffa.context_code,'||
1043 ' decode(dffa.attribute_column, ''ATTRIBUTE1'',jeh.attribute1, ''ATTRIBUTE2'',jeh.attribute2,'||
1044 ' ''ATTRIBUTE3'',jeh.attribute3, ''ATTRIBUTE4'',jeh.attribute4, ''ATTRIBUTE5'',jeh.attribute5,'||
1045 ' ''ATTRIBUTE6'',jeh.attribute6, ''ATTRIBUTE7'',jeh.attribute7, ''ATTRIBUTE8'',jeh.attribute8,'||
1046 ' ''ATTRIBUTE9'',jeh.attribute9, ''ATTRIBUTE10'',jeh.attribute10))'||
1047 ' FROM ja_cn_dff_assignments dffa'||
1048 ' WHERE dffa.Application_Id = 101'||
1049 ' AND dffa.chart_of_accounts_id = '||p_chart_of_accounts_id||
1050 ' AND dffa.dff_title_code=''GLJO'')'||
1051 ' ) Settlement_Method_Number ';
1052
1053 l_sql:='INSERT INTO Ja_Cn_Journal_Lines_Req';
1054 l_sql:=l_sql||' (Je_Header_Id,';
1055 l_sql:=l_sql||' Ledger_Id,';
1056 l_sql:=l_sql||' Legal_Entity_Id,';
1057 l_sql:=l_sql||' Journal_Number,';
1058 l_sql:=l_sql||' Je_Category,';
1059 l_sql:=l_sql||' Default_Effective_Date,';
1060 l_sql:=l_sql||' Period_Name,';
1061 l_sql:=l_sql||' Currency_Code,';
1062 l_sql:=l_sql||' Currency_Conversion_Rate,';
1063 l_sql:=l_sql||' Currency_Conversion_Type,';--Added by Chaoqun for fixing bug 9872212
1064 l_sql:=l_sql||' Je_Line_Num,';
1065 l_sql:=l_sql||' Line_Number,';
1066 l_sql:=l_sql||' Description,';
1067 l_sql:=l_sql||' Company_Segment,';
1068 l_sql:=l_sql||' Code_Combination_Id,';
1069 --l_sql:=l_sql||' Cost_Center,';
1070 --l_sql:=l_sql||' Third_Party_Id,';
1071 --l_sql:=l_sql||' Third_Party_Number,';
1072 --l_sql:=l_sql||' Personnel_Id,';
1073 --l_sql:=l_sql||' Personnel_Number,';
1074 --l_sql:=l_sql||' Project_Number,';
1075 --l_sql:=l_sql||' Project_Source,';
1076 l_sql:=l_sql||l_Insert_Header_Sql;
1077 l_sql:=l_sql||' Account_Segment,';
1078 l_sql:=l_sql||' Entered_Dr,';
1079 l_sql:=l_sql||' Entered_Cr,';
1080 l_sql:=l_sql||' Accounted_Dr,';
1081 l_sql:=l_sql||' Accounted_Cr,';
1082 l_sql:=l_sql||' Status,';
1083 l_sql:=l_sql||' Created_By,';
1084 l_sql:=l_sql||' Creation_Date,';
1085 l_sql:=l_sql||' Last_Updated_By,';
1086 l_sql:=l_sql||' Last_Update_Date,';
1087 l_sql:=l_sql||' Last_Update_Login,';
1088 l_sql:=l_sql||' Populate_Code,';
1089 l_sql:=l_sql||' Request_Id,';
1090 l_sql:=l_sql||' Journal_Created_By,';
1091 l_sql:=l_sql||' Journal_Posted_By,';
1092 l_sql:=l_sql||' Settlement_Method_Number)';
1093 l_sql:=l_sql||' SELECT DISTINCT Jel.Je_Header_Id Je_Header_Id,';
1094 l_sql:=l_sql||' Jeh.Ledger_Id Ledger_Id,';
1095 l_sql:=l_sql||' Jop.Legal_Entity_Id Legal_Entity_Id,';
1096 l_sql:=l_sql||' To_Number(NULL) Journal_Number,';
1097 l_sql:=l_sql||' Jeh.Je_Category Je_Category,';
1098 l_sql:=l_sql||' Jeh.Default_Effective_Date Default_Effective_Date,';
1099 l_sql:=l_sql||' Jeh.Period_Name Period_Name,';
1100 l_sql:=l_sql||' Jeh.Currency_Code Currency_Code,';
1101 l_sql:=l_sql||' Jeh.Currency_Conversion_Rate Currency_Conversion_Rate,';
1102 l_sql:=l_sql||' Jeh.Currency_Conversion_Type Currency_Conversion_Type,';--Added by Chaoqun for fixing bug 9872212
1103 l_sql:=l_sql||' Jel.Je_Line_Num Je_Line_Num,';
1104 l_sql:=l_sql||' To_Number(NULL) Line_Number,';
1105 l_sql:=l_sql||' Nvl(Jel.Description, Jeh.Description) Description,';
1106 l_sql:=l_sql||' Jcc.Company_Segment Company_Segment,';
1107 l_sql:=l_sql||' Jcc.Code_Combination_Id Code_Combination_Id,';
1108 --l_sql:=l_sql||' Jcc.Cost_Segment Cost_Segment,';
1109 --l_sql:=l_sql||' To_Number(NULL) Third_Party_Id,';
1110 --l_sql:=l_sql||' To_Char(NULL) Third_Party_Number,';
1111 --l_sql:=l_sql||' To_Number(NULL) Personnel_Id,';
1112 --l_sql:=l_sql||' To_Char(NULL) Personnel_Number,';
1113 /* l_sql:=l_sql||' Decode(Nvl(l_Project_Option, ''N''),';
1114 l_sql:=l_sql||' ''N'',';
1115 l_sql:=l_sql||' To_Char(NULL),';
1116 l_sql:=l_sql||' ''COA'',';
1117 l_sql:=l_sql||' Jcc.Project_Number,';
1118 l_sql:=l_sql||' To_Char(NULL)) Project_Number,';*/
1119 --l_sql:=l_sql||' Nvl(l_Project_Option, ''N'') Project_Source,';
1120 l_sql:=l_sql||l_Insert_Values_Sql;
1121 l_sql:=l_sql||' Jcc.Account_Segment Account_Segment,';
1122 l_sql:=l_sql||' Jel.Entered_Dr,';
1123 l_sql:=l_sql||' Jel.Entered_Cr,';
1124 l_sql:=l_sql||' Jel.Accounted_Dr,';
1125 l_sql:=l_sql||' Jel.Accounted_Cr,';
1126 l_sql:=l_sql||' ''U'' Status,';
1127 l_sql:=l_sql||' Fnd_Global.User_Id Created_Gy,';
1128 l_sql:=l_sql||' SYSDATE Creation_Date,';
1129 l_sql:=l_sql||' Fnd_Global.User_Id Last_Updated_By,';
1130 l_sql:=l_sql||' SYSDATE Last_Update_Date,';
1131 l_sql:=l_sql||' Fnd_Global.Login_Id Last_Update_Login,';
1132 l_sql:=l_sql||' ''NO ITEMIZATION'',';
1133 l_sql:=l_sql||l_Request_Id||', ';
1134 l_sql:=l_sql||' Jeh.Created_By,';
1135 l_sql:=l_sql||' Jeb.Posted_By, ';
1136 l_sql:=l_sql|| l_SM_Query_Header_Sql; -- Added for fetching settlement method
1137 l_sql:=l_sql||' FROM Gl_Je_Headers Jeh,';
1138 l_sql:=l_sql||' Gl_Je_Lines Jel,';
1139 l_sql:=l_sql||' Ja_Cn_Code_Combination_v Jcc,';
1140 l_sql:=l_sql||' Ja_Cn_Journals_Of_Period Jop,';
1141 l_sql:=l_sql||' Gl_Je_Batches Jeb ';
1142 l_sql:=l_sql||' WHERE Jeh.Je_Header_Id = Jel.Je_Header_Id';
1143 l_sql:=l_sql||' AND Jcc.Ledger_Id = Jeh.Ledger_Id';
1144 l_sql:=l_sql||' AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id';
1145 l_sql:=l_sql||' AND Jeh.Je_Header_Id = Jop.Je_Header_Id';
1146 l_sql:=l_sql||' AND Jel.Je_Line_Num = Jop.Je_Line_Num';
1147 l_sql:=l_sql||' AND Jop.Request_Id = '||l_Request_Id;
1148 l_sql:=l_sql||' AND Jel.Je_Line_Num NOT IN';
1149 l_sql:=l_sql||' (SELECT Je_Line_Num';
1150 l_sql:=l_sql||' FROM Ja_Cn_Journal_Lines_Req';
1151 l_sql:=l_sql||' WHERE Je_Header_Id = Jeh.Je_Header_Id)';
1152 l_sql:=l_sql||' AND Jeb.Je_Batch_Id=Jeh.Je_Batch_Id';
1153
1154 execute immediate l_sql;
1155
1156 /* INSERT INTO Ja_Cn_Journal_Lines_Req
1157 (Je_Header_Id,
1158 Ledger_Id,
1159 Legal_Entity_Id,
1160 Journal_Number,
1161 Je_Category,
1162 Default_Effective_Date,
1163 Period_Name,
1164 Currency_Code,
1165 Currency_Conversion_Rate,
1166 Je_Line_Num,
1167 Line_Number,
1168 Description,
1169 Company_Segment,
1170 Code_Combination_Id,
1171 Cost_Center,
1172 Third_Party_Id,
1173 Third_Party_Number,
1174 Personnel_Id,
1175 Personnel_Number,
1176 Project_Number,
1177 Project_Source,
1178 Account_Segment,
1179 Entered_Dr,
1180 Entered_Cr,
1181 Accounted_Dr,
1182 Accounted_Cr,
1183 Status,
1184 Created_By,
1185 Creation_Date,
1186 Last_Updated_By,
1187 Last_Update_Date,
1188 Last_Update_Login,
1189 Populate_Code,
1190 Request_Id,
1191 Journal_Created_By,
1192 Journal_Posted_By)
1193 SELECT Jel.Je_Header_Id Je_Header_Id,
1194 Jeh.Ledger_Id Ledger_Id,
1195 Jop.Legal_Entity_Id Legal_Entity_Id,
1196 To_Number(NULL) Journal_Number,
1197 Jeh.Je_Category Je_Category,
1198 Jeh.Default_Effective_Date Default_Effective_Date,
1199 Jeh.Period_Name Period_Name,
1200 Jeh.Currency_Code Currency_Code,
1201 Jeh.Currency_Conversion_Rate Currency_Conversion_Rate,
1202 Jel.Je_Line_Num Je_Line_Num,
1203 To_Number(NULL) Line_Number,
1204 Nvl(Jel.Description, Jeh.Description) Description,
1205 Jcc.Company_Segment Company_Segment,
1206 Jcc.Code_Combination_Id Code_Combination_Id,
1207 Jcc.Cost_Segment Cost_Segment,
1208 To_Number(NULL) Third_Party_Id,
1209 To_Char(NULL) Third_Party_Number,
1210 To_Number(NULL) Personnel_Id,
1211 To_Char(NULL) Personnel_Number,
1212 Decode(Nvl(l_Project_Option, 'N'),
1213 'N',
1214 To_Char(NULL),
1215 'COA',
1216 Jcc.Project_Number,
1217 To_Char(NULL)) Project_Number,
1218 Nvl(l_Project_Option, 'N') Project_Source,
1219 Jcc.Account_Segment Account_Segment,
1220 Jel.Entered_Dr,
1221 Jel.Entered_Cr,
1222 Jel.Accounted_Dr,
1223 Jel.Accounted_Cr,
1224 'U' Status,
1225 Fnd_Global.User_Id Created_Gy,
1226 SYSDATE Creation_Date,
1227 Fnd_Global.User_Id Last_Updated_By,
1228 SYSDATE Last_Update_Date,
1229 Fnd_Global.Login_Id Last_Update_Login,
1230 'NO ITEMIZATION',
1231 l_Request_Id,
1232 Jeh.Created_By,
1233 Jeb.Posted_By --added by lyb, for bug for bug 6654734
1234 -- Decode(Nvl(Jeh.Accrual_Rev_Status, 'N'),
1235 -- 'R',
1236 -- To_Number(NULL),
1237 -- Jeh.Last_Updated_By)
1238 FROM Gl_Je_Headers Jeh,
1239 Gl_Je_Lines Jel,
1240 Ja_Cn_Code_Combination_v Jcc,
1241 Ja_Cn_Journals_Of_Period Jop,
1242 Gl_Je_Batches Jeb--added by lyb, for bug 6654734
1243 WHERE Jeh.Je_Header_Id = Jel.Je_Header_Id
1244 AND Jcc.Ledger_Id = Jeh.Ledger_Id
1245 AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id
1246 AND Jeh.Je_Header_Id = Jop.Je_Header_Id
1247 AND Jel.Je_Line_Num = Jop.Je_Line_Num
1248 AND Jop.Request_Id = l_Request_Id
1249 AND Jel.Je_Line_Num NOT IN
1250 (SELECT Je_Line_Num
1251 FROM Ja_Cn_Journal_Lines_Req
1252 WHERE Je_Header_Id = Jeh.Je_Header_Id)
1253 AND Jeb.Je_Batch_Id=Jeh.Je_Batch_Id;--added by lyb, for bug 6654734
1254 */
1255
1256 --End: Chaoqun updated for CNAOV2 on 01-20-2010
1257
1258 -- log for debug
1259 IF (l_Proc_Level >= l_Dbg_Level) THEN
1260 Fnd_Log.STRING(l_Proc_Level,
1261 l_Module_Prefix || '.' || l_Proc_Name || '.end',
1262 'Enter procedure');
1263 END IF; --(l_proc_level >= l_dbg_level)
1264 EXCEPTION
1265 WHEN OTHERS THEN
1266 ROLLBACK;
1267
1268 --log for debug
1269 IF (l_Proc_Level >= l_Dbg_Level) THEN
1270 Fnd_Log.STRING(l_Proc_Level,
1271 l_Module_Prefix || '.' || l_Proc_Name ||
1272 '. Other_Exception ',
1273 SQLCODE || ':' || SQLERRM);
1274 END IF; --(l_proc_level >= l_dbg_level)
1275 RAISE;
1276 END Unitemize_Journal_Lines;
1277
1278 --==========================================================================
1279 -- PROCEDURE NAME:
1280 -- generate_journal_and_line_num Private
1281 --
1282 -- DESCRIPTION:
1283 -- This procedure is used to generate journal number
1284 -- and journal line number based on legal entity level and period
1285 -- PARAMETERS:
1286 -- In: p_period_name period
1287 -- p_request_id identifier of current session
1288 --
1289 -- DESIGN REFERENCES:
1290 -- None
1291 --
1292 -- CHANGE HISTORY:
1293 -- 02/21/2006 Qingjun Zhao Created
1294 --===========================================================================
1295
1296 PROCEDURE Generate_Journal_Num(p_Period_Name IN VARCHAR2,
1297 p_Request_Id IN NUMBER,
1298 p_ledger_id in number,
1299 P_legal_entity_id in number) IS
1300 l_Request_Id NUMBER;
1301 l_Period_Name Gl_Periods.Period_Name%TYPE;
1302 l_Je_Header_Id NUMBER;
1303 l_Journal_Number NUMBER;
1304 l_Je_Appending_Id NUMBER;
1305 l_Line_Num_m NUMBER;
1306 l_ledger_id number;
1307 l_legal_entity_id number;
1308 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1309 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1310 l_Proc_Name VARCHAR2(100) := 'generate_journal_num';
1311
1312 CURSOR c_Journal IS
1313 SELECT Je_Header_Id
1314 FROM (SELECT DISTINCT Effective_Date, Je_Header_Id
1315 FROM Ja_Cn_Journals_Of_Period
1316 WHERE Request_Id = l_Request_Id
1317 AND Period_Name = l_Period_Name)
1318 ORDER BY Effective_Date ASC, Je_Header_Id ASC;
1319
1320 CURSOR c_Journal_Appending IS
1321 SELECT DISTINCT Je_Header_Id, Journal_Number
1322 FROM Ja_Cn_Journal_Lines Jl
1323 WHERE Je_Header_Id = l_Je_Header_Id
1324 AND Journal_Number IS NOT NULL
1325 AND Company_Segment IN
1326 (SELECT bsv.bal_seg_value
1327 FROM ja_cn_ledger_le_bsv_gt bsv
1328 WHERE Legal_Entity_Id = l_Legal_Entity_Id
1329 and ledger_id = l_ledger_id);
1330
1331 BEGIN
1332 --log for debug
1333 IF (l_Proc_Level >= l_Dbg_Level) THEN
1334 Fnd_Log.STRING(l_Proc_Level,
1335 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
1336 'Enter procedure');
1337 END IF; --(l_proc_level >= l_dbg_level)
1338
1339 l_Request_Id := p_Request_Id;
1340 l_Period_Name := p_Period_Name;
1341 l_legal_entity_id:=p_legal_entity_id;
1342 l_ledger_id:=p_ledger_id;
1343 OPEN c_Journal;
1344
1345 LOOP
1346 FETCH c_Journal
1347 INTO l_Je_Header_Id;
1348 EXIT WHEN c_Journal%NOTFOUND;
1349
1350 OPEN c_Journal_Appending;
1351 FETCH c_Journal_Appending
1352 INTO l_Je_Appending_Id, l_Journal_Number;
1353
1354 IF c_Journal_Appending%FOUND THEN
1355 CLOSE c_Journal_Appending;
1356 UPDATE Ja_Cn_Journal_Lines jop
1357 SET Journal_Number = l_Journal_Number
1358 WHERE Je_Header_Id = l_Je_Header_Id
1359 AND Journal_Number IS NULL
1360 AND Company_Segment IN
1361 (SELECT bsv.bal_seg_value
1362 FROM ja_cn_ledger_le_bsv_gt bsv
1363 WHERE Legal_Entity_Id = l_Legal_Entity_Id
1364 and ledger_id = l_ledger_id);
1365 ELSE
1366 CLOSE c_Journal_Appending;
1367 END IF; --c_journal_appending%FOUND
1368
1369 --get journal number based on legal entity and period
1370
1371 l_Journal_Number := Ja_Cn_Update_Jl_Seq_Pkg.Fetch_Jl_Seq(p_Legal_Entity_Id => l_Legal_Entity_Id,
1372 p_ledger_id=>l_ledger_id,
1373 p_Period_Name => l_Period_Name);
1374
1375 IF Nvl(l_Journal_Number, 0) > 0 THEN
1376 UPDATE Ja_Cn_Journal_Lines
1377 SET Journal_Number = l_Journal_Number
1378 WHERE Je_Header_Id = l_Je_Header_Id
1379 AND Company_Segment IN
1380 (SELECT bsv.bal_seg_value
1381 FROM ja_cn_ledger_le_bsv_gt bsv
1382 WHERE Legal_Entity_Id = l_Legal_Entity_Id
1383 and ledger_id = l_ledger_id);
1384 END IF;
1385
1386 END LOOP;
1387 CLOSE c_Journal;
1388 EXCEPTION
1389 WHEN OTHERS THEN
1390 IF (l_Proc_Level >= l_Dbg_Level) THEN
1391 Fnd_Log.STRING(l_Proc_Level,
1392 l_Module_Prefix || '.' || l_Proc_Name ||
1393 '. Other_Exception ',
1394 SQLCODE || ':' || SQLERRM);
1395 END IF; --(l_proc_level >= l_dbg_level)
1396 RAISE;
1397 END Generate_Journal_Num;
1398
1399 --==========================================================================
1400 -- PROCEDURE NAME:
1401 -- generate_journal_and_line_num Private
1402 --
1403 -- DESCRIPTION:
1404 -- This procedure is used to generate journal number
1405 -- and journal line number based on legal entity level and period
1406 -- PARAMETERS:
1407 -- In: p_period_name period
1408 -- p_request_id identifier of current session
1409 --
1410 -- DESIGN REFERENCES:
1411 -- None
1412 --
1413 -- CHANGE HISTORY:
1414 -- 02/21/2006 Qingjun Zhao Created
1415 -- 10/10/2009 Chaoqun Wu Fix bug 8970684
1416 -- 01/20/2010 Chaoqun Wu Updated for CNAOV2 solution
1417 -- 05/24/2010 Chaoqun Wu Fixed bug 9590798
1418 -- 07/14/2010 Chaoqun Wu Fixed bug 9872212
1419 -- 08/03/2010 Chaoqun Wu Fixed bug 9962344
1420 --===========================================================================
1421
1422 PROCEDURE Itemize_Journals_Sla(p_chart_of_accounts_id in number,
1423 p_Request_Id IN NUMBER) IS
1424 l_Request_Id NUMBER;
1425 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1426 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1427 l_Proc_Name VARCHAR2(100) := 'ITEMIZE_JOURNALS_SLA';
1428 l_Project_Source_Flag VARCHAR2(15);
1429 l_Project_Ac_Code VARCHAR2(30);
1430 l_Project_Ac_Detail_Code VARCHAR2(30);
1431 l_Grouping_Order NUMBER;
1432 l_Insertsql Dbms_Sql.Varchar2s;
1433 l_Line_No NUMBER := 0;
1434 l_Chart_Of_Accounts_Id number;
1435 l_sql varchar2(8000);
1436
1437 --Begin: Chaoqun updated for CNAOV2 on 01-20-2010
1438
1439 l_Insert_Header_Sql VARCHAR2(4000) := '';
1440 l_Insert_Values_Sql VARCHAR2(4000) := '';
1441 l_Project_Query_Table_Sql VARCHAR2(100) := '';
1442 l_Project_Query_Condition_Sql VARCHAR2(1000) := '';
1443 l_SM_Query_Header_Sql VARCHAR2(4000) := ''; --for settlement method
1444 l_SM_Grouping_Order NUMBER;
1445
1446 --Deleted by Chaoqun begin
1447 /* CURSOR c_Sub_Acc_Sources IS
1448 SELECT Sas.Project_Source_Flag,
1449 Sas.Project_Ac_Code,
1450 Sas.Project_Ac_Detail_Code,
1451 Sas.Ac_Grouping_Order--added for bug 6669665
1452 FROM Ja_Cn_Sub_Acc_Sources_All Sas
1453 WHERE Sas.Chart_Of_Accounts_Id = l_Chart_Of_Accounts_Id;*/
1454
1455 --Deleted by Chaoqun end
1456
1457 CURSOR c_Sub_Acc_Sources IS
1458 SELECT 'PA' PROJECT_SOURCE_FLAG,
1459 SAM.CONTEXT_CODE PROJECT_AC_CODE,
1460 XADB.GROUPING_ORDER AC_GROUPING_ORDER
1461 FROM JA_CN_SUB_ACC_MAPPING SAM,
1462 XLA_ANALYTICAL_DTLS_B XADB
1463 WHERE XADB.ANALYTICAL_CRITERION_CODE = SAM.CONTEXT_CODE
1464 AND SAM.SOURCES_CODE = 'PROJECT MODULE'
1465 AND SAM.CHART_OF_ACCOUNTS_ID = l_Chart_Of_Accounts_Id;
1466
1467 --Get subsidiary segment, sources and context defined in subsidiary account form
1468 --based on identifier of chart of account
1469 CURSOR c_Sub_Segment IS
1470 SELECT SAM.SUBSIDIARY_SEGMENT_CODE,
1471 SAM.SOURCES_CODE,
1472 SAM.CONTEXT_CODE
1473 FROM JA_CN_SUB_ACC_MAPPING SAM
1474 WHERE SAM.CHART_OF_ACCOUNTS_ID = l_Chart_Of_Accounts_Id;
1475 --End: Chaoqun added for CNAOV2 on 01-20-2010
1476
1477 BEGIN
1478 --log for debug
1479 IF (l_Proc_Level >= l_Dbg_Level) THEN
1480 Fnd_Log.STRING(l_Proc_Level,
1481 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
1482 'Enter procedure');
1483 END IF; --(l_proc_level >= l_dbg_level)
1484 l_Chart_Of_Accounts_Id := p_chart_of_accounts_id;
1485 l_Request_Id := p_Request_Id;
1486
1487 --Begin: Chaoqun added for CNAOV2 on 01-20-2010
1488
1489 --Deleted by Chaoqun begin
1490 /* OPEN c_Sub_Acc_Sources;
1491 FETCH c_Sub_Acc_Sources
1492 INTO l_Project_Source_Flag, l_Project_Ac_Code, l_Project_Ac_Detail_Code,l_Grouping_Order;--added for bug 6669665
1493 CLOSE c_Sub_Acc_Sources;*/
1494 --Deleted by Chaoqun end
1495 BEGIN
1496
1497 OPEN c_Sub_Acc_Sources;
1498 FETCH c_Sub_Acc_Sources
1499 INTO l_Project_Source_Flag, l_Project_Ac_Code, l_Grouping_Order;
1500 CLOSE c_Sub_Acc_Sources;
1501
1502 EXCEPTION
1503 WHEN OTHERS THEN
1504 NULL;
1505
1506 END;
1507
1508 -- For settlement method from SLA
1509 BEGIN
1510
1511 SELECT XADB.GROUPING_ORDER
1512 INTO l_SM_Grouping_Order
1513 FROM XLA_ANALYTICAL_DTLS_B XADB
1514 WHERE XADB.ANALYTICAL_CRITERION_CODE = 'SETTLEMENT METHOD';
1515
1516 EXCEPTION
1517 WHEN OTHERS THEN
1518 --NULL;
1519 l_SM_Grouping_Order := 1;
1520 END;
1521
1522 --Fetching settlement method from AP & AR
1523 l_SM_Query_Header_Sql := ' decode(jeh.JE_SOURCE,''Payables'','||
1524 ' (select acs.ac'||l_SM_Grouping_Order||
1525 ' from xla_ae_line_acs acs'||
1526 ' where Acs.Ae_Header_Id = Ael.Ae_Header_Id'||
1527 ' and Acs.Ae_Line_Num = Ael.Ae_Line_Num'||
1528 ' and Acs.analytical_criterion_code = ''JA_CN_SM'') ,'||
1529 ' ''Receivables'','||
1530 ' (select acs.ac'||l_SM_Grouping_Order||
1531 ' from xla_ae_line_acs acs'||
1532 ' where Acs.Ae_Header_Id = Ael.Ae_Header_Id'||
1533 ' and Acs.Ae_Line_Num = Ael.Ae_Line_Num'||
1534 ' and Acs.analytical_criterion_code = ''JA_CN_SM''),'||
1535 ' NULL) Settlement_Method_Number ';
1536
1537 FOR l_Sub_Segment IN c_Sub_Segment
1538 LOOP
1539
1540 l_Insert_Header_Sql := l_Insert_Header_Sql || l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ';
1541 --Populate values of COA segments defined in subsidiary account source form
1542 --from view JA_CN_CODE_COMBINATION_V
1543 IF l_Sub_Segment.SOURCES_CODE = 'COA' THEN
1544
1545 l_Insert_Values_Sql := l_Insert_Values_Sql || 'jcc.' ||
1546 l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ';
1547 --Populate values of SLA segments like customer, supplier and employee supplier
1548 -- defined in subsidiary account source form from view JA_CN_CODE_COMBINATION_V
1549 ELSIF l_Sub_Segment.Sources_Code = 'SLA' THEN
1550 IF l_Sub_Segment.CONTEXT_CODE = 'CUSTOMER' THEN
1551 l_Insert_Values_Sql := l_Insert_Values_Sql ||
1552 'Decode(Nvl(Ael.Party_Type_Code,''D''), ''C'', ' ||
1553 'Part.Party_Number, To_Char(NULL)) ' || l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ' ;
1554
1555 ELSIF l_Sub_Segment.CONTEXT_CODE = 'SUPPLIER' THEN
1556 l_Insert_Values_Sql := l_Insert_Values_Sql ||
1557 'Decode(vendor_type_lookup_code, ''EMPLOYEE'', to_char(null), '||
1558 'Decode(Nvl(Ael.Party_Type_Code,''D''), ''S'', ' ||
1559 'Sup.Segment1, To_Char(NULL))) ' || l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ' ;
1560
1561 ELSIF l_Sub_Segment.CONTEXT_CODE = 'EMPLOYEE_SUPPLIER' THEN --Updated for fixing bug 9962344
1562
1563 l_Insert_Values_Sql := l_Insert_Values_Sql ||
1564 'Decode(vendor_type_lookup_code, ''EMPLOYEE'', '||
1565 'TO_CHAR((SELECT EMPLOYEE_NUMBER FROM PER_PEOPLE_F WHERE PERSON_ID = sup.employee_id)), to_char(null)) '||
1566 l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ' ;
1567
1568 /* l_Insert_Values_Sql := l_Insert_Values_Sql || 'To_Char(NULL) ' ||
1569 l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ' ;*/
1570 END IF;
1571 --Populate values of PROJECT_NUMBER defined in subsidiary account source form
1572 --from view JA_CN_CODE_COMBINATION_V
1573 ELSIF l_Sub_Segment.SOURCES_CODE = 'PROJECT MODULE' THEN
1574 l_Insert_Values_Sql := l_Insert_Values_Sql || 'acs.ac' ||
1575 l_Grouping_Order || ' ' ||
1576 l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ' ;
1577
1578 END IF;
1579
1580 END LOOP;
1581
1582
1583 /* l_Line_No:=l_line_no+1;
1584 l_Insertsql:='*/
1585 if nvl(l_Project_Source_Flag, 'N') = 'PA' then
1586 l_Project_Query_Table_Sql := 'xla_ae_line_acs acs,';
1587 l_Project_Query_Condition_Sql := ' AND Acs.Ae_Header_Id(+) = Ael.Ae_Header_Id' ||
1588 ' AND Acs.Ae_Line_Num(+) = Ael.Ae_Line_Num ' ||
1589 ' AND Acs.analytical_criterion_code(+)='''||l_Project_Ac_Code||''' ';
1590 end if;
1591
1592 --get the detail code's group order in the analytical criterion
1593
1594 --deleted for bug 6669665
1595 -- SELECT Dtl.Grouping_Order
1596 -- INTO l_Grouping_Order
1597 -- FROM Xla_Analytical_Dtls_b Dtl
1598 -- WHERE Dtl.Analytical_Criterion_Code = l_Project_Ac_Code
1599 -- AND Dtl.Analytical_Detail_Code = l_Project_Ac_Detail_Code;
1600
1601 l_sql:='INSERT INTO Ja_Cn_Journal_Lines_Req';
1602 l_sql:=l_sql||' (Je_Header_Id,';
1603 l_sql:=l_sql||' Ledger_Id,';
1604 l_sql:=l_sql||' Legal_Entity_Id,';
1605 l_sql:=l_sql||' Journal_Number,';
1606 l_sql:=l_sql||' Je_Category,';
1607 l_sql:=l_sql||' Default_Effective_Date,';
1608 l_sql:=l_sql||' Period_Name,';
1609 l_sql:=l_sql||' Currency_Code,';
1610 l_sql:=l_sql||' Currency_Conversion_Rate,';
1611 l_sql:=l_sql||' Currency_Conversion_Type,'; --Added for fixing bug 9872212
1612 l_sql:=l_sql||' Je_Line_Num,';
1613 l_sql:=l_sql||' Line_Number,';
1614 l_sql:=l_sql||' Description,';
1615 l_sql:=l_sql||' Code_Combination_Id,';
1616 --Begin: Deleted by Chaoqun for CNAOV2
1617 l_sql:=l_sql||' Company_Segment,';
1618 /*l_sql:=l_sql||' Cost_Center,';*/
1619 l_sql:=l_sql||' Third_Party_Id,';
1620 /*l_sql:=l_sql||' Third_Party_Number,';*/
1621 l_sql:=l_sql||' third_party_type,';
1622 l_sql:=l_sql||' Personnel_Id,';
1623 /*l_sql:=l_sql||' Personnel_Number,';
1624 l_sql:=l_sql||' Project_Number,';
1625 l_sql:=l_sql||' Project_Source,';*/
1626 --End: Deleted by Chaoqun for CNAOV2
1627 l_sql:=l_sql||l_Insert_Header_Sql; -- Added by Chaoqun for CNAOV2
1628 l_sql:=l_sql||' Account_Segment,';
1629 l_sql:=l_sql||' Entered_Dr,';
1630 l_sql:=l_sql||' Entered_Cr,';
1631 l_sql:=l_sql||' Accounted_Dr,';
1632 l_sql:=l_sql||' Accounted_Cr,';
1633 l_sql:=l_sql||' Status,';
1634 l_sql:=l_sql||' Created_By,';
1635 l_sql:=l_sql||' Creation_Date,';
1636 l_sql:=l_sql||' Last_Updated_By,';
1637 l_sql:=l_sql||' Last_Update_Date,';
1638 l_sql:=l_sql||' Last_Update_Login,';
1639 l_sql:=l_sql||' Populate_Code,';
1640 l_sql:=l_sql||' Request_Id,';
1641 --for test
1642 --l_sql:='';
1643 l_sql:=l_sql||' Journal_Created_By,';
1644 l_sql:=l_sql||' Journal_Posted_By,';
1645 l_sql:=l_sql||' Settlement_Method_Number)';
1646 l_sql:=l_sql||' SELECT DISTINCT /*+index(ael,xla_ae_lines_n4)+*/';
1647 l_sql:=l_sql||' Jel.Je_Header_Id Je_Header_Id,';
1648 l_sql:=l_sql||' Jeh.Ledger_Id Ledger_Id,';
1649 l_sql:=l_sql||' Jop.Legal_Entity_Id Legal_Entity_Id,';
1650 l_sql:=l_sql||' To_Number(NULL) Journal_Number,';
1651 l_sql:=l_sql||' Jeh.Je_Category Je_Category,';
1652 l_sql:=l_sql||' Jeh.Default_Effective_Date Default_Effective_Date,';
1653 l_sql:=l_sql||' Jeh.Period_Name Period_Name,';
1654 l_sql:=l_sql||' Jeh.Currency_Code Currency_Code,';
1655 --l_sql:=l_sql||' Jeh.Currency_Conversion_Rate Currency_Conversion_Rate,';--udpate for fixing bug 9590798
1656 l_sql:=l_sql||' Ael.Currency_Conversion_Rate Currency_Conversion_Rate,';
1657 l_sql:=l_sql||' Nvl(Ael.Currency_Conversion_Type,Jeh.Currency_Conversion_Type) Currency_Conversion_Type,';--Added by Chaoqun for fixing bug 9872212
1658 l_sql:=l_sql||' Jel.Je_Line_Num Je_Line_Num,';
1659 l_sql:=l_sql||' To_Number(NULL) Line_Number,';
1660 l_sql:=l_sql||' Nvl(Ael.Description, Nvl(Jel.Description, Jeh.Description)) Description,';
1661 l_sql:=l_sql||' Jcc.Code_Combination_Id Code_Combination_Id,';
1662 --Begin: Deleted by Chaoqun for CNAOV2
1663 l_sql:=l_sql||' Jcc.Company_Segment Company_Segment,';
1664 /*l_sql:=l_sql||' Jcc.Cost_Segment Cost_Segment,';*/
1665 l_sql:=l_sql||' decode(vendor_type_lookup_code,'; --Updated for fixing bug 8970684
1666 l_sql:=l_sql||'''EMPLOYEE'''||',';
1667 l_sql:=l_sql||' to_number(null),';
1668 l_sql:=l_sql||' ael.party_id) Third_Party_Id,';
1669 -- l_sql:=l_sql||' to_char(null) third_party_number,';
1670 /* l_sql:=l_sql||' Decode(Nvl(Ael.Party_Type_Code, ';
1671 -- l_sql:='';
1672 l_sql:=l_sql||'''D'''||'), ';
1673 l_sql:=l_sql||'''C'''||',Part.Party_Number, ';
1674 l_sql:=l_sql||'''S'''||',Sup.Segment1,To_Char(NULL)) Third_Party_Number, ';*/
1675 l_sql:=l_sql||' decode(vendor_type_lookup_code,'; --Updated for fixing bug 8970684
1676 l_sql:=l_sql||' ''EMPLOYEE'''||',';
1677 l_sql:=l_sql||' to_char(null),';
1678 l_sql:=l_sql||' ael.party_type_code) third_party_type,';
1679 l_sql:=l_sql||' decode(vendor_type_lookup_code,'; --Updated for fixing bug 8970684
1680 l_sql:=l_sql||'''EMPLOYEE'''||',';
1681 l_sql:=l_sql||' sup.employee_id,';
1682 l_sql:=l_sql||' to_number(null)) Personnel_Id,';
1683 /*l_sql:=l_sql||' to_char(null) Personnel_Number,';
1684 l_sql:=l_sql||' acs.ac'||l_grouping_order;
1685 l_sql:=l_sql||' Project_Number,'''||l_Project_Option||''' Project_Source,';*/
1686 --End: Deleted by Chaoqun for CNAOV2
1687 l_sql:=l_sql||l_Insert_Values_Sql; -- Added by Chaoqun for CNAOV2
1688 l_sql:=l_sql||' Jcc.Account_Segment Account_Segment,';
1689 l_sql:=l_sql||' Ael.Entered_Dr,';
1690 l_sql:=l_sql||' Ael.Entered_Cr,';
1691 l_sql:=l_sql||' Ael.Accounted_Dr,';
1692 l_sql:=l_sql||' Ael.Accounted_Cr,';
1693 l_sql:=l_sql||'''U'''||' Status,';
1694 l_sql:=l_sql||' Fnd_Global.User_Id Created_Gy,';
1695 l_sql:=l_sql||' SYSDATE Creation_Date,';
1696 l_sql:=l_sql||' Fnd_Global.User_Id Last_Updated_By,';
1697 l_sql:=l_sql||' SYSDATE Last_Update_Date,';
1698 l_sql:=l_sql||' Fnd_Global.Login_Id Last_Update_Login,';
1699 l_sql:=l_sql||'''FSAH'''||',';
1700 l_sql:=l_sql||l_Request_Id||',';
1701 l_sql:=l_sql||' Jeh.Created_By,';
1702 l_sql:=l_sql||' jeb.posted_by,';
1703 l_sql:=l_sql|| l_SM_Query_Header_Sql; -- Added for fetching settlement method
1704 /* for bug 6654734
1705 l_sql:=l_sql||' Decode(Nvl(Jeh.Accrual_Rev_Status, '||'''N'''||'),';
1706 l_sql:=l_sql||'''R'''||',';
1707 ---for test
1708 -- l_sql:='';
1709
1710 l_sql:=l_sql||' To_Number(NULL),';
1711 l_sql:=l_sql||' Jeh.Last_Updated_By)';*/
1712 l_sql:=l_sql||' FROM Gl_Je_Lines Jel,';
1713 l_sql:=l_sql||' Gl_Je_Headers Jeh,';
1714 l_sql:=l_sql||' Gl_Je_Batches Jeb,';
1715 l_sql:=l_sql||' Xla_Ae_Lines Ael,';
1716 l_sql:=l_sql||' Xla_Ae_Headers Aeh,';
1717 l_sql:=l_sql||' Gl_Import_References Gir,';
1718 l_sql:=l_sql||' Ja_Cn_Code_Combination_v Jcc,';
1719 --l_sql:=l_sql||' xla_ae_line_acs acs,'; --Deleted by Chaoqun for CNAOV2
1720 l_sql:=l_sql||l_Project_Query_Table_Sql; -- Added by Chaoqun for CNAOV2
1721 l_sql:=l_sql||' ap_suppliers sup,';
1722 l_sql:=l_sql||' Ja_Cn_Journals_Of_Period Jop,';
1723 l_sql:=l_sql||' Hz_Cust_Accounts Cust,';
1724 l_sql:=l_sql||' Hz_Parties Part ';
1725 l_sql:=l_sql||' WHERE Jel.Je_Header_Id = Jeh.Je_Header_Id';
1726 l_sql:=l_sql||' AND Jeb.Je_Batch_Id=jeh.je_batch_id ';
1727 l_sql:=l_sql||' AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id';
1728 l_sql:=l_sql||' AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table';
1729 l_sql:=l_sql||' AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id';
1730 l_sql:=l_sql||' AND Gir.Je_Header_Id = Jeh.Je_Header_Id';
1731 l_sql:=l_sql||' and sup.vendor_id(+) = ael.party_id';
1732 l_sql:=l_sql||' AND Gir.Je_Line_Num = Jel.Je_Line_Num';
1733 --for test
1734 -- l_sql:='';
1735 l_sql:=l_sql||' AND Jop.Je_Header_Id = Jel.Je_Header_Id';
1736 l_sql:=l_sql||' AND Jop.Je_Line_Num = Jel.Je_Line_Num';
1737 l_sql:=l_sql||' AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id';
1738 l_sql:=l_sql||' and jop.request_id = '||l_request_id;
1739 -- l_sql:=l_sql||' AND Acs.Ae_Header_Id(+) = Ael.Ae_Header_Id'; --Deleted by Chaoqun for CNAOV2
1740 l_sql:=l_sql||' and jcc.ledger_id=jeh.ledger_id ';
1741 -- l_sql:=l_sql||' AND Acs.Ae_Line_Num(+) = Ael.Ae_Line_Num '; --Deleted by Chaoqun for CNAOV2
1742 l_sql:=l_sql||' and cust.cust_account_id(+)=ael.party_id ';
1743 l_sql:=l_sql||' and cust.party_id=part.party_id(+) ';
1744 -- l_sql:=l_sql||' AND Acs.analytical_criterion_code(+)='''||l_Project_Ac_Code||''' '; --Deleted by Chaoqun for CNAOV2
1745 l_sql:=l_sql||l_Project_Query_Condition_Sql; -- Added by Chqoqun for CNAOV2
1746
1747 execute immediate l_sql;
1748 /* else
1749 INSERT INTO Ja_Cn_Journal_Lines_Req
1750 (Je_Header_Id,
1751 Ledger_Id,
1752 Legal_Entity_Id,
1753 Journal_Number,
1754 Je_Category,
1755 Default_Effective_Date,
1756 Period_Name,
1757 Currency_Code,
1758 Currency_Conversion_Rate,
1759 Je_Line_Num,
1760 Line_Number,
1761 Description,
1762 --Company_Segment,
1763 Code_Combination_Id,||
1764 l_Insert_Header_Sql ||
1765 --Cost_Center,
1766 --Third_Party_Id,
1767 --Third_Party_Type,
1768 --Third_Party_Number,
1769 --Personnel_Id,
1770 --Personnel_Number,
1771 --Project_Number,
1772 --Project_Source,
1773
1774 Account_Segment,
1775 Entered_Dr,
1776 Entered_Cr,
1777 Accounted_Dr,
1778 Accounted_Cr,
1779 Status,
1780 Created_By,
1781 Creation_Date,
1782 Last_Updated_By,
1783 Last_Update_Date,
1784 Last_Update_Login,
1785 Populate_Code,
1786 Request_Id,
1787 Journal_Created_By,
1788 Journal_Posted_By)
1789 SELECT \*+index(ael,xla_ae_lines_n4)+*\
1790 Jel.Je_Header_Id Je_Header_Id
1791 ,Jeh.Ledger_Id Ledger_Id
1792 ,Jop.Legal_Entity_Id Legal_Entity_Id
1793 ,To_Number(NULL) Journal_Number
1794 ,Jeh.Je_Category Je_Category
1795 ,Jeh.Default_Effective_Date Default_Effective_Date
1796 ,Jeh.Period_Name Period_Name
1797 ,Jeh.Currency_Code Currency_Code
1798 ,Jeh.Currency_Conversion_Rate Currency_Conversion_Rate
1799 ,Jel.Je_Line_Num Je_Line_Num
1800 ,To_Number(NULL) Line_Number
1801 ,Nvl(Ael.Description,
1802 Nvl(Jel.Description,
1803 Jeh.Description)) Description
1804 ,Jcc.Company_Segment Company_Segment
1805 ,Jcc.Code_Combination_Id Code_Combination_Id
1806 ,Jcc.Cost_Segment Cost_Segment
1807 ,Decode(Pay_Group_Lookup_Code,
1808 'EMPLOYEE',
1809 To_Number(NULL),
1810 Ael.Party_Id) Third_Party_Id
1811 ,Decode(Pay_Group_Lookup_Code,
1812 'EMPLOYEE',
1813 To_Char(NULL),
1814 Ael.Party_Type_Code) Third_Party_Type
1815 ,Decode(Nvl(Ael.Party_Type_Code,
1816 'D'),
1817 'C',
1818 Part.Party_Number,
1819 'S',
1820 Sup.Segment1,
1821 To_Char(NULL)) Third_Party_Number
1822 ,Decode(Pay_Group_Lookup_Code,
1823 'EMPLOYEE',
1824 Sup.Employee_Id,
1825 To_Number(NULL)) Personnel_Id
1826 ,To_Char(NULL) Personnel_Number
1827 ,Decode(Nvl(l_Project_Option,
1828 'N'),
1829 'N',
1830 To_Char(NULL),
1831 'COA',
1832 Jcc.Project_Number,
1833 To_Char(NULL)) Project_Number
1834 ,Nvl(l_Project_Option,
1835 'N') Project_Source
1836 ,Jcc.Account_Segment Account_Segment
1837 ,Ael.Entered_Dr
1838 ,Ael.Entered_Cr
1839 ,Ael.Accounted_Dr
1840 ,Ael.Accounted_Cr
1841 ,'U' Status
1842 ,Fnd_Global.User_Id Created_Gy
1843 ,SYSDATE Creation_Date
1844 ,Fnd_Global.User_Id Last_Updated_By
1845 ,SYSDATE Last_Update_Date
1846 ,Fnd_Global.Login_Id Last_Update_Login
1847 ,'FSAH'
1848 ,l_Request_Id
1849 ,Jeh.Created_By
1850 ,jeb.posted_by
1851 ----deleted by lyb, for bug 6654734
1852 -- ,Decode(Nvl(Jeh.Accrual_Rev_Status,
1853 -- 'N'),
1854 -- 'R',
1855 -- To_Number(NULL),
1856 -- Jeh.Last_Updated_By)
1857 FROM Gl_Je_Lines Jel
1858 ,Gl_Je_Headers Jeh
1859 ,Gl_Je_Batches Jeb --added by lyb, for bug 6654734
1860 ,Xla_Ae_Lines Ael
1861 ,Xla_Ae_Headers Aeh
1862 ,Gl_Import_References Gir
1863 ,Ja_Cn_Code_Combination_v Jcc
1864 ,Ap_Suppliers Sup
1865 ,
1866 -- per_all_people_f per,
1867 Ja_Cn_Journals_Of_Period Jop
1868 ,Hz_Cust_Accounts Cust
1869 ,Hz_Parties Part
1870 WHERE Jel.Je_Header_Id = Jeh.Je_Header_Id
1871 AND Jeb.Je_Batch_Id=jeh.je_batch_id --added by lyb, for bug 6654734
1872 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
1873 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
1874 AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
1875 AND Gir.Je_Header_Id = Jeh.Je_Header_Id
1876 AND Sup.Vendor_Id(+) = Ael.Party_Id
1877 -- and sup.pay_group_lookup_code='EMPLOYEE'
1878 -- AND nvl(pv.employee_id, -1) = per.person_id(+)
1879 AND Gir.Je_Line_Num = Jel.Je_Line_Num
1880 AND Jop.Je_Header_Id = Jel.Je_Header_Id
1881 AND Jop.Je_Line_Num = Jel.Je_Line_Num
1882 AND Jcc.Ledger_Id = Jeh.Ledger_Id
1883 AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id
1884 AND Jop.Request_Id = l_Request_Id
1885 AND Cust.Cust_Account_Id(+) = Ael.Party_Id
1886 AND Cust.Party_Id = Part.Party_Id(+);
1887
1888 end if;*/
1889
1890 --End: Chaoqun updated for CNAOV2 on 01-20-2010
1891
1892 EXCEPTION
1893 WHEN OTHERS THEN
1894 IF (l_Proc_Level >= l_Dbg_Level) THEN
1895 Fnd_Log.STRING(l_Proc_Level,
1896 l_Module_Prefix || '.' || l_Proc_Name ||
1897 '. Other_Exception ',
1898 SQLCODE || ':' || SQLERRM);
1899 END IF; --(l_proc_level >= l_dbg_level)
1900 RAISE;
1901 END Itemize_Journals_Sla;
1902
1903 --=========================================================================
1904 -- PROCEDURE NAME:
1905 -- transfer_gl_sla_to_cnao Public
1906 --
1907 -- DESCRIPTION:
1908 -- This is main procedure through which other procedures are called
1909 -- according to source and category of journal.Then call generate
1910 -- journal number and journal line number procedure and call post
1911 -- program
1912 -- PARAMETERS:
1913 -- Out: errbuf Mandatory parameter for PL/SQL concurrent programs
1914 -- Out: retcode Mandatory parameter for PL/SQL concurrent programs
1915 -- In: p_period_name Accounting period name
1916 -- In: p_legal_entity_ID Legal entity id
1917 --
1918 -- DESIGN REFERENCES:
1919 -- None
1920 --
1921 -- CHANGE HISTORY:
1922 -- 02/21/2006 Qingjun Zhao Created
1923 -- 07/14/2010 Chaoqun Wu Fixed bug 9872212
1924 -- 01/12/2011 Chongwu Li CNAO V1 to V2 Upgrade
1925 --===========================================================================
1926
1927 PROCEDURE Transfer_Gl_Sla_To_Cnao(Errbuf OUT NOCOPY VARCHAR2,
1928 Retcode OUT NOCOPY VARCHAR2,
1929 p_Chart_Of_Accounts_Id IN NUMBER,
1930 p_Ledger_Id IN NUMBER,
1931 p_Legal_Entity_Id IN NUMBER,
1932 p_Period_Name IN VARCHAR2) IS
1933
1934 l_Error_Msg VARCHAR2(2000);
1935 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1936 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1937 l_Proc_Name VARCHAR2(100) := 'transfer_gl_sla_to_cnao';
1938 l_Phase VARCHAR2(100);
1939 l_Status VARCHAR2(100);
1940 l_Dev_Phase VARCHAR2(100);
1941 l_Dev_Status VARCHAR2(100);
1942 l_Message VARCHAR2(100);
1943 l_Till_Period_Name Gl_Periods.Period_Name%TYPE;
1944 l_Period_Name Gl_Periods.Period_Name%TYPE;
1945 l_Start_Period_Name Gl_Periods.Period_Name%TYPE;
1946 l_End_Period_Name Gl_Periods.Period_Name%TYPE;
1947 l_Conc_Succ BOOLEAN;
1948 l_Request_Id NUMBER;
1949 l_Chart_Of_Account_Id NUMBER;
1950 l_Ledger_Id NUMBER;
1951 l_Result BOOLEAN;
1952 l_Submit_Fail_Module VARCHAR2(100);
1953 l_Execution_Fail_Module VARCHAR2(100);
1954 l_Post_Con_Req_Id NUMBER := 0;
1955 l_Phase_Code Fnd_Lookup_Values.Lookup_Code%TYPE;
1956 l_Status_Code Fnd_Lookup_Values.Lookup_Code%TYPE;
1957 l_Request_Submit_Fail EXCEPTION;
1958 l_Request_Execution_Fail EXCEPTION;
1959 l_Post_Fail EXCEPTION;
1960 --Begin: Chaoqun updated for CNAOV2 on 01-20-2010
1961
1962 l_sql varchar2(4000) :='';
1963 l_Insert_Header_Sql VARCHAR2(4000) := '';
1964 l_Insert_Values_Sql VARCHAR2(4000) := '';
1965 --Get subsidiary segment, sources and context defined in subsidiary account form
1966 --based on identifier of chart of account
1967 CURSOR c_Sub_Segment IS
1968 SELECT SAM.SUBSIDIARY_SEGMENT_CODE,
1969 SAM.SOURCES_CODE,
1970 SAM.CONTEXT_CODE
1971 FROM JA_CN_SUB_ACC_MAPPING SAM
1972 WHERE SAM.CHART_OF_ACCOUNTS_ID = l_Chart_Of_Accounts_Id;
1973
1974
1975 /* CURSOR c_Project_Option IS
1976 SELECT Project_Source_Flag
1977 FROM Ja_Cn_Sub_Acc_Sources_All
1978 WHERE Chart_Of_Accounts_Id = l_Chart_Of_Accounts_Id;*/
1979
1980 --End: Chaoqun updateed for CNAOV2 on 01-20-2010
1981
1982 CURSOR c_Period_Name IS
1983 SELECT Gp.Period_Name
1984 FROM Gl_Periods Gp, Gl_Ledgers Led
1985 WHERE Led.Ledger_Id = l_Ledger_Id
1986 AND Led.Period_Set_Name = Gp.Period_Set_Name
1987 AND Led.Accounted_Period_Type = Gp.Period_Type
1988 AND Gp.Start_Date BETWEEN
1989 (SELECT Start_Date
1990 FROM Gl_Periods Gp
1991 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
1992 AND Led.Accounted_Period_Type = Gp.Period_Type
1993 AND Gp.Period_Name = l_Start_Period_Name)
1994 AND (SELECT Start_Date
1995 FROM Gl_Periods Gp
1996 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
1997 AND Led.Accounted_Period_Type = Gp.Period_Type
1998 AND Gp.Period_Name = l_End_Period_Name)
1999 ORDER BY Gp.Start_Date;
2000 BEGIN
2001
2002 --log for debug
2003 IF (l_Proc_Level >= l_Dbg_Level) THEN
2004 Fnd_Log.STRING(l_Proc_Level,
2005 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
2006 'Enter procedure');
2007 Fnd_Log.STRING(l_Proc_Level,
2008 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
2009 'p_period_name:' || p_Period_Name);
2010 Fnd_Log.STRING(l_Proc_Level,
2011 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
2012 'p_legal_entity_id:' || p_Legal_Entity_Id);
2013 END IF; --(l_proc_level >= l_dbg_level)
2014
2015 --call JA_CN_UTILITY.Check_Profile, if it doesn't return true, exit
2016 /* IF Ja_Cn_Utility.Check_Profile() <> TRUE THEN
2017
2018 --log for debug
2019 IF (l_Proc_Level >= l_Dbg_Level) THEN
2020 Fnd_Log.STRING(l_Proc_Level, l_Dbg_Level, 'Check profile failed!');
2021 END IF; --(l_proc_level >= l_dbg_level)
2022 l_Conc_Succ := Fnd_Concurrent.Set_Completion_Status(Status => 'WARNING',
2023 Message => '');
2024 RETURN;
2025 END IF; */ --JA_CN_UTILITY.Check_Profile() != TRUE
2026
2027 l_Till_Period_Name := p_Period_Name;
2028 l_Legal_Entity_Id := p_Legal_Entity_Id;
2029 l_ledger_id := p_Ledger_Id;
2030
2031 --log for debug
2032 IF (l_Proc_Level >= l_Dbg_Level) THEN
2033 Fnd_Log.STRING(l_Proc_Level,
2034 l_Module_Prefix || '.' || l_Proc_Name ||
2035 '.current set of books id',
2036 'set of book id is ' || l_Ledger_Id);
2037 END IF; --l_exception_level >= l_runtime_level
2038
2039 l_chart_of_accounts_id := p_chart_of_accounts_id;
2040
2041 --Begin: Chaoqun deleted for CNAOV2 on 01-21-2010
2042
2043 /* --Get "Project" definition in global_attribute1 of led
2044 OPEN c_Project_Option;
2045 FETCH c_Project_Option
2046 INTO l_Project_Option;
2047
2048 --if "Project" isn't defined,then consider "Project"
2049 --as "Project Not considered"--'N'
2050 IF (c_Project_Option%NOTFOUND) THEN
2051 l_Project_Option := 'N';
2052 END IF; --(c_project_option%NOTFOUND)
2053
2054 --log for debug
2055 IF (l_Proc_Level >= l_Dbg_Level) THEN
2056 Fnd_Log.STRING(l_Proc_Level,
2057 l_Module_Prefix || '.' || l_Proc_Name ||
2058 '.setup information',
2059 'project option is ' || l_Project_Option);
2060 END IF; --l_exception_level >= l_runtime_level
2061
2062 CLOSE c_Project_Option;*/
2063
2064 --End: Chaoqun deleted for CNAOV2 on 01-21-2010
2065
2066 --generate current session identifier
2067 SELECT Ja_Cn_Journal_Lines_Req_s.NEXTVAL INTO l_Request_Id FROM Dual;
2068
2069 --generate code combination view
2070 Generate_Code_Combination_View(p_ledger_id => l_ledger_id);
2071
2072 --Get effective begin period and end period;
2073 Get_Period_Range(p_Period_Name => l_Till_Period_Name,
2074 p_ledger_id => l_ledger_id,
2075 p_Start_Period_Name => l_Start_Period_Name,
2076 p_End_Period_Name => l_End_Period_Name);
2077
2078 --Populate journal lines which will be itemized by possible subsidiary
2079 -- between start period and end period
2080
2081 Populate_Journal_Of_Period(p_Start_Period => l_Start_Period_Name,
2082 p_ledger_id => l_ledger_id,
2083 p_legal_entity_id => l_legal_entity_id,
2084 p_End_Period => l_End_Period_Name,
2085 p_Request_Id => l_Request_Id);
2086
2087 Itemize_Journals_Sla(p_chart_of_accounts_id => l_chart_of_accounts_id,
2088 p_request_id => l_request_id);
2089
2090 Purge_Unmatch_Lines(p_Request_Id => l_Request_Id);
2091
2092 --Transfer directly journal lines, which cannt be itmized by above
2093 --Itemization concurrent programs, into CNAO system
2094 Unitemize_Journal_Lines(p_chart_of_accounts_id => l_chart_of_accounts_id, --Updated by Chaoqun for CNAOV2
2095 p_Request_Id => l_Request_Id/*,
2096 p_Project_Option => l_Project_Option*/);
2097
2098 --get approver of journals that have been approved in General Ledger
2099 Get_Journal_Approver(p_Request_Id => l_Request_Id);
2100
2101 --get creator's name of journal
2102 UPDATE JA_CN_JOURNAL_LINES_REQ REQ
2103 SET REQ.JOURNAL_CREATOR = (SELECT LAST_NAME || FIRST_NAME FULL_NAME
2104 FROM PER_ALL_PEOPLE_F
2105 WHERE PERSON_ID =
2106 (SELECT EMPLOYEE_ID
2107 FROM FND_USER
2108 WHERE USER_ID =
2109 REQ.JOURNAL_CREATED_BY)
2110 AND REQ.DEFAULT_EFFECTIVE_DATE BETWEEN
2111 EFFECTIVE_START_DATE AND
2112 EFFECTIVE_END_DATE)
2113 WHERE REQ.REQUEST_ID = L_REQUEST_ID
2114 AND REQ.JE_HEADER_ID > 0;
2115 --get poster's name of journals
2116 UPDATE JA_CN_JOURNAL_LINES_REQ REQ
2117 SET REQ.JOURNAL_POSTER = (SELECT LAST_NAME || FIRST_NAME FULL_NAME
2118 FROM PER_ALL_PEOPLE_F
2119 WHERE PERSON_ID =
2120 (SELECT EMPLOYEE_ID
2121 FROM FND_USER
2122 WHERE USER_ID =
2123 REQ.JOURNAL_POSTED_BY)
2124 AND REQ.DEFAULT_EFFECTIVE_DATE BETWEEN
2125 EFFECTIVE_START_DATE AND
2126 EFFECTIVE_END_DATE)
2127 WHERE REQ.REQUEST_ID = L_REQUEST_ID
2128 AND REQ.JE_HEADER_ID > 0;
2129 COMMIT;
2130
2131 --update journal line into status of itemizated
2132 UPDATE Gl_Je_Lines Jel
2133 SET Jel.Global_Attribute7 = 'P' --Updated for CNAOV2 solution by chaoqun on 13-May-2010
2134 WHERE Jel.Je_Line_Num IN
2135 (SELECT Je_Line_Num
2136 FROM Ja_Cn_Journals_Of_Period
2137 WHERE Request_Id = l_Request_Id
2138 AND Je_Header_Id = Jel.Je_Header_Id)
2139 AND Jel.Je_Header_Id IN
2140 (SELECT Je_Header_Id
2141 FROM Ja_Cn_Journals_Of_Period
2142 WHERE Request_Id = l_Request_Id);
2143
2144 --Begin: Chaoqun updated for CNAOV2 on 01-21-2010
2145
2146 FOR l_Sub_Segment IN c_Sub_Segment
2147 LOOP
2148
2149 l_Insert_Header_Sql := l_Insert_Header_Sql || l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ';
2150
2151 END LOOP;
2152
2153 l_Insert_Values_Sql := l_Insert_Header_Sql;
2154
2155
2156 --transfer itemized data into ja_cn_journal_lines in this session
2157
2158 --Begin: Delted by Chaoqun for CNAOV2
2159 /* INSERT INTO Ja_Cn_Journal_Lines
2160 (Je_Header_Id,
2161 Ledger_Id,
2162 Legal_Entity_Id,
2163 Journal_Number,
2164 Je_Category,
2165 Default_Effective_Date,
2166 Period_Name,
2167 Currency_Code,
2168 Currency_Conversion_Rate,
2169 Je_Line_Num,
2170 Line_Number,
2171 Description,
2172 Company_Segment,
2173 Code_Combination_Id,
2174 Cost_Center,
2175 Third_Party_Id,
2176 Third_Party_Number,
2177 Third_Party_Type,
2178 Personnel_Id,
2179 Personnel_Number,
2180 Project_Number,
2181 Project_Source,
2182 Account_Segment,
2183 Entered_Dr,
2184 Entered_Cr,
2185 Accounted_Dr,
2186 Accounted_Cr,
2187 Status,
2188 Created_By,
2189 Creation_Date,
2190 Last_Updated_By,
2191 Last_Update_Date,
2192 Last_Update_Login,
2193 Populate_Code,
2194 Journal_Creator,
2195 Journal_Approver,
2196 Journal_Poster)
2197 SELECT Je_Header_Id,
2198 Ledger_Id,
2199 Legal_Entity_Id,
2200 Journal_Number,
2201 Je_Category,
2202 Default_Effective_Date,
2203 Period_Name,
2204 Currency_Code,
2205 Currency_Conversion_Rate,
2206 Je_Line_Num,
2207 Line_Number,
2208 Description,
2209 Company_Segment,
2210 Code_Combination_Id,
2211 Cost_Center,
2212 Third_Party_Id,
2213 Third_Party_Number,
2214 Third_Party_Type,
2215 Personnel_Id,
2216 Personnel_Number,
2217 Project_Number,
2218 Project_Source,
2219 Account_Segment,
2220 Entered_Dr,
2221 Entered_Cr,
2222 Accounted_Dr,
2223 Accounted_Cr,
2224 Status,
2225 Created_By,
2226 Creation_Date,
2227 Last_Updated_By,
2228 Last_Update_Date,
2229 Last_Update_Login,
2230 Populate_Code,
2231 Journal_Creator,
2232 Journal_Approver,
2233 Journal_Poster
2234 FROM Ja_Cn_Journal_Lines_Req
2235 WHERE Request_Id = l_Request_Id
2236 AND Je_Header_Id > 0;*/
2237 --End: Deleted by Chaoqun for CNAOV2
2238
2239 l_sql:='INSERT INTO Ja_Cn_Journal_Lines';
2240 l_sql:=l_sql||' (Je_Header_Id,';
2241 l_sql:=l_sql||' Ledger_Id,';
2242 l_sql:=l_sql||' Legal_Entity_Id,';
2243 l_sql:=l_sql||' Journal_Number,';
2244 l_sql:=l_sql||' Je_Category,';
2245 l_sql:=l_sql||' Default_Effective_Date,';
2246 l_sql:=l_sql||' Period_Name,';
2247 l_sql:=l_sql||' Currency_Code,';
2248 l_sql:=l_sql||' Currency_Conversion_Rate,';
2249 l_sql:=l_sql||' Currency_Conversion_Type,'; --Added for fixing bug 9872212
2250 l_sql:=l_sql||' Je_Line_Num,';
2251 l_sql:=l_sql||' Line_Number,';
2252 l_sql:=l_sql||' Description,';
2253 l_sql:=l_sql||' Company_Segment,';
2254 l_sql:=l_sql||' Code_Combination_Id,';
2255 --l_sql:=l_sql||' Cost_Center,';
2256 l_sql:=l_sql||' Third_Party_Id,';
2257 -- l_sql:=l_sql||' Third_Party_Number,';
2258 l_sql:=l_sql||' Third_Party_Type,';
2259 l_sql:=l_sql||' Personnel_Id,';
2260 --l_sql:=l_sql||' Personnel_Number,';
2261 --l_sql:=l_sql||' Project_Number,';
2262 --l_sql:=l_sql||' Project_Source,';
2263 l_sql:=l_sql||l_Insert_Header_Sql;
2264 l_sql:=l_sql||' Account_Segment,';
2265 l_sql:=l_sql||' Entered_Dr,';
2266 l_sql:=l_sql||' Entered_Cr,';
2267 l_sql:=l_sql||' Accounted_Dr,';
2268 l_sql:=l_sql||' Accounted_Cr,';
2269 l_sql:=l_sql||' Status,';
2270 l_sql:=l_sql||' Created_By,';
2271 l_sql:=l_sql||' Creation_Date,';
2272 l_sql:=l_sql||' Last_Updated_By,';
2273 l_sql:=l_sql||' Last_Update_Date,';
2274 l_sql:=l_sql||' Last_Update_Login,';
2275 l_sql:=l_sql||' Populate_Code,';
2276 l_sql:=l_sql||' Journal_Creator,';
2277 l_sql:=l_sql||' Journal_Approver,';
2278 l_sql:=l_sql||' Journal_Poster,';
2279 l_sql:=l_sql||' Settlement_Method_Number)';
2280 l_sql:=l_sql||' SELECT Je_Header_Id,';
2281 l_sql:=l_sql||' Ledger_Id,';
2282 l_sql:=l_sql||' Legal_Entity_Id,';
2283 l_sql:=l_sql||' Journal_Number,';
2284 l_sql:=l_sql||' Je_Category,';
2285 l_sql:=l_sql||' Default_Effective_Date,';
2286 l_sql:=l_sql||' Period_Name,';
2287 l_sql:=l_sql||' Currency_Code,';
2288 l_sql:=l_sql||' Currency_Conversion_Rate,';
2289 l_sql:=l_sql||' Currency_Conversion_Type,'; --Added for fixing bug 9872212
2290 l_sql:=l_sql||' Je_Line_Num,';
2291 l_sql:=l_sql||' Line_Number,';
2292 l_sql:=l_sql||' Description,';
2293 l_sql:=l_sql||' Company_Segment,';
2294 l_sql:=l_sql||' Code_Combination_Id,';
2295 --l_sql:=l_sql||' Cost_Center,';
2296 l_sql:=l_sql||' Third_Party_Id,';
2297 --l_sql:=l_sql||' Third_Party_Number,';
2298 l_sql:=l_sql||' Third_Party_Type,';
2299 l_sql:=l_sql||' Personnel_Id,';
2300 --l_sql:=l_sql||' Personnel_Number,';
2301 --l_sql:=l_sql||' Project_Number,';
2302 --l_sql:=l_sql||' Project_Source,';
2303 l_sql:=l_sql||l_Insert_Values_Sql;
2304 l_sql:=l_sql||' Account_Segment,';
2305 l_sql:=l_sql||' Entered_Dr,';
2306 l_sql:=l_sql||' Entered_Cr,';
2307 l_sql:=l_sql||' Accounted_Dr,';
2308 l_sql:=l_sql||' Accounted_Cr,';
2309 l_sql:=l_sql||' Status,';
2310 l_sql:=l_sql||' Created_By,';
2311 l_sql:=l_sql||' Creation_Date,';
2312 l_sql:=l_sql||' Last_Updated_By,';
2313 l_sql:=l_sql||' Last_Update_Date,';
2314 l_sql:=l_sql||' Last_Update_Login,';
2315 l_sql:=l_sql||' Populate_Code,';
2316 l_sql:=l_sql||' Journal_Creator,';
2317 l_sql:=l_sql||' Journal_Approver,';
2318 l_sql:=l_sql||' Journal_Poster,';
2319 l_sql:=l_sql||' Settlement_Method_Number';
2320 l_sql:=l_sql||' FROM Ja_Cn_Journal_Lines_Req';
2321 l_sql:=l_sql||' WHERE Request_Id = '||l_Request_Id;
2322 l_sql:=l_sql||' AND Je_Header_Id > 0';
2323
2324 execute immediate l_sql;
2325 --End: Chaoqun updated for CNAOV2 on 01-21-2010
2326 --get journal approver for itemized journal lines
2327 --which journal source need been approved
2328
2329 --generate journal number and journal line number
2330 --based on legal entity level
2331 OPEN c_Period_Name;
2332
2333 LOOP
2334 FETCH c_Period_Name
2335 INTO l_Period_Name;
2336 EXIT WHEN c_Period_Name%NOTFOUND;
2337 -- generate journal number and journal line number
2338 -- chongwu change for CNAO V1 to V2 upgrade.
2339 /* Generate_Journal_Num(p_Period_Name => l_Period_Name,
2340 p_Request_Id => l_Request_Id,
2341 p_ledger_id =>p_ledger_id,
2342 p_legal_entity_id =>p_legal_entity_id);*/
2343 JA_CN_VOUCHER_NUM_PKG.Generate_voucher_number(pn_ledger_id =>p_ledger_id,
2344 pn_legal_entity_id =>p_legal_entity_id,
2345 pv_period_name => l_Period_Name,
2346 pv_regenerate_flag => 'N');
2347
2348 END LOOP;
2349
2350 --commit itemized journal lines with journal number and journal line number
2351 COMMIT;
2352
2353 --Delete temparory data of current session
2354 /* DELETE FROM Ja_Cn_Journals_Of_Period
2355 WHERE Request_Id = l_Request_Id
2356 AND Je_Header_Id > 0;*/
2357
2358 DELETE FROM Ja_Cn_Journal_Lines_Req
2359 WHERE Request_Id = l_Request_Id
2360 AND Je_Header_Id > 0;
2361 COMMIT;
2362
2363 --call post program to post these journals itemized
2364 l_Post_Con_Req_Id := Fnd_Request.Submit_Request('JA', --Comment by Chaoqun for test
2365 'JACNPOST',
2366 NULL,
2367 To_Date(NULL),
2368 FALSE,
2369 l_Period_Name,
2370 l_Ledger_Id,
2371 l_Legal_Entity_Id);
2372
2373 IF (l_Post_Con_Req_Id = 0) THEN
2374 Errbuf := Fnd_Message.Get;
2375 Retcode := 2;
2376 l_Submit_Fail_Module := 'General Legder';
2377 RAISE l_Post_Fail;
2378 END IF; --(l_gl_con_req_id = 0)
2379 --submit post request
2380 COMMIT;
2381 IF l_Post_Con_Req_Id <> 0 THEN
2382 l_Result := Fnd_Concurrent.Wait_For_Request(l_Post_Con_Req_Id,
2383 60,
2384 -1,
2385 l_Phase,
2386 l_Status,
2387 l_Dev_Phase,
2388 l_Dev_Status,
2389 l_Message);
2390
2391 IF l_Result = FALSE THEN
2392 Errbuf := Fnd_Message.Get;
2393 Retcode := 2;
2394 l_Execution_Fail_Module := 'Post';
2395
2396 --log for debug
2397 IF (l_Proc_Level >= l_Dbg_Level) THEN
2398 Fnd_Log.STRING(l_Proc_Level,
2399 l_Module_Prefix || '.' || l_Proc_Name ||
2400 '.JACNGLJT.EXECUTION',
2401 'l_status.' || l_Status || '--' || 'l_phase.' ||
2402 l_Phase);
2403 END IF; --l_exception_level >= l_runtime_level
2404
2405 END IF; --l_result = FALSE
2406
2407 END IF; --l_post_con_req_id <> 0
2408
2409 --log for debug
2410 IF (l_Proc_Level >= l_Dbg_Level) THEN
2411 Fnd_Log.STRING(l_Proc_Level,
2412 l_Module_Prefix || '.' || l_Proc_Name || '.end',
2413 'Exit procedure');
2414 END IF; --( l_proc_level >= l_dbg_level )
2415
2416 EXCEPTION
2417 WHEN l_Post_Fail THEN
2418 -- dbms_output.put_line('Post Program fails,please connect your system ');
2419 --log for debug
2420 IF (l_Proc_Level >= l_Dbg_Level) THEN
2421 Fnd_Log.STRING(l_Proc_Level,
2422 l_Module_Prefix || '.' || l_Proc_Name ||
2423 '. Other_Exception ',
2424 SQLCODE || ':' || SQLERRM);
2425 END IF; --(l_proc_level >= l_dbg_level)
2426 l_Conc_Succ := Fnd_Concurrent.Set_Completion_Status(Status => 'ERROR',
2427 Message => SQLCODE || ':' ||
2428 SQLERRM);
2429 WHEN OTHERS THEN
2430 ROLLBACK;
2431 DELETE FROM Ja_Cn_Journals_Of_Period
2432 WHERE Request_Id = l_Request_Id
2433 AND Je_Header_Id > 0;
2434
2435 DELETE FROM Ja_Cn_Journal_Lines_Req
2436 WHERE Request_Id = l_Request_Id
2437 AND Je_Header_Id > 0;
2438 COMMIT;
2439 Fnd_File.Put_Line(Fnd_File.Log, SQLCODE || ':' || SQLERRM);
2440 l_Conc_Succ := Fnd_Concurrent.Set_Completion_Status(Status => 'ERROR',
2441 Message => SQLCODE || ':' ||
2442 SQLERRM);
2443 --log for debug
2444 IF (l_Proc_Level >= l_Dbg_Level) THEN
2445 Fnd_Log.STRING(l_Proc_Level,
2446 l_Module_Prefix || '.' || l_Proc_Name ||
2447 '. Other_Exception ',
2448 SQLCODE || ':' || SQLERRM);
2449 END IF; --(l_proc_level >= l_dbg_level)
2450
2451 END Transfer_Gl_Sla_To_Cnao;
2452
2453 END Ja_Cn_Acc_Je_Itemization_Pkg;