[Home] [Help]
PACKAGE BODY: APPS.JA_CN_CFS_CLT_SLA_PKG
Source
1 package body JA_CN_CFS_CLT_SLA_PKG AS
2 --$Header: JACNSLAB.pls 120.24.12020000.3 2012/09/29 03:10:48 chongwan ship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNSLAB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used in Collecting CFS Data from SLA |
13 --| in the CNAO Project. |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Collect_SLA_Data PUBLIC |
17 --| PROCEDURE put_line PRIVATE |
18 --| PROCEDURE put_log PRIVATE |
19 --| FUNCTION get_period_name PUBLIC |
20 --| |
21 --| HISTORY |
22 --| 23/04/2004 Shujuan Yan Created |
23 --| 08/09/2008 Yao Zhang Fix Bug#7334017 for R12 enhancment |
24 --| 09/11/2008 Yao Zhang Fix bug#7535144 |
25 --| 06/01/2009 Shujuan Yan Fix bug 8370270,8395396,8395408 |
26 --| and 8395411 for AP void and AR |
27 --| reverse and Unclearing |
28 --| 29/09/2009 Chaoqun Wu Fix bug 8969631 for cancelled |
29 --| payment and reversed receipt. |
30 --| 2/2/2010 Shujuan Yan Add collect GL Journar header id |
31 --| and GL Journal Line Number for |
32 --| CNAO V2
33 --| |
34 --| 30/11/2010 Chuanling Shao Fix Bug#10316509,add extra condition|
35 --| ledgerId when we collect GL Journal|
36 --| header id and GL Journal Line |
37 --| Number for CNAO V2 |
38 --| 21/02/2011 Chongwu Li Fix bug 11777674 VOIDED PAYMENT |
39 --| CANNOT BE COLLECTED IN CASH FLOW |
40 --| STATEMENT. |
41 --| 18/03/2011 Chongwu Li Fix bug 11872243 CASH FLOW STATEMENT|
42 --| PROGRAM - DATA COLLECTION" ENDED |
43 --| WITH ERROR |
44 --| 22/06/2011 Chongwu Li Fix bug 12639420 TST122.XB4.QA. |
45 --| RECEIPT CLEAR THEN UNCLEAR IN SAME |
46 --| PERIOD CASH FLOW JOURNAL WRONG |
47 --| 19/10/2011 Shujuan Yan Fix bug 12542469 Cash Flow Statement|
48 --| Collect non cash account transaction|
49 --| 31/01/2012 Shujuan Yan Fix bug 13498675 CFS COLLECTION |
50 --| PROGRAM COLLECT VOID PAYMENT DATA TWICE|
51 --| 28/09/2012 Jar Wang Fix bug 14664279 add description column
52 --+======================================================================*/
53 --==========================================================================
54 -- PROCEDURE NAME:
55 -- Put_Line private
56 --
57 -- DESCRIPTION:
58 -- This procedure write data to log file.
59 --
60 -- PARAMETERS:
61 -- In: p_str VARCHAR2
62 --
63 -- DESIGN REFERENCES:
64 -- None
65 --
66 -- CHANGE HISTORY:
67 -- 23/04/2007 Shujuan Yan Created
68 --===========================================================================
69 PROCEDURE put_log(p_module IN VARCHAR2, p_message IN VARCHAR2) AS
70 BEGIN
71 IF (fnd_log.LEVEL_STATEMENT >= g_debug_devel) THEN
72 fnd_log.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
73 MODULE => p_module,
74 MESSAGE => p_message);
75 END IF;
76
77 END put_log;
78 --==========================================================================
79 -- PROCEDURE NAME:
80 -- Put_Line private
81 --
82 -- DESCRIPTION:
83 -- This procedure write data to concurrent output file.
84 --
85 -- PARAMETERS:
86 -- In: p_str VARCHAR2
87 --
88 -- DESIGN REFERENCES:
89 -- None
90 --
91 -- CHANGE HISTORY:
92 -- 23/04/2007 Shujuan Yan Created
93 --===========================================================================
94 PROCEDURE put_line(p_str IN VARCHAR2) AS
95 BEGIN
96 FND_FILE.Put_Line(FND_FILE.Output, p_str);
97 END put_line;
98 --==========================================================================
99 -- FUNCTION NAME:
100 -- get_period_name Public
101 --
102 -- DESCRIPTION:
103 -- This FUNCTION is used to get period name from a period set and given date
104 -- the period name is month type
105 --
106 -- PARAMETERS:
107 -- In: p_period_set_name period set name
108 -- p_gl_date date
109 -- p_period_type period type
110 -- return: period name
111 --
112 -- DESIGN REFERENCES:
113 -- None
114 --
115 -- CHANGE HISTORY:
116 -- 23/04/2007 Shujuan Yan Created
117 --===========================================================================
118 FUNCTION get_period_name(p_period_set_name IN VARCHAR2,
119 p_gl_date IN DATE,
120 p_period_type IN VARCHAR2) RETURN VARCHAR2 AS
121 l_period_name VARCHAR2(30);
122 BEGIN
123 SELECT period_name
124 INTO l_period_name
125 FROM gl_periods
126 WHERE period_set_name = p_period_set_name
127 AND start_date <= p_gl_date
128 AND End_Date >= p_gl_date
129 AND period_type = p_period_type
130 AND adjustment_period_flag = 'N';
131
132 RETURN l_period_name;
133
134 END get_period_name;
135
136
137 -- Fix bug#7334017 add begin
138 --==========================================================================
139 -- PROCEDURE NAME:
140 -- get_balancing_segment private
141 --
142 -- DESCRIPTION:
143 -- This procedure returns the balancing segment value of a CCID.
144 --
145 -- PARAMETERS:
146 -- In: P_CC_ID NUMBER
147 --
148 -- DESIGN REFERENCES:
149 -- None
150 --
151 -- CHANGE HISTORY:
152 -- 03/09/2008 Yao Zhang Created
153 --===========================================================================
154 FUNCTION get_balancing_segment
155 ( P_CC_ID IN NUMBER
156 )
157 RETURN VARCHAR2
158 IS
159 L_BALANCING_SEGMENT GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
160 BEGIN
161 SELECT
162 DECODE(FSAV.APPLICATION_COLUMN_NAME, --segment FSAV.APPLICATION_COLUMN_NAME of gcc
163 'SEGMENT1',GCC.SEGMENT1, 'SEGMENT2',GCC.SEGMENT2, 'SEGMENT3',GCC.SEGMENT3,
164 'SEGMENT4',GCC.SEGMENT4, 'SEGMENT5',GCC.SEGMENT5, 'SEGMENT6',GCC.SEGMENT6,
165 'SEGMENT7',GCC.SEGMENT7, 'SEGMENT8',GCC.SEGMENT8, 'SEGMENT9',GCC.SEGMENT9,
166 'SEGMENT10',GCC.SEGMENT10, 'SEGMENT11',GCC.SEGMENT11, 'SEGMENT12',GCC.SEGMENT12,
167 'SEGMENT13',GCC.SEGMENT13, 'SEGMENT14',GCC.SEGMENT14, 'SEGMENT15',GCC.SEGMENT15,
168 'SEGMENT16',GCC.SEGMENT16, 'SEGMENT17',GCC.SEGMENT17, 'SEGMENT18',GCC.SEGMENT18,
169 'SEGMENT19',GCC.SEGMENT19, 'SEGMENT20',GCC.SEGMENT20, 'SEGMENT21',GCC.SEGMENT21,
170 'SEGMENT22',GCC.SEGMENT22, 'SEGMENT23',GCC.SEGMENT23, 'SEGMENT24',GCC.SEGMENT24,
171 'SEGMENT25',GCC.SEGMENT25, 'SEGMENT26',GCC.SEGMENT26, 'SEGMENT27',GCC.SEGMENT27,
172 'SEGMENT28',GCC.SEGMENT28, 'SEGMENT29',GCC.SEGMENT29, 'SEGMENT30',GCC.SEGMENT30)
173 INTO L_BALANCING_SEGMENT
174 FROM GL_CODE_COMBINATIONS GCC,
175 FND_SEGMENT_ATTRIBUTE_VALUES FSAV
176 WHERE GCC.CODE_COMBINATION_ID = P_CC_ID
177 AND FSAV.ATTRIBUTE_VALUE = 'Y'
178 AND FSAV.APPLICATION_ID = 101
179 AND FSAV.ID_FLEX_CODE = 'GL#'--Fix bug#7334017 add
180 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
181 AND FSAV.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID;
182
183 RETURN L_BALANCING_SEGMENT;
184 END get_balancing_segment;
185 -- Fix bug#7334017 add end
186
187
188 --==========================================================================
189 -- PROCEDURE NAME:
190 -- insert_sla_data Public
191 --
192 -- DESCRIPTION:
193 -- This procedure is used to search the record in sla module and insert
194 -- the cash flow item into CFS tables
195 --
196 -- PARAMETERS:
197 -- In: p_coa_id Chart of Accounts id
198 -- p_ledger_id Ledger ID
199 -- p_le_id legal entity ID
200 -- p_period_set_name period_set_name
201 -- p_gl_period_from the calculation period
202 -- p_gl_period_to the calculation period
203 -- p_source Source
204 -- p_bsv Balance Segment Value
205 --
206 -- DESIGN REFERENCES:
207 -- CNAO_CFS_Data_collection_TD.doc
208 --
209 -- CHANGE HISTORY:
210 -- 23/04/2006 Shujuan Yan Created
211 -- 29/09/2009 Chaoqun Wu Fix bug 8969631
212 -- 02/02/2010 SHujuan Yan Add collect GL Journal header id
213 -- and Journal line number for CNAO V2
214 -- 18/03/2011 Chongwu Li Fix bug 11872243
215 -- 22/06/2011 Chongwu Li Fix bug 12639420
216 -- 28/09/2012 Jar Wang Fix bug 14664279
217 --===========================================================================
218 PROCEDURE insert_SLA_data(P_COA_ID IN NUMBER,
219 P_LEDGER_ID IN NUMBER,
220 P_LE_ID IN NUMBER,
221 p_period_set_name IN VARCHAR2,
222 p_application_id IN NUMBER,
223 p_ae_header_id IN NUMBER,
224 p_ae_line_num IN VARCHAR2,
225 p_transaction_date IN DATE,
226 p_period_type IN VARCHAR2,
227 p_func_currency_code IN VARCHAR2,
228 p_currency_code IN VARCHAR2,
229 p_currency_conversion_rate IN NUMBER,
230 p_currency_conversion_type IN VARCHAR2,
231 p_currency_conversion_date IN DATE,
232 p_header_description IN VARCHAR2, --Added by Jar.Wang for fixing bug 14664279
233 p_detailed_cfs_item IN VARCHAR2,
234 p_EVENT_CLASS_CODE IN VARCHAR2,
235 --p_application_id IN NUMBER,
236 p_ANALYTICAL_CRITERION_CODE IN VARCHAR2,
237 p_ac_value IN VARCHAR2,
238 p_cash_date IN DATE,
239 p_accounting_class IN VARCHAR2,
240 p_cash_amount_cr IN NUMBER,
241 p_cash_amount_dr IN NUMBER,
242 p_accounted_dr IN NUMBER,
243 p_entered_dr IN NUMBER,
244 p_accounted_cr IN NUMBER,
245 p_entered_cr IN NUMBER,
246 p_ccid In VARCHAR2,--Fix bug#7334017 add
247 p_cash_ae_header_id in NUMBER,
248 p_cash_ae_line_number in NUMBER, --Added by Chaoqun for fixing bug 8969631
249 P_event_type_code in varchar2) AS
250 L_GL_date ja_cn_cfs_activities_all.gl_date%type;
251 l_func_amount ja_cn_cfs_activities_all.func_amount%type;
252 l_orig_amount ja_cn_cfs_activities_all.original_amount%type;
253 l_detailed_item_desc varchar2(240);
254 l_period_name gl_periods.period_name%TYPE;
255 l_application_source fnd_application.application_short_name%TYPE;
256 l_status ar_cash_receipt_history_all.status%TYPE;
257 l_je_header_id gl_je_lines.je_header_id%type;
258 l_je_line_num gl_je_lines.je_line_num%type;
259 BEGIN
260 --Get the application short name for source
261 SELECT application_short_name
262 INTO l_application_source
263 FROM fnd_application
264 WHERE application_id = p_application_id;
265
266 -- Get the gl date,
267 If p_cash_date > p_transaction_date then
268 L_GL_date := p_cash_date;
269 Else
270 L_GL_date := p_transaction_date;
271 End IF;
272
273 -- Check if status is Remitted
274 IF p_event_type_code = 'RECP_UPDATE'
275 THEN
276 begin
277 select ach.status
278 into l_status
279 from ar_cash_receipt_history_all ach,
280 xla_ae_headers ah
281 where ah.ae_header_id = p_cash_ae_header_id
282 ----- choli add this line to fix bug 11777168
283 -- choli change the condition as follow to fix bug 12639420
284 --and ach.account_code_combination_id = p_ccid
285
286 and ach.cash_receipt_history_id =
287 (select max(cash_receipt_history_id)
288 from ar_cash_receipt_history_all
289 where event_id = ah.event_id)
290
291 -----
292 and ah.event_id = ach.event_id;
293 EXCEPTION
294 WHEN NO_DATA_FOUND THEN
295 l_status :='';
296 END;
297 END IF;
298 FND_FILE.Put_Line(FND_FILE.log, 'p_cash_ae_header_id = ' || p_cash_ae_header_id || '===p_ccid=' || p_ccid || '===l_status=' || l_status);
299
300 -- For Uncleasing case
301 IF p_event_type_code = 'PAYMENT UNCLEARED'
302 or (p_event_type_code = 'RECP_UPDATE' and l_status = 'REMITTED')
303 Then
304 -- get the cash related functional amount and orignal amount
305 -- If the cash amount is in the credit
306 If p_accounting_class is not null and p_cash_amount_cr is not null Then
307 If p_accounted_dr is not null Then
308 L_func_amount := p_accounted_dr;
309 L_orig_amount := p_entered_dr;
310 Else
311 L_func_amount := -1* p_accounted_cr;
312 L_orig_amount := -1* p_entered_cr;
313 End IF;
314 Else
315 -- If the cash amount is in the debit
316 If p_accounting_class is not null and p_cash_amount_dr is not null Then
317 If p_accounted_dr is not null Then
318 L_func_amount := p_accounted_dr;
319 L_orig_amount := p_entered_dr;
320 Else
321 L_func_amount := -1* p_accounted_cr;
322 L_orig_amount := -1* p_entered_cr;
323 End IF;
324 End IF;
325 END IF;
326 Else
327 -- get the cash related functional amount and orignal amount
328 -- If the cash amount is in the credit
329 If p_accounting_class is not null and p_cash_amount_cr is not null Then
330 If p_accounted_dr is not null Then
331 L_func_amount := -1* p_accounted_dr;
332 L_orig_amount := -1* p_entered_dr;
333 Else
334 L_func_amount := p_accounted_cr;
335 L_orig_amount := p_entered_cr;
336 End IF;
337 Else
338 -- If the cash amount is in the debit
339 If p_accounting_class is not null and p_cash_amount_dr is not null Then
340 If p_accounted_dr is not null Then
341 L_func_amount := -1* p_accounted_dr;
342 L_orig_amount := -1* p_entered_dr;
343 Else
344 L_func_amount := p_accounted_cr;
345 L_orig_amount := p_entered_cr;
346 End IF;
347 End IF;
348 END IF;
349 End if;
350
351
352
353 --Get the Detailed cfs item description
354 BEGIN
355 SELECT Ffvt.DESCRIPTION
356 INTO l_detailed_item_desc
357 FROM Fnd_Flex_Values_Tl Ffvt,
358 fnd_flex_values Ffv,
359 ja_cn_cash_valuesets_all Cra
360 WHERE Cra.Chart_Of_Accounts_Id = p_coa_id
361 AND Ffv.Flex_Value_Set_Id = Cra.Flex_Value_Set_Id
362 AND Ffv.Flex_Value_Id = Ffvt.Flex_Value_Id
363 AND ffvt.flex_value_meaning = p_detailed_cfs_item
364 AND ffvt.LANGUAGE = userenv('LANG');
365 EXCEPTION
366 WHEN no_data_found THEN
367 l_detailed_item_desc :='';
368 END;
369 --get the period name
370 l_period_name := get_period_name(p_period_set_name,l_gl_date,p_period_type);
371
372 -- Get the GL Jounral header Id and JE line Number for CNAO V2
373 Begin
374 SELECT gl.je_header_id, gl.je_line_num
375 INTO l_je_header_id,l_je_line_num
376 FROM gl_import_references gl,
377 xla_ae_lines xl,
378 gl_je_headers jeh
379 WHERE xl.ae_header_id = p_ae_header_id
380 AND xl.ae_line_num = p_ae_line_num
381 AND xl.gl_sl_link_id = gl.gl_sl_link_id
382 AND xl.gl_sl_link_table= gl.gl_sl_link_table
383 AND jeh.je_header_id = gl.je_header_id
384 AND jeh.je_batch_id = gl.je_batch_id
385 AND jeh.ledger_id = P_LEDGER_ID; --test by chaoqun fixed bug 10316509
386 EXCEPTION
387 WHEN no_data_found THEN
388 l_je_header_id := '';
389 l_je_line_num :='';
390 END;
391
392 INSERT INTO ja_cn_cfs_activities_all(CFS_ACTIVITY_ID,
393 LEGAL_ENTITY_ID,
394 LEDGER_ID,
395 ORG_ID,
396 TRX_ID,
397 TRX_NUMBER,
398 TRX_LINE_ID,
399 CASH_TRX_ID, --Added by Chaoqun for fixing bug 8969631
400 CASH_TRX_LINE_ID, --Added by Chaoqun for fixing bug 8969631
401 SOURCE,
402 TRANSACTION_TYPE,
403 DOCUMENT_SEQUENCE_NUMBER,
404 TRANSACTION_DATE,
405 GL_DATE,
406 PERIOD_NAME,
407 FUNC_CURR_CODE,
408 FUNC_AMOUNT,
409 ORIGINAL_CURR_CODE,
410 ORIGINAL_AMOUNT,
411 CURRENCY_CONVERSION_RATE,
412 CURRENCY_CONVERSION_TYPE,
413 CURRENCY_CONVERSION_DATE,
414 DESCRIPTION,
415 DETAILED_CFS_ITEM,
416 INTERCOMPANY_FLAG,
417 REFERENCE_NUMBER,
418 THIRD_PARTY_NAME,
419 THIRD_PARTY_NUMBER,
420 EVENT_CLASS_CODE,
421 SOURCE_APPLICATION_ID,
422 ANALYTICAL_CRITERION_CODE,
423 SOURCE_VALUE,
424 CASH_ITEM_DESC ,
425 LAST_UPDATE_DATE,
426 LAST_UPDATED_BY,
427 CREATION_DATE,
428 CREATED_BY,
429 LAST_UPDATE_LOGIN,
430 balancing_segment, --Fix bug#7334017 add
431 je_header_id,
432 je_line_num)
433 VALUES(
434 ja_cn_cfs_activities_s.NEXTVAL
435 ,p_le_id
436 ,p_ledger_id
437 ,NULL
438 ,p_ae_header_id
439 ,NULL
440 ,p_ae_line_num
441 ,p_cash_ae_header_id --Added by Chaoqun for fixing bug 8969631
442 ,p_cash_ae_line_number --Added by Chaoqun for fixing bug 8969631
443 ,l_application_source--p_source
444 ,'SLA'
445 ,NULL
446 ,p_transaction_date
447 ,l_gl_date
448 ,l_period_name
449 ,p_func_currency_code
450 ,l_func_amount
451 ,p_currency_code
452 ,l_orig_amount
453 ,p_currency_conversion_rate
454 ,p_currency_conversion_type
455 ,p_currency_conversion_date
456 ,p_header_description --Added by Jar.Wang for fixing bug 14664279
457 ,p_detailed_cfs_item
458 ,''
459 ,p_ae_header_id
460 ,''
461 ,NULL
462 ,p_EVENT_CLASS_CODE
463 ,p_application_id
464 ,p_ANALYTICAL_CRITERION_CODE
465 ,p_ac_value
466 ,l_detailed_item_desc
467 ,SYSDATE
468 ,fnd_global.user_id
469 ,SYSDATE
470 ,fnd_global.user_id
471 ,fnd_global.LOGIN_ID
472 ,get_balancing_segment(p_ccid)--Fix bug#7334017 add
473 ,l_je_header_id
474 ,l_je_line_num);
475 END;
476 --==========================================================================
477 -- PROCEDURE NAME:
478 -- collect_sla_data Public
479 --
480 -- DESCRIPTION:
481 -- This procedure is used to search the record in sla module and insert
482 -- the cash flow item into CFS tables
483 --
484 -- PARAMETERS:
485 -- In: p_coa_id Chart of Accounts id
486 -- p_ledger_id Ledger ID
487 -- p_le_id legal entity ID
488 -- p_period_set_name period_set_name
489 -- p_gl_period_from the calculation period
490 -- p_gl_period_to the calculation period
491 -- p_source Source
492 --
493 -- DESIGN REFERENCES:
494 -- CNAO_CFS_Data_collection_TD.doc
495 --
496 -- CHANGE HISTORY:
497 -- 23/04/2006 Shujuan Yan Created
498 -- 08/09/2008 Yao Zhang Fix bug #7334017
499 -- 29/09/2009 Chaoqun Wu Fix bug 8969631
500 -- 21/02/2011 Chongwu Li Fix bug 11777674
501 -- 05/07/2011 Chongwu Li Fix bug 11777674(12720924), new solution
502 -- 28/09/2012 Jar Wang Fix bug 14664279
503 --===========================================================================
504 PROCEDURE collect_SLA_data(P_COA_ID IN NUMBER,
505 P_LEDGER_ID IN NUMBER,
506 P_LE_ID IN NUMBER,
507 P_PERIOD_SET_NAME IN VARCHAR2,
508 P_GL_PERIOD_FROM IN VARCHAR2,
509 P_GL_PERIOD_TO IN VARCHAR2,
510 P_SOURCE IN VARCHAR2) AS
511 l_procedure_name VARCHAR2(30) := 'collect_SLA_data';
512 l_period_num_from gl_periods.period_num%TYPE;
513 l_period_num_to gl_periods.period_num%TYPE;
514 l_date_from gl_periods.start_date%TYPE;
515 l_date_to gl_periods.end_date%TYPE;
516 l_func_currency_code fnd_currencies.currency_code%TYPE;
517 l_period_type gl_ledgers.accounted_period_type%TYPE;
518 --l_source fnd_application.application_short_name%TYPE;
519 l_source_id fnd_application.application_id%TYPE;
520
521 -- the interim variables for activities all table
522 l_line_org_id xla_transaction_entities.security_id_int_1%TYPE;
523 l_mapping_org_id NUMBER;
524 l_detailed_cfs_item ja_cn_cfs_activities_all.detailed_cfs_item%type;
525 l_event_class_code ja_cn_cfs_item_mapping_hdrs.event_class_code%TYPE;
526
527 --the analytical criterion variables
528 l_analytical_criterion_code xla_ae_line_acs.analytical_criterion_code%type;
529 l_ac_type_code xla_ae_line_acs.analytical_criterion_type_code%type;
530 l_amb_context_code xla_ae_line_acs.amb_context_code%type;
531 l_ac_value xla_ae_line_acs.ac1%type;
532
533 --sla lines variables
534 l_ae_header_id xla_ae_lines.ae_header_id%TYPE;
535 l_ae_line_num xla_ae_lines.ae_line_num%TYPE;
536 l_transaction_date xla_ae_lines.accounting_date%TYPE;
537 l_accounted_dr xla_ae_lines.accounted_dr%TYPE;
538 l_accounted_cr xla_ae_lines.accounted_cr%TYPE;
539 l_entered_dr xla_ae_lines.entered_dr%TYPE;
540 l_entered_cr xla_ae_lines.entered_cr%TYPE;
541 l_currency_code xla_ae_lines.currency_code%TYPE;
542 l_currency_conversion_rate xla_ae_lines.currency_conversion_rate%TYPE;
543 l_currency_conversion_type xla_ae_lines.currency_conversion_type%TYPE;
544 l_currency_conversion_date xla_ae_lines.currency_conversion_date%TYPE;
545
546 l_transaction_num xla_ae_line_acs.ac1%TYPE;
547 l_application_id xla_ae_lines.application_id%TYPE;
548 l_ccid xla_ae_lines.code_combination_id%TYPE;--Fix bug#7334017 add
549
550 -- Cash lines variables
551 l_cash_date xla_ae_lines.accounting_date%TYPE;
552 l_accounting_class xla_ae_lines.accounting_class_code%TYPE;
553 l_cash_amount_cr xla_ae_lines.accounted_cr%TYPE;
554 l_cash_amount_dr xla_ae_lines.accounted_dr%TYPE;
555 l_cash_app_id xla_ae_lines.application_id%TYPE; -- Fix bug 12542469 Added by shujuan
556
557 l_event_type_code xla_ae_headers.event_type_code%TYPE; -- Bug fixing Added by Shujan
558 l_cash_ae_header_id xla_ae_lines.ae_header_id%TYPE;
559 l_cash_ae_line_num xla_ae_lines.ae_line_num%TYPE;
560 l_header_description xla_ae_headers.description%TYPE; --Added by Jar.Wang for Fixing bug 14664279
561 -- dynatical cursor
562 TYPE SLACurTyp IS REF CURSOR;
563 c_sla_lines SLACurTyp;
564 c_cash_lines SLACurTyp;
565 sql_stmt_sla VARCHAR2(20000);
566 sql_stmt_sla_temp VARCHAR2(20000);
567 sql_stmt_cash VARCHAR2(20000);
568 --l_flag VARCHAR2(15);
569
570 BEGIN
571 --l_flag := Ja_Cn_Utility.Populate_Ledger_Le_Bsv_Gt (P_LEDGER_ID,p_le_id);
572 -- sql tatement for sla ae lines
573 sql_stmt_sla :=
574 'SELECT al.ae_header_id,
575 al.ae_line_num,
576 al.accounting_date,
577 al.accounted_dr,
578 al.accounted_cr,
579 al.entered_dr,
580 al.entered_cr,
581 al.currency_code,
582 al.currency_conversion_rate,
583 al.currency_conversion_type,
584 al.currency_conversion_date,
585 ah.description,
586 al.application_id,
587 ala.Analytical_Criterion_Code,
588 ala.analytical_criterion_type_code,
589 ala.amb_context_code,
590 ala.ac1,
591 al.code_combination_id
592 FROM xla_ae_lines al
593 ,xla_ae_headers ah
594 ,xla_ae_line_acs ala
595 ,fnd_segment_attribute_values fsav
596 ,gl_code_combinations gcc
597 ,ja_cn_ledger_le_bsv_gt glsv
598 WHERE ah.gl_transfer_status_code = ''Y''
599 AND al.ae_header_id = ah.ae_header_id
600 --AND al.analytical_balance_flag IS NOT NULL
601 AND ala.ae_header_id IN (SELECT ae_header_id
602 FROM xla_ae_line_acs
603 WHERE analytical_criterion_code = ''CHECK_ID''
604 AND amb_context_code = ''DEFAULT''
605 AND analytical_criterion_type_code = ''S''
606 AND ac1= :l_transaction_num)
607 AND al.ae_header_id = ala.ae_header_id
608 AND al.ae_line_num = ala.ae_line_num
609 AND al.ledger_id = :p_ledger_id
610 AND ala.Analytical_Criterion_Code <> ''CHECK_ID''
611 AND al.code_combination_id = gcc.code_combination_id
612 AND gcc.chart_of_accounts_id = :p_coa_id
613 AND fsav.application_id = 101
614 AND fsav.id_flex_num = gcc.chart_of_accounts_id
615 AND fsav.attribute_value = ''Y''
616 AND fsav.segment_attribute_type = ''GL_BALANCING''
617 AND FSAV.ID_FLEX_CODE = ''GL#''
618 AND glsv.ledger_id = :p_ledger_id
619 AND glsv.legal_entity_id = :p_le_id
620 @source_sql
621 @event_type_sql
622 AND DECODE(FSAV.APPLICATION_COLUMN_NAME,
623 ''SEGMENT1'',GCC.SEGMENT1, ''SEGMENT2'',GCC.SEGMENT2, ''SEGMENT3'',GCC.SEGMENT3,
624 ''SEGMENT4'',GCC.SEGMENT4, ''SEGMENT5'',GCC.SEGMENT5, ''SEGMENT6'',GCC.SEGMENT6,
625 ''SEGMENT7'',GCC.SEGMENT7, ''SEGMENT8'',GCC.SEGMENT8, ''SEGMENT9'',GCC.SEGMENT9,
626 ''SEGMENT10'',GCC.SEGMENT10, ''SEGMENT11'',GCC.SEGMENT11, ''SEGMENT12'',GCC.SEGMENT12,
627 ''SEGMENT13'',GCC.SEGMENT13, ''SEGMENT14'',GCC.SEGMENT14, ''SEGMENT15'',GCC.SEGMENT15,
628 ''SEGMENT16'',GCC.SEGMENT16, ''SEGMENT17'',GCC.SEGMENT17, ''SEGMENT18'',GCC.SEGMENT18,
629 ''SEGMENT19'',GCC.SEGMENT19, ''SEGMENT20'',GCC.SEGMENT20, ''SEGMENT21'',GCC.SEGMENT21,
630 ''SEGMENT22'',GCC.SEGMENT22, ''SEGMENT23'',GCC.SEGMENT23, ''SEGMENT24'',GCC.SEGMENT24,
631 ''SEGMENT25'',GCC.SEGMENT25, ''SEGMENT26'',GCC.SEGMENT26, ''SEGMENT27'',GCC.SEGMENT27,
632 ''SEGMENT28'',GCC.SEGMENT28, ''SEGMENT29'',GCC.SEGMENT29, ''SEGMENT30'',GCC.SEGMENT30) = glsv.bal_seg_value';
633 --Sql statement for sla cash lines
634 sql_stmt_cash :=
635 'SELECT al.accounting_date
636 ,al.accounting_class_code
637 ,al.accounted_cr
638 ,al.accounted_dr
639 ,ala.ac1
640 ,ah.event_type_code
641 ,ah.ae_header_id
642 ,al.ae_line_num
643 ,al.application_id
644 FROM xla_ae_lines al
645 ,xla_ae_headers ah
646 ,xla_ae_line_acs ala
647 ,fnd_segment_attribute_values fsav
648 ,fnd_segment_attribute_values fsav1
649 ,gl_code_combinations gcc
650 ,gl_code_combinations gcc1
651 ,ja_cn_ledger_le_bsv_gt glsv
652 ,ja_cn_cash_accounts_all jca
653 WHERE al.accounting_date >= :l_date_from
654 AND al.accounting_date < :l_date_to + 1
655 AND ah.gl_transfer_status_code = ''Y''
656 -- choli fix bug 11777674, 21/02/2011
657 AND ah.event_type_code <> ''PAYMENT ADJUSTED''
658 -- choli fix bug 11777674, 05/07/2011
659 AND al.displayed_line_number >=0
660 AND nvl(al.accounted_dr, al.accounted_cr) <> 0
661 --
662 AND al.ae_header_id = ah.ae_header_id
663 and al.ae_header_id = ala.ae_header_id
664 AND al.ae_line_num = ala.ae_line_num
665 --AND al.analytical_balance_flag IS NOT NULL
666 AND ala.analytical_criterion_code = ''CHECK_ID''
667 AND ala.analytical_criterion_type_code = ''S''
668 AND ala.amb_context_code = ''DEFAULT''
669 --AND ala.ac1 = :l_transaction_num
670 AND al.ledger_id = :p_ledger_id
671 AND (al.accounting_class_code =''CASH''
672 OR (al.accounting_class_code IN (SELECT class_code
673 FROM ja_cn_accounting_classes_all
674 WHERE chart_of_accounts_id = :p_coa_id )))
675 AND al.code_combination_id = gcc1.code_combination_id
676 AND gcc1.chart_of_accounts_id = :p_coa_id
677 AND fsav1.application_id = 101
678 AND fsav1.id_flex_num = :p_coa_id
679 AND fsav1.attribute_value = ''Y''
680 AND fsav1.segment_attribute_type = ''GL_BALANCING''
681 AND FSAV1.ID_FLEX_CODE = ''GL#''
682 AND glsv.ledger_id = :p_ledger_id
683 AND glsv.legal_entity_id = :p_le_id
684 AND DECODE(FSAV1.APPLICATION_COLUMN_NAME,
685 ''SEGMENT1'',GCC1.SEGMENT1, ''SEGMENT2'',GCC1.SEGMENT2, ''SEGMENT3'',GCC1.SEGMENT3,
686 ''SEGMENT4'',GCC1.SEGMENT4, ''SEGMENT5'',GCC1.SEGMENT5, ''SEGMENT6'',GCC1.SEGMENT6,
687 ''SEGMENT7'',GCC1.SEGMENT7, ''SEGMENT8'',GCC1.SEGMENT8, ''SEGMENT9'',GCC1.SEGMENT9,
688 ''SEGMENT10'',GCC1.SEGMENT10, ''SEGMENT11'',GCC1.SEGMENT11, ''SEGMENT12'',GCC1.SEGMENT12,
689 ''SEGMENT13'',GCC1.SEGMENT13, ''SEGMENT14'',GCC1.SEGMENT14, ''SEGMENT15'',GCC1.SEGMENT15,
690 ''SEGMENT16'',GCC1.SEGMENT16, ''SEGMENT17'',GCC1.SEGMENT17, ''SEGMENT18'',GCC1.SEGMENT18,
691 ''SEGMENT19'',GCC1.SEGMENT19, ''SEGMENT20'',GCC1.SEGMENT20, ''SEGMENT21'',GCC1.SEGMENT21,
692 ''SEGMENT22'',GCC1.SEGMENT22, ''SEGMENT23'',GCC1.SEGMENT23, ''SEGMENT24'',GCC1.SEGMENT24,
693 ''SEGMENT25'',GCC1.SEGMENT25, ''SEGMENT26'',GCC1.SEGMENT26, ''SEGMENT27'',GCC1.SEGMENT27,
694 ''SEGMENT28'',GCC1.SEGMENT28, ''SEGMENT29'',GCC1.SEGMENT29, ''SEGMENT30'',GCC1.SEGMENT30) = glsv.bal_seg_value
695 AND al.code_combination_id = gcc.code_combination_id
696 AND gcc.chart_of_accounts_id = :p_coa_id
697 AND FSAV.ATTRIBUTE_VALUE = ''Y''
698 AND FSAV.APPLICATION_ID = 101
699 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = ''GL_ACCOUNT''
700 AND FSAV.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
701 AND FSAV.ID_FLEX_CODE = ''GL#''
702 AND JCA.chart_of_accounts_id = :p_coa_id
703 @source_sql
704 AND DECODE(FSAV.APPLICATION_COLUMN_NAME,
705 ''SEGMENT1'',GCC.SEGMENT1, ''SEGMENT2'',GCC.SEGMENT2, ''SEGMENT3'',GCC.SEGMENT3,
706 ''SEGMENT4'',GCC.SEGMENT4, ''SEGMENT5'',GCC.SEGMENT5, ''SEGMENT6'',GCC.SEGMENT6,
707 ''SEGMENT7'',GCC.SEGMENT7, ''SEGMENT8'',GCC.SEGMENT8, ''SEGMENT9'',GCC.SEGMENT9,
708 ''SEGMENT10'',GCC.SEGMENT10, ''SEGMENT11'',GCC.SEGMENT11, ''SEGMENT12'',GCC.SEGMENT12,
709 ''SEGMENT13'',GCC.SEGMENT13, ''SEGMENT14'',GCC.SEGMENT14, ''SEGMENT15'',GCC.SEGMENT15,
710 ''SEGMENT16'',GCC.SEGMENT16, ''SEGMENT17'',GCC.SEGMENT17, ''SEGMENT18'',GCC.SEGMENT18,
711 ''SEGMENT19'',GCC.SEGMENT19, ''SEGMENT20'',GCC.SEGMENT20, ''SEGMENT21'',GCC.SEGMENT21,
712 ''SEGMENT22'',GCC.SEGMENT22, ''SEGMENT23'',GCC.SEGMENT23, ''SEGMENT24'',GCC.SEGMENT24,
713 ''SEGMENT25'',GCC.SEGMENT25, ''SEGMENT26'',GCC.SEGMENT26, ''SEGMENT27'',GCC.SEGMENT27,
714 ''SEGMENT28'',GCC.SEGMENT28, ''SEGMENT29'',GCC.SEGMENT29, ''SEGMENT30'',GCC.SEGMENT30) IN JCA.ACCOUNT_SEGMENT_VALUE';
715 IF (G_PROC_LEVEL >= g_debug_devel) THEN
716 FND_LOG.STRING(G_PROC_LEVEL,
717 G_MODULE_PREFIX || l_procedure_name || '.begin',
718 'Begin procedure');
719 END IF; --( G_PROC_LEVEL >= g_debug_devel)
720
721 --Get base currency code
722 SELECT currency_code, accounted_period_type
723 INTO l_func_currency_code, l_period_type
724 FROM gl_ledgers
725 WHERE ledger_id = p_ledger_id;
726
727 -- Get l_date_from, l_date_to
728 BEGIN
729 SELECT period_year * 1000 + period_num, start_date
730 INTO l_period_num_from, l_date_from
731 FROM gl_periods
732 WHERE period_set_name = p_period_set_name
733 AND period_name = P_GL_PERIOD_FROM
734 AND period_type = l_period_type;
735
736 SELECT period_year * 1000 + period_num, end_date
737 INTO l_period_num_to, l_date_to
738 FROM gl_periods
739 WHERE period_set_name = p_period_set_name
740 AND period_name = P_GL_PERIOD_to
741 AND period_type = l_period_type;
742
743 IF (G_STATEMENT_LEVEL >= g_debug_devel) THEN
744 put_log(G_MODULE_PREFIX || l_procedure_name || '.date range',
745 l_date_from || ':' || l_date_to);
746 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
747
748 EXCEPTION
749 WHEN NO_DATA_FOUND THEN
750 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel) THEN
751 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
752 G_MODULE_PREFIX || l_procedure_name ||
753 '.NO_DATA_FOUND',
754 'parameter periods wrong');
755 END IF;
756 RAISE;
757 END;
758
759 -- delete the record calculated before
760 IF p_source = 'ALL' THEN
761 DELETE ja_cn_cfs_activities_all ca
762 WHERE ca.legal_entity_id = P_LE_ID
763 AND ledger_id = p_ledger_id
764 AND ca.SOURCE_application_id NOT IN (101, 435)
765 AND ca.period_name IN
766 (SELECT period_name
767 FROM Gl_Periods
768 WHERE period_set_name = p_period_set_name
769 AND period_year * 1000 + period_num BETWEEN
770 l_period_num_from AND l_period_num_to);
771 COMMIT;
772 l_source_id := 101;
773 sql_stmt_sla := REPLACE(sql_stmt_sla,
774 '@source_sql',
775 'AND al.application_id = :l_cash_app_id'); -- Updated by shujuan for bug 12542469
776 sql_stmt_cash := REPLACE(sql_stmt_cash,
777 '@source_sql',
778 'AND al.application_id <> :l_source_id');
779
780 ELSE
781 BEGIN
782 SELECT application_id
783 INTO l_source_id
784 FROM fnd_application
785 WHERE application_short_name = p_source;
786
787 DELETE ja_cn_cfs_activities_all ca
788 WHERE ca.legal_entity_id = P_LE_ID
789 AND ledger_id = p_ledger_id
790 AND ca.SOURCE_application_id = l_source_id
791 AND ca.period_name IN
792 (SELECT period_name
793 FROM Gl_Periods
794 WHERE period_set_name = p_period_set_name
795 AND period_year * 1000 + period_num BETWEEN
796 l_period_num_from AND l_period_num_to);
797 COMMIT;
798 sql_stmt_sla := REPLACE(sql_stmt_sla,
799 '@source_sql',
800 'And al.application_id = :l_cash_app_id'); -- Updated by shujuan for bug 12542469
801 sql_stmt_cash := REPLACE(sql_stmt_cash,
802 '@source_sql',
803 'And al.application_id = :l_source_id');
804
805 IF (G_STATEMENT_LEVEL >= g_debug_devel) THEN
806 put_log(G_MODULE_PREFIX || l_procedure_name || '.source',
807 p_source);
808 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
809
810 EXCEPTION
811 WHEN NO_DATA_FOUND THEN
812 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel) THEN
813 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
814 G_MODULE_PREFIX || l_procedure_name ||
815 '.NO_DATA_FOUND',
816 'parameter periods wrong');
817 END IF;
818 RAISE;
819 END;
820 END IF;
821 IF( G_STATEMENT_LEVEL >= g_debug_devel )
822 THEN
823 put_log(G_MODULE_PREFIX||l_procedure_name||'.chart of accounts id'
824 ,p_coa_id);
825 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
826 -------------------------------
827 FND_FILE.Put_Line(FND_FILE.log, 'sql_stmt_cash 784 : --- : ' || sql_stmt_cash);
828 FND_FILE.Put_Line(FND_FILE.log, 'l_date_from = ' || l_date_from);
829 FND_FILE.Put_Line(FND_FILE.log, 'l_date_to = ' || l_date_to);
830 FND_FILE.Put_Line(FND_FILE.log, 'p_ledger_id = ' || p_ledger_id);
831 FND_FILE.Put_Line(FND_FILE.log, 'p_coa_id = ' || p_coa_id);
832 FND_FILE.Put_Line(FND_FILE.log, 'p_le_id = ' || p_le_id);
833 FND_FILE.Put_Line(FND_FILE.log, 'l_source_id = ' || l_source_id);
834 ------------------------------
835 OPEN c_cash_lines FOR sql_stmt_cash
836 using l_date_from, l_date_to, p_ledger_id, p_coa_id,
837 p_coa_id,p_coa_id,p_ledger_id,p_le_id,p_coa_id,p_coa_id,l_source_id;
838 LOOP
839 FETCH c_cash_lines INTO l_cash_date,
840 l_accounting_class,
841 l_cash_amount_cr,
842 l_cash_amount_dr,
843 l_transaction_num,
844 l_event_type_code,
845 l_cash_ae_header_id,
846 l_cash_ae_line_num, --Added by Chaoqun for fixing bug 8969631
847 l_cash_app_id; --Added by shujuan
848 EXIT WHEN c_cash_lines%NOTFOUND;
849 sql_stmt_sla_temp:= sql_stmt_sla;
850 -- For payment cancelled and receipt reverse
851 IF l_event_type_code not in ('PAYMENT CANCELLED' ,'RECP_REVERSE')
852 THEN
853 sql_stmt_sla_temp := REPLACE(sql_stmt_sla_temp,
854 '@event_type_sql',
855 'AND ah.event_type_code not IN (''PAYMENT CANCELLED'',''RECP_REVERSE'')');
856 Else
857 sql_stmt_sla_temp:= REPLACE(sql_stmt_sla_temp,
858 '@event_type_sql',
859 'AND ah.event_type_code IN (''PAYMENT CANCELLED'',''RECP_REVERSE'')');
860 END IF;
861 --Fixed bug 13498675 by shyan, Begin
862 IF l_event_type_code = 'PAYMENT CANCELLED' AND l_cash_amount_dr < 0 THEN
863 goto a;
864 end if;
865 --Fixed bug 13498675 by shyan, End
866 -------------------------------
867 FND_FILE.Put_Line(FND_FILE.log, 'sql_stmt_sla_temp 811 : --- : ' || sql_stmt_sla_temp);
868 FND_FILE.Put_Line(FND_FILE.log, 'l_transaction_num = ' || l_transaction_num);
869 FND_FILE.Put_Line(FND_FILE.log, 'p_ledger_id = ' || p_ledger_id);
870 FND_FILE.Put_Line(FND_FILE.log, 'p_coa_id = ' || p_coa_id);
871 FND_FILE.Put_Line(FND_FILE.log, 'p_le_id = ' || p_le_id);
872 FND_FILE.Put_Line(FND_FILE.log, 'l_source_id = ' || l_source_id);
873 -------------------------------
874
875 OPEN c_sla_lines FOR sql_stmt_sla_temp using l_transaction_num,p_ledger_id, p_coa_id,p_ledger_id,p_le_id,l_cash_app_id;
876 LOOP
877 FETCH c_sla_lines INTO l_ae_header_id,
878 l_ae_line_num,
879 l_transaction_date,
880 l_accounted_dr,
881 l_accounted_cr,
882 l_entered_dr,
883 l_entered_cr,
884 l_currency_code,
885 l_currency_conversion_rate,
886 l_currency_conversion_type,
887 l_currency_conversion_date,
888 l_header_description, -- Added by Jar.Wang for fixing bug 14664279
889 l_application_id,
890 l_analytical_criterion_code,
891 l_ac_type_code,
892 l_amb_context_code,
893 l_ac_value,
894 l_ccid;--Fix bug#7334017 add
895 EXIT WHEN c_sla_lines%NOTFOUND;
896 --Get org id of the current sla ae line
897 begin
898 Select xte.SECURITY_ID_INT_1
899 Into l_line_org_id
900 FROM XLA_TRANSACTION_ENTITIES xte, xla_ae_headers xah
901 Where xte.entity_id = xah.entity_id
902 And xte.application_id = xah.application_id
903 And xah.ae_header_id = l_ae_header_id
904 AND xah.application_id = l_application_id;
905 exception
906 WHEN no_data_found THEN
907 l_line_org_id:= NULL;
908 end;
909 --Get the detailed cfs item when there is the mapping relationship of
910 --the current analytical criterion and sla ae line org id
911 --in the mapping table
912 BEGIN
913 Select jccl.detailed_cfs_item, jccl.org_id, jcch.event_class_code
914 Into l_detailed_cfs_item, L_mapping_org_id, l_event_class_code
915 From ja_cn_cfs_item_mapping_hdrs jcch,
916 ja_cn_cfs_item_mapping_lines jccl
917 Where jcch.Chart_of_Accounts_id = p_coa_id
918 And jcch.analytical_criterion_code = l_analytical_criterion_code
919 AND jcch.analytical_criterion_type_code = l_ac_type_code
920 AND jcch.amb_context_code = l_amb_context_code
921 And jcch.mapping_header_id = jccl.mapping_header_id
922 And jccl.ac_value = l_ac_value
923 AND nvl(jccl.effective_start_date, l_transaction_date) <=l_transaction_date
924 AND nvl(jccl.effective_end_date,l_transaction_date) >=l_transaction_date
925 And jccl.org_id = l_line_org_id;
926 EXCEPTION
927 WHEN no_data_found THEN
928 l_detailed_cfs_item := NULL;
929 l_mapping_org_id := NULL;
930 WHEN too_many_rows THEN
931 l_detailed_cfs_item := NULL;
932 l_mapping_org_id := NULL;
933 END;
934
935 If l_detailed_cfs_item is null THEN
936 -- Get the detailed cfs item when there is the mapping relationship of
937 -- the current analytical criterion in the mapping table and the org id is
938 -- null in the mapping table
939
940
941 BEGIN
942 Select jccl.detailed_cfs_item,jcch.event_class_code
943 Into l_detailed_cfs_item,l_event_class_code
944 From ja_cn_cfs_item_mapping_hdrs jcch,
945 ja_cn_cfs_item_mapping_lines jccl
946 Where jcch.Chart_of_Accounts_id = p_coa_id
947 And jcch.analytical_criterion_code = l_analytical_criterion_code
948 AND jcch.analytical_criterion_type_code = l_ac_type_code
949 AND jcch.amb_context_code = l_amb_context_code
950 And jcch.mapping_header_id = jccl.mapping_header_id
951 And jccl.ac_value = l_ac_value
952 AND nvl(jccl.effective_start_date,l_transaction_date) <= l_transaction_date
953 AND nvl(jccl.effective_end_date, l_transaction_date) >= l_transaction_date
954 And jccl.org_id is NULL;
955 EXCEPTION
956 WHEN no_data_found THEN
957 l_detailed_cfs_item := NULL;
958 WHEN too_many_rows THEN
959 l_detailed_cfs_item := NULL;
960 END;
961 If l_detailed_cfs_item is not null THEN
962
963 insert_SLA_data(P_COA_ID,
964 P_LEDGER_ID,
965 P_LE_ID,
966 p_period_set_name,
967 l_application_id,
968 l_ae_header_id,
969 l_ae_line_num,
970 l_transaction_date,
971 l_period_type,
972 l_func_currency_code,
973 l_currency_code,
974 l_currency_conversion_rate,
975 l_currency_conversion_type,
976 l_currency_conversion_date,
977 l_header_description, -- Added by Jar.Wang for fixing bug 14664279
978 l_detailed_cfs_item,
979 l_EVENT_CLASS_CODE,
980 --l_application_id,
981 l_ANALYTICAL_CRITERION_CODE,
982 l_ac_value,
983 l_cash_date,
984 l_accounting_class,
985 l_cash_amount_cr,
986 l_cash_amount_dr,
987 l_accounted_dr,
988 l_entered_dr,
989 l_accounted_cr,
990 l_entered_cr,
991 l_ccid, --Fix bug#7334017 add
992 l_cash_ae_header_id,
993 l_cash_ae_line_num, --Added by Chaoqun for fixing bug 8969631
994 l_event_type_code);
995 End IF;
996 Else
997 insert_SLA_data(P_COA_ID,
998 P_LEDGER_ID,
999 P_LE_ID,
1000 p_period_set_name,
1001 l_application_id,
1002 l_ae_header_id,
1003 l_ae_line_num,
1004 l_transaction_date,
1005 l_period_type,
1006 l_func_currency_code,
1007 l_currency_code,
1008 l_currency_conversion_rate,
1009 l_currency_conversion_type,
1010 l_currency_conversion_date,
1011 l_header_description, -- Added by Jar.Wang for fixing bug 14664279
1012 l_detailed_cfs_item,
1013 l_EVENT_CLASS_CODE,
1014 --l_application_id,
1015 l_ANALYTICAL_CRITERION_CODE,
1016 l_ac_value,
1017 l_cash_date,
1018 l_accounting_class,
1019 l_cash_amount_cr,
1020 l_cash_amount_dr,
1021 l_accounted_dr,
1022 l_entered_dr,
1023 l_accounted_cr,
1024 l_entered_cr ,
1025 l_ccid,--Fix bug#7334017 add
1026 l_cash_ae_header_id,
1027 l_cash_ae_line_num, --Added by Chaoqun for fixing bug 8969631
1028 l_event_type_code);
1029 End IF;
1030 END LOOP;
1031 CLOSE c_sla_lines;
1032 COMMIT;
1033 --Fixed bug 13498675 by shyan, Begin
1034 <<a>>
1035 null;
1036 --Fixed bug 13498675 by shyan, End
1037 END LOOP;
1038 CLOSE c_cash_lines;
1039 IF( G_PROC_LEVEL >= g_debug_devel )
1040 THEN
1041 FND_LOG.STRING(G_PROC_LEVEL
1042 ,G_MODULE_PREFIX||l_procedure_name||'.end'
1043 ,'End procedure');
1044 END IF; --( G_PROC_LEVEL >= g_debug_devel)
1045 EXCEPTION
1046 WHEN OTHERS THEN
1047 IF(FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel)
1048 THEN
1049 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1050 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
1051 , SQLCODE||':'||SQLERRM||p_coa_id);
1052 END IF;
1053 RAISE;
1054 END collect_SLA_data;
1055
1056 end JA_CN_CFS_CLT_SLA_PKG;