[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.7.12010000.3 2008/11/10 06:21:15 shyan ship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNCDCB.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 --+======================================================================*/
26 --==========================================================================
27 -- PROCEDURE NAME:
28 -- Put_Line private
29 --
30 -- DESCRIPTION:
31 -- This procedure write data to log file.
32 --
33 -- PARAMETERS:
34 -- In: p_str VARCHAR2
35 --
36 -- DESIGN REFERENCES:
37 -- None
38 --
39 -- CHANGE HISTORY:
40 -- 23/04/2007 Shujuan Yan Created
41 --===========================================================================
42 PROCEDURE put_log(p_module IN VARCHAR2, p_message IN VARCHAR2) AS
43 BEGIN
44 IF (fnd_log.LEVEL_STATEMENT >= g_debug_devel) THEN
45 fnd_log.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
46 MODULE => p_module,
47 MESSAGE => p_message);
48 END IF;
49
50 END put_log;
51 --==========================================================================
52 -- PROCEDURE NAME:
53 -- Put_Line private
54 --
55 -- DESCRIPTION:
56 -- This procedure write data to concurrent output file.
57 --
58 -- PARAMETERS:
59 -- In: p_str VARCHAR2
60 --
61 -- DESIGN REFERENCES:
62 -- None
63 --
64 -- CHANGE HISTORY:
65 -- 23/04/2007 Shujuan Yan Created
66 --===========================================================================
67 PROCEDURE put_line(p_str IN VARCHAR2) AS
68 BEGIN
69 FND_FILE.Put_Line(FND_FILE.Output, p_str);
70 END put_line;
71 --==========================================================================
72 -- FUNCTION NAME:
73 -- get_period_name Public
74 --
75 -- DESCRIPTION:
76 -- This FUNCTION is used to get period name from a period set and given date
77 -- the period name is month type
78 --
79 -- PARAMETERS:
80 -- In: p_period_set_name period set name
81 -- p_gl_date date
82 -- p_period_type period type
83 -- return: period name
84 --
85 -- DESIGN REFERENCES:
86 -- None
87 --
88 -- CHANGE HISTORY:
89 -- 23/04/2007 Shujuan Yan Created
90 --===========================================================================
91 FUNCTION get_period_name(p_period_set_name IN VARCHAR2,
92 p_gl_date IN DATE,
93 p_period_type IN VARCHAR2) RETURN VARCHAR2 AS
94 l_period_name VARCHAR2(30);
95 BEGIN
96 SELECT period_name
97 INTO l_period_name
98 FROM gl_periods
99 WHERE period_set_name = p_period_set_name
100 AND start_date <= p_gl_date
101 AND End_Date >= p_gl_date
102 AND period_type = p_period_type
103 AND adjustment_period_flag = 'N';
104
105 RETURN l_period_name;
106
107 END get_period_name;
108
109
110 -- Fix bug#7334017 add begin
111 --==========================================================================
112 -- PROCEDURE NAME:
113 -- get_balancing_segment private
114 --
115 -- DESCRIPTION:
116 -- This procedure returns the balancing segment value of a CCID.
117 --
118 -- PARAMETERS:
119 -- In: P_CC_ID NUMBER
120 --
121 -- DESIGN REFERENCES:
122 -- None
123 --
124 -- CHANGE HISTORY:
125 -- 03/09/2008 Yao Zhang Created
126 --===========================================================================
127 FUNCTION get_balancing_segment
128 ( P_CC_ID IN NUMBER
129 )
130 RETURN VARCHAR2
131 IS
132 L_BALANCING_SEGMENT GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
133 BEGIN
134 SELECT
135 DECODE(FSAV.APPLICATION_COLUMN_NAME, --segment FSAV.APPLICATION_COLUMN_NAME of gcc
136 'SEGMENT1',GCC.SEGMENT1, 'SEGMENT2',GCC.SEGMENT2, 'SEGMENT3',GCC.SEGMENT3,
137 'SEGMENT4',GCC.SEGMENT4, 'SEGMENT5',GCC.SEGMENT5, 'SEGMENT6',GCC.SEGMENT6,
138 'SEGMENT7',GCC.SEGMENT7, 'SEGMENT8',GCC.SEGMENT8, 'SEGMENT9',GCC.SEGMENT9,
139 'SEGMENT10',GCC.SEGMENT10, 'SEGMENT11',GCC.SEGMENT11, 'SEGMENT12',GCC.SEGMENT12,
140 'SEGMENT13',GCC.SEGMENT13, 'SEGMENT14',GCC.SEGMENT14, 'SEGMENT15',GCC.SEGMENT15,
141 'SEGMENT16',GCC.SEGMENT16, 'SEGMENT17',GCC.SEGMENT17, 'SEGMENT18',GCC.SEGMENT18,
142 'SEGMENT19',GCC.SEGMENT19, 'SEGMENT20',GCC.SEGMENT20, 'SEGMENT21',GCC.SEGMENT21,
143 'SEGMENT22',GCC.SEGMENT22, 'SEGMENT23',GCC.SEGMENT23, 'SEGMENT24',GCC.SEGMENT24,
144 'SEGMENT25',GCC.SEGMENT25, 'SEGMENT26',GCC.SEGMENT26, 'SEGMENT27',GCC.SEGMENT27,
145 'SEGMENT28',GCC.SEGMENT28, 'SEGMENT29',GCC.SEGMENT29, 'SEGMENT30',GCC.SEGMENT30)
146 INTO L_BALANCING_SEGMENT
147 FROM GL_CODE_COMBINATIONS GCC,
148 FND_SEGMENT_ATTRIBUTE_VALUES FSAV
149 WHERE GCC.CODE_COMBINATION_ID = P_CC_ID
150 AND FSAV.ATTRIBUTE_VALUE = 'Y'
151 AND FSAV.APPLICATION_ID = 101
152 AND FSAV.ID_FLEX_CODE = 'GL#'--Fix bug#7334017 add
153 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
154 AND FSAV.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID;
155
156 RETURN L_BALANCING_SEGMENT;
157 END get_balancing_segment;
158 -- Fix bug#7334017 add end
159
160
161 --==========================================================================
162 -- PROCEDURE NAME:
163 -- insert_sla_data Public
164 --
165 -- DESCRIPTION:
166 -- This procedure is used to search the record in sla module and insert
167 -- the cash flow item into CFS tables
168 --
169 -- PARAMETERS:
170 -- In: p_coa_id Chart of Accounts id
171 -- p_ledger_id Ledger ID
172 -- p_le_id legal entity ID
173 -- p_period_set_name period_set_name
174 -- p_gl_period_from the calculation period
175 -- p_gl_period_to the calculation period
176 -- p_source Source
177 -- p_bsv Balance Segment Value
178 --
179 -- DESIGN REFERENCES:
180 -- CNAO_CFS_Data_collection_TD.doc
181 --
182 -- CHANGE HISTORY:
183 -- 23/04/2006 Shujuan Yan Created
184 --===========================================================================
185 PROCEDURE insert_SLA_data(P_COA_ID IN NUMBER,
186 P_LEDGER_ID IN NUMBER,
187 P_LE_ID IN NUMBER,
188 p_period_set_name IN VARCHAR2,
189 p_application_id IN NUMBER,
190 p_ae_header_id IN NUMBER,
191 p_ae_line_num IN VARCHAR2,
192 p_transaction_date IN DATE,
193 p_period_type IN VARCHAR2,
194 p_func_currency_code IN VARCHAR2,
195 p_currency_code IN VARCHAR2,
196 p_currency_conversion_rate IN NUMBER,
197 p_currency_conversion_type IN VARCHAR2,
198 p_currency_conversion_date IN DATE,
199 p_detailed_cfs_item IN VARCHAR2,
200 p_EVENT_CLASS_CODE IN VARCHAR2,
201 --p_application_id IN NUMBER,
202 p_ANALYTICAL_CRITERION_CODE IN VARCHAR2,
203 p_ac_value IN VARCHAR2,
204 p_cash_date IN DATE,
205 p_accounting_class IN VARCHAR2,
206 p_cash_amount_cr IN NUMBER,
207 p_cash_amount_dr IN NUMBER,
208 p_accounted_dr IN NUMBER,
209 p_entered_dr IN NUMBER,
210 p_accounted_cr IN NUMBER,
211 p_entered_cr IN NUMBER,
212 p_ccid In VARCHAR2) AS--Fix bug#7334017 add
213 L_GL_date ja_cn_cfs_activities_all.gl_date%type;
214 l_func_amount ja_cn_cfs_activities_all.func_amount%type;
215 l_orig_amount ja_cn_cfs_activities_all.original_amount%type;
216 l_detailed_item_desc varchar2(240);
217 l_period_name gl_periods.period_name%TYPE;
218 l_application_source fnd_application.application_short_name%TYPE;
219 BEGIN
220 --Get the application short name for source
221 SELECT application_short_name
222 INTO l_application_source
223 FROM fnd_application
224 WHERE application_id = p_application_id;
225
226 -- Get the gl date,
227 If p_cash_date > p_transaction_date then
228 L_GL_date := p_cash_date;
229 Else
230 L_GL_date := p_transaction_date;
231 End IF;
232 -- get the cash related functional amount and orignal amount
233 -- If the cash amount is in the credit
234 If p_accounting_class is not null and p_cash_amount_cr is not null Then
235 If p_accounted_dr is not null Then
236 L_func_amount := -1* p_accounted_dr;
237 L_orig_amount := -1* p_entered_dr;
238 Else
239 L_func_amount := p_accounted_cr;
240 L_orig_amount := p_entered_cr;
241 End IF;
242 Else
243 -- If the cash amount is in the debit
244 If p_accounting_class is not null and p_cash_amount_dr is not null Then
245 If p_accounted_dr is not null Then
246 L_func_amount := -1* p_accounted_dr;
247 L_orig_amount := -1* p_entered_dr;
248 Else
249 L_func_amount := p_accounted_cr;
250 L_orig_amount := p_entered_cr;
251 End IF;
252 End IF;
253 END IF;
254
255 --Get the Detailed cfs item description
256 BEGIN
257 SELECT Ffvt.DESCRIPTION
258 INTO l_detailed_item_desc
259 FROM Fnd_Flex_Values_Tl Ffvt,
260 fnd_flex_values Ffv,
261 ja_cn_cash_valuesets_all Cra
262 WHERE Cra.Chart_Of_Accounts_Id = p_coa_id
263 AND Ffv.Flex_Value_Set_Id = Cra.Flex_Value_Set_Id
264 AND Ffv.Flex_Value_Id = Ffvt.Flex_Value_Id
265 AND ffvt.flex_value_meaning = p_detailed_cfs_item
266 AND ffvt.LANGUAGE = userenv('LANG');
267 EXCEPTION
268 WHEN no_data_found THEN
269 l_detailed_item_desc :='';
270 END;
271 --get the period name
272 l_period_name := get_period_name(p_period_set_name,l_gl_date,p_period_type);
273 INSERT INTO ja_cn_cfs_activities_all(CFS_ACTIVITY_ID,
274 LEGAL_ENTITY_ID,
275 LEDGER_ID,
276 ORG_ID,
277 TRX_ID,
278 TRX_NUMBER,
279 TRX_LINE_ID,
280 SOURCE,
281 TRANSACTION_TYPE,
282 DOCUMENT_SEQUENCE_NUMBER,
283 TRANSACTION_DATE,
284 GL_DATE,
285 PERIOD_NAME,
286 FUNC_CURR_CODE,
287 FUNC_AMOUNT,
288 ORIGINAL_CURR_CODE,
289 ORIGINAL_AMOUNT,
290 CURRENCY_CONVERSION_RATE,
291 CURRENCY_CONVERSION_TYPE,
292 CURRENCY_CONVERSION_DATE,
293 DESCRIPTION,
294 DETAILED_CFS_ITEM,
295 INTERCOMPANY_FLAG,
296 REFERENCE_NUMBER,
297 THIRD_PARTY_NAME,
298 THIRD_PARTY_NUMBER,
299 EVENT_CLASS_CODE,
300 SOURCE_APPLICATION_ID,
301 ANALYTICAL_CRITERION_CODE,
302 SOURCE_VALUE,
303 CASH_ITEM_DESC ,
304 LAST_UPDATE_DATE,
305 LAST_UPDATED_BY,
306 CREATION_DATE,
307 CREATED_BY,
308 LAST_UPDATE_LOGIN,
309 balancing_segment)--Fix bug#7334017 add
310 VALUES(
311 ja_cn_cfs_activities_s.NEXTVAL
312 ,p_le_id
313 ,p_ledger_id
314 ,NULL
315 ,p_ae_header_id
316 ,NULL
317 ,p_ae_line_num
318 ,l_application_source--p_source
319 ,'SLA'
320 ,NULL
321 ,p_transaction_date
322 ,l_gl_date
323 ,l_period_name
324 ,p_func_currency_code
325 ,l_func_amount
326 ,p_currency_code
327 ,l_orig_amount
328 ,p_currency_conversion_rate
329 ,p_currency_conversion_type
330 ,p_currency_conversion_date
331 ,''
332 ,p_detailed_cfs_item
333 ,''
334 ,p_ae_header_id
335 ,''
336 ,NULL
337 ,p_EVENT_CLASS_CODE
338 ,p_application_id
339 ,p_ANALYTICAL_CRITERION_CODE
340 ,p_ac_value
341 ,l_detailed_item_desc
342 ,SYSDATE
343 ,fnd_global.user_id
344 ,SYSDATE
345 ,fnd_global.user_id
346 ,fnd_global.LOGIN_ID
347 ,get_balancing_segment(p_ccid));--Fix bug#7334017 add
348 END;
349 --==========================================================================
350 -- PROCEDURE NAME:
351 -- collect_sla_data Public
352 --
353 -- DESCRIPTION:
354 -- This procedure is used to search the record in sla module and insert
355 -- the cash flow item into CFS tables
356 --
357 -- PARAMETERS:
358 -- In: p_coa_id Chart of Accounts id
359 -- p_ledger_id Ledger ID
360 -- p_le_id legal entity ID
361 -- p_period_set_name period_set_name
362 -- p_gl_period_from the calculation period
363 -- p_gl_period_to the calculation period
364 -- p_source Source
365 --
366 -- DESIGN REFERENCES:
367 -- CNAO_CFS_Data_collection_TD.doc
368 --
369 -- CHANGE HISTORY:
370 -- 23/04/2006 Shujuan Yan Created
371 -- 08/09/2008 Yao Zhang Fix bug #7334017
372 --===========================================================================
373 PROCEDURE collect_SLA_data(P_COA_ID IN NUMBER,
374 P_LEDGER_ID IN NUMBER,
375 P_LE_ID IN NUMBER,
376 P_PERIOD_SET_NAME IN VARCHAR2,
377 P_GL_PERIOD_FROM IN VARCHAR2,
378 P_GL_PERIOD_TO IN VARCHAR2,
379 P_SOURCE IN VARCHAR2) AS
380 l_procedure_name VARCHAR2(30) := 'collect_SLA_data';
381 l_period_num_from gl_periods.period_num%TYPE;
382 l_period_num_to gl_periods.period_num%TYPE;
383 l_date_from gl_periods.start_date%TYPE;
384 l_date_to gl_periods.end_date%TYPE;
385 l_func_currency_code fnd_currencies.currency_code%TYPE;
386 l_period_type gl_ledgers.accounted_period_type%TYPE;
387 --l_source fnd_application.application_short_name%TYPE;
388 l_source_id fnd_application.application_id%TYPE;
389
390 -- the interim variables for activities all table
391 l_line_org_id xla_transaction_entities.security_id_int_1%TYPE;
392 l_mapping_org_id NUMBER;
393 l_detailed_cfs_item ja_cn_cfs_activities_all.detailed_cfs_item%type;
394 l_event_class_code ja_cn_cfs_item_mapping_hdrs.event_class_code%TYPE;
395
396 --the analytical criterion variables
397 l_analytical_criterion_code xla_ae_line_acs.analytical_criterion_code%type;
398 l_ac_type_code xla_ae_line_acs.analytical_criterion_type_code%type;
399 l_amb_context_code xla_ae_line_acs.amb_context_code%type;
400 l_ac_value xla_ae_line_acs.ac1%type;
401
402 --sla lines variables
403 l_ae_header_id xla_ae_lines.ae_header_id%TYPE;
404 l_ae_line_num xla_ae_lines.ae_line_num%TYPE;
405 l_transaction_date xla_ae_lines.accounting_date%TYPE;
406 l_accounted_dr xla_ae_lines.accounted_dr%TYPE;
407 l_accounted_cr xla_ae_lines.accounted_cr%TYPE;
408 l_entered_dr xla_ae_lines.entered_dr%TYPE;
409 l_entered_cr xla_ae_lines.entered_cr%TYPE;
410 l_currency_code xla_ae_lines.currency_code%TYPE;
411 l_currency_conversion_rate xla_ae_lines.currency_conversion_rate%TYPE;
412 l_currency_conversion_type xla_ae_lines.currency_conversion_type%TYPE;
413 l_currency_conversion_date xla_ae_lines.currency_conversion_date%TYPE;
414 l_transaction_num xla_ae_line_acs.ac1%TYPE;
415 l_application_id xla_ae_lines.application_id%TYPE;
416 l_ccid xla_ae_lines.code_combination_id%TYPE;--Fix bug#7334017 add
417
418 -- Cash lines variables
419 l_cash_date xla_ae_lines.accounting_date%TYPE;
420 l_accounting_class xla_ae_lines.accounting_class_code%TYPE;
421 l_cash_amount_cr xla_ae_lines.accounted_cr%TYPE;
422 l_cash_amount_dr xla_ae_lines.accounted_dr%TYPE;
423
424 -- dynatical cursor
425 TYPE SLACurTyp IS REF CURSOR;
426 c_sla_lines SLACurTyp;
427 c_cash_lines SLACurTyp;
428 sql_stmt_sla VARCHAR2(20000);
429 sql_stmt_cash VARCHAR2(20000);
430 l_flag VARCHAR2(15);
431
432 BEGIN
433 --l_flag := Ja_Cn_Utility.Populate_Ledger_Le_Bsv_Gt (P_LEDGER_ID,p_le_id);
434 -- sql tatement for sla ae lines
435 sql_stmt_sla :=
436 'SELECT al.ae_header_id,
437 al.ae_line_num,
438 al.accounting_date,
439 al.accounted_dr,
440 al.accounted_cr,
441 al.entered_dr,
442 al.entered_cr,
443 al.currency_code,
444 al.currency_conversion_rate,
445 al.currency_conversion_type,
446 al.currency_conversion_date,
447 al.application_id,
448 ala.Analytical_Criterion_Code,
449 ala.analytical_criterion_type_code,
450 ala.amb_context_code,
451 ala.ac1,
452 al.code_combination_id
453 FROM xla_ae_lines al
454 ,xla_ae_headers ah
455 ,xla_ae_line_acs ala
456 ,fnd_segment_attribute_values fsav
457 ,gl_code_combinations gcc
458 ,ja_cn_ledger_le_bsv_gt glsv
459 WHERE ah.gl_transfer_status_code = ''Y''
460 AND ah.event_type_code NOT IN (''PAYMENT CANCELLED'',''RECP_REVERSE'')
461 AND al.ae_header_id = ah.ae_header_id
462 --AND al.analytical_balance_flag IS NOT NULL
463 AND ala.ae_header_id IN (SELECT ae_header_id
464 FROM xla_ae_line_acs
465 WHERE analytical_criterion_code = ''CHECK_ID''
466 AND amb_context_code = ''DEFAULT''
467 AND analytical_criterion_type_code = ''S''
468 AND ac1= :l_transaction_num)
469 AND al.ae_header_id = ala.ae_header_id
470 AND al.ae_line_num = ala.ae_line_num
471 AND al.ledger_id = :p_ledger_id
472 AND ala.Analytical_Criterion_Code <> ''CHECK_ID''
473 AND al.code_combination_id = gcc.code_combination_id
474 AND gcc.chart_of_accounts_id = :p_coa_id
475 AND fsav.application_id = 101
476 AND fsav.id_flex_num = gcc.chart_of_accounts_id
477 AND fsav.attribute_value = ''Y''
478 AND fsav.segment_attribute_type = ''GL_BALANCING''
479 AND FSAV.ID_FLEX_CODE = ''GL#''
480 AND glsv.ledger_id = :p_ledger_id
481 AND glsv.legal_entity_id = :p_le_id
482 @source_sql
483 AND DECODE(FSAV.APPLICATION_COLUMN_NAME,
484 ''SEGMENT1'',GCC.SEGMENT1, ''SEGMENT2'',GCC.SEGMENT2, ''SEGMENT3'',GCC.SEGMENT3,
485 ''SEGMENT4'',GCC.SEGMENT4, ''SEGMENT5'',GCC.SEGMENT5, ''SEGMENT6'',GCC.SEGMENT6,
486 ''SEGMENT7'',GCC.SEGMENT7, ''SEGMENT8'',GCC.SEGMENT8, ''SEGMENT9'',GCC.SEGMENT9,
487 ''SEGMENT10'',GCC.SEGMENT10, ''SEGMENT11'',GCC.SEGMENT11, ''SEGMENT12'',GCC.SEGMENT12,
488 ''SEGMENT13'',GCC.SEGMENT13, ''SEGMENT14'',GCC.SEGMENT14, ''SEGMENT15'',GCC.SEGMENT15,
489 ''SEGMENT16'',GCC.SEGMENT16, ''SEGMENT17'',GCC.SEGMENT17, ''SEGMENT18'',GCC.SEGMENT18,
490 ''SEGMENT19'',GCC.SEGMENT19, ''SEGMENT20'',GCC.SEGMENT20, ''SEGMENT21'',GCC.SEGMENT21,
491 ''SEGMENT22'',GCC.SEGMENT22, ''SEGMENT23'',GCC.SEGMENT23, ''SEGMENT24'',GCC.SEGMENT24,
492 ''SEGMENT25'',GCC.SEGMENT25, ''SEGMENT26'',GCC.SEGMENT26, ''SEGMENT27'',GCC.SEGMENT27,
493 ''SEGMENT28'',GCC.SEGMENT28, ''SEGMENT29'',GCC.SEGMENT29, ''SEGMENT30'',GCC.SEGMENT30) = glsv.bal_seg_value';
494 --Sql statement for sla cash lines
495 sql_stmt_cash :=
496 'SELECT al.accounting_date
497 ,al.accounting_class_code
498 ,al.accounted_cr
499 ,al.accounted_dr
500 ,ala.ac1
501 FROM xla_ae_lines al
502 ,xla_ae_headers ah
503 ,xla_ae_line_acs ala
504 ,fnd_segment_attribute_values fsav
505 ,fnd_segment_attribute_values fsav1
506 ,gl_code_combinations gcc
507 ,gl_code_combinations gcc1
508 ,ja_cn_ledger_le_bsv_gt glsv
509 ,ja_cn_cash_accounts_all jca
510 WHERE al.accounting_date >= :l_date_from
511 AND al.accounting_date < :l_date_to + 1
512 AND ah.gl_transfer_status_code = ''Y''
513 AND al.ae_header_id = ah.ae_header_id
514 and al.ae_header_id = ala.ae_header_id
515 AND al.ae_line_num = ala.ae_line_num
516 --AND al.analytical_balance_flag IS NOT NULL
517 AND ala.analytical_criterion_code = ''CHECK_ID''
518 AND ala.analytical_criterion_type_code = ''S''
519 AND ala.amb_context_code = ''DEFAULT''
520 --AND ala.ac1 = :l_transaction_num
521 AND al.ledger_id = :p_ledger_id
522 AND (al.accounting_class_code =''CASH''
523 OR (al.accounting_class_code IN (SELECT class_code
524 FROM ja_cn_accounting_classes_all
525 WHERE chart_of_accounts_id = :p_coa_id )))
526 AND al.code_combination_id = gcc1.code_combination_id
527 AND gcc1.chart_of_accounts_id = :p_coa_id
528 AND fsav1.application_id = 101
529 AND fsav1.id_flex_num = :p_coa_id
530 AND fsav1.attribute_value = ''Y''
531 AND fsav1.segment_attribute_type = ''GL_BALANCING''
532 AND FSAV1.ID_FLEX_CODE = ''GL#''
533 AND glsv.ledger_id = :p_ledger_id
534 AND glsv.legal_entity_id = :p_le_id
535 AND DECODE(FSAV1.APPLICATION_COLUMN_NAME,
536 ''SEGMENT1'',GCC1.SEGMENT1, ''SEGMENT2'',GCC1.SEGMENT2, ''SEGMENT3'',GCC1.SEGMENT3,
537 ''SEGMENT4'',GCC1.SEGMENT4, ''SEGMENT5'',GCC1.SEGMENT5, ''SEGMENT6'',GCC1.SEGMENT6,
538 ''SEGMENT7'',GCC1.SEGMENT7, ''SEGMENT8'',GCC1.SEGMENT8, ''SEGMENT9'',GCC1.SEGMENT9,
539 ''SEGMENT10'',GCC1.SEGMENT10, ''SEGMENT11'',GCC1.SEGMENT11, ''SEGMENT12'',GCC1.SEGMENT12,
540 ''SEGMENT13'',GCC1.SEGMENT13, ''SEGMENT14'',GCC1.SEGMENT14, ''SEGMENT15'',GCC1.SEGMENT15,
541 ''SEGMENT16'',GCC1.SEGMENT16, ''SEGMENT17'',GCC1.SEGMENT17, ''SEGMENT18'',GCC1.SEGMENT18,
542 ''SEGMENT19'',GCC1.SEGMENT19, ''SEGMENT20'',GCC1.SEGMENT20, ''SEGMENT21'',GCC1.SEGMENT21,
543 ''SEGMENT22'',GCC1.SEGMENT22, ''SEGMENT23'',GCC1.SEGMENT23, ''SEGMENT24'',GCC1.SEGMENT24,
544 ''SEGMENT25'',GCC1.SEGMENT25, ''SEGMENT26'',GCC1.SEGMENT26, ''SEGMENT27'',GCC1.SEGMENT27,
545 ''SEGMENT28'',GCC1.SEGMENT28, ''SEGMENT29'',GCC1.SEGMENT29, ''SEGMENT30'',GCC1.SEGMENT30) = glsv.bal_seg_value
546 AND al.code_combination_id = gcc.code_combination_id
547 AND gcc.chart_of_accounts_id = :p_coa_id
548 AND FSAV.ATTRIBUTE_VALUE = ''Y''
549 AND FSAV.APPLICATION_ID = 101
550 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = ''GL_ACCOUNT''
551 AND FSAV.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
552 AND FSAV.ID_FLEX_CODE = ''GL#''
553 AND JCA.chart_of_accounts_id = :p_coa_id
554 @source_sql
555 AND DECODE(FSAV.APPLICATION_COLUMN_NAME,
556 ''SEGMENT1'',GCC.SEGMENT1, ''SEGMENT2'',GCC.SEGMENT2, ''SEGMENT3'',GCC.SEGMENT3,
557 ''SEGMENT4'',GCC.SEGMENT4, ''SEGMENT5'',GCC.SEGMENT5, ''SEGMENT6'',GCC.SEGMENT6,
558 ''SEGMENT7'',GCC.SEGMENT7, ''SEGMENT8'',GCC.SEGMENT8, ''SEGMENT9'',GCC.SEGMENT9,
559 ''SEGMENT10'',GCC.SEGMENT10, ''SEGMENT11'',GCC.SEGMENT11, ''SEGMENT12'',GCC.SEGMENT12,
560 ''SEGMENT13'',GCC.SEGMENT13, ''SEGMENT14'',GCC.SEGMENT14, ''SEGMENT15'',GCC.SEGMENT15,
561 ''SEGMENT16'',GCC.SEGMENT16, ''SEGMENT17'',GCC.SEGMENT17, ''SEGMENT18'',GCC.SEGMENT18,
562 ''SEGMENT19'',GCC.SEGMENT19, ''SEGMENT20'',GCC.SEGMENT20, ''SEGMENT21'',GCC.SEGMENT21,
563 ''SEGMENT22'',GCC.SEGMENT22, ''SEGMENT23'',GCC.SEGMENT23, ''SEGMENT24'',GCC.SEGMENT24,
564 ''SEGMENT25'',GCC.SEGMENT25, ''SEGMENT26'',GCC.SEGMENT26, ''SEGMENT27'',GCC.SEGMENT27,
565 ''SEGMENT28'',GCC.SEGMENT28, ''SEGMENT29'',GCC.SEGMENT29, ''SEGMENT30'',GCC.SEGMENT30) IN JCA.ACCOUNT_SEGMENT_VALUE';
566 IF (G_PROC_LEVEL >= g_debug_devel) THEN
567 FND_LOG.STRING(G_PROC_LEVEL,
568 G_MODULE_PREFIX || l_procedure_name || '.begin',
569 'Begin procedure');
570 END IF; --( G_PROC_LEVEL >= g_debug_devel)
571
572 --Get base currency code
573 SELECT currency_code, accounted_period_type
574 INTO l_func_currency_code, l_period_type
575 FROM gl_ledgers
576 WHERE ledger_id = p_ledger_id;
577
578 -- Get l_date_from, l_date_to
579 BEGIN
580 SELECT period_year * 1000 + period_num, start_date
581 INTO l_period_num_from, l_date_from
582 FROM gl_periods
583 WHERE period_set_name = p_period_set_name
584 AND period_name = P_GL_PERIOD_FROM
585 AND period_type = l_period_type;
586
587 SELECT period_year * 1000 + period_num, end_date
588 INTO l_period_num_to, l_date_to
589 FROM gl_periods
590 WHERE period_set_name = p_period_set_name
591 AND period_name = P_GL_PERIOD_to
592 AND period_type = l_period_type;
593
594 IF (G_STATEMENT_LEVEL >= g_debug_devel) THEN
595 put_log(G_MODULE_PREFIX || l_procedure_name || '.date range',
596 l_date_from || ':' || l_date_to);
597 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
598
599 EXCEPTION
600 WHEN NO_DATA_FOUND THEN
601 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel) THEN
602 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
603 G_MODULE_PREFIX || l_procedure_name ||
604 '.NO_DATA_FOUND',
605 'parameter periods wrong');
606 END IF;
607 RAISE;
608 END;
609
610 -- delete the record calculated before
611 IF p_source = 'ALL' THEN
612 DELETE ja_cn_cfs_activities_all ca
613 WHERE ca.legal_entity_id = P_LE_ID
614 AND ledger_id = p_ledger_id
615 AND ca.SOURCE_application_id NOT IN (101, 435)
616 AND ca.period_name IN
617 (SELECT period_name
618 FROM Gl_Periods
619 WHERE period_set_name = p_period_set_name
620 AND period_year * 1000 + period_num BETWEEN
621 l_period_num_from AND l_period_num_to);
622 COMMIT;
623 l_source_id := 101;
624 sql_stmt_sla := REPLACE(sql_stmt_sla,
625 '@source_sql',
626 'AND al.application_id <> :l_source_id');
627 sql_stmt_cash := REPLACE(sql_stmt_cash,
628 '@source_sql',
629 'AND al.application_id <> :l_source_id');
630
631 ELSE
632 BEGIN
633 SELECT application_id
634 INTO l_source_id
635 FROM fnd_application
636 WHERE application_short_name = p_source;
637
638 DELETE ja_cn_cfs_activities_all ca
639 WHERE ca.legal_entity_id = P_LE_ID
640 AND ledger_id = p_ledger_id
641 AND ca.SOURCE_application_id = l_source_id
642 AND ca.period_name IN
643 (SELECT period_name
644 FROM Gl_Periods
645 WHERE period_set_name = p_period_set_name
646 AND period_year * 1000 + period_num BETWEEN
647 l_period_num_from AND l_period_num_to);
648 COMMIT;
649 sql_stmt_sla := REPLACE(sql_stmt_sla,
650 '@source_sql',
651 'And al.application_id = :l_source_id');
652 sql_stmt_cash := REPLACE(sql_stmt_cash,
653 '@source_sql',
654 'And al.application_id = :l_source_id');
655
656 IF (G_STATEMENT_LEVEL >= g_debug_devel) THEN
657 put_log(G_MODULE_PREFIX || l_procedure_name || '.source',
658 p_source);
659 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
660
661 EXCEPTION
662 WHEN NO_DATA_FOUND THEN
663 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel) THEN
664 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
665 G_MODULE_PREFIX || l_procedure_name ||
666 '.NO_DATA_FOUND',
667 'parameter periods wrong');
668 END IF;
669 RAISE;
670 END;
671 END IF;
672
673 IF( G_STATEMENT_LEVEL >= g_debug_devel )
674 THEN
675 put_log(G_MODULE_PREFIX||l_procedure_name||'.chart of accounts id'
676 ,p_coa_id);
677 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
678
679 OPEN c_cash_lines FOR sql_stmt_cash
680 using l_date_from, l_date_to, p_ledger_id, p_coa_id,
681 p_coa_id,p_coa_id,p_ledger_id,p_le_id,p_coa_id,p_coa_id,l_source_id;
682 LOOP
683 FETCH c_cash_lines INTO l_cash_date,
684 l_accounting_class,
685 l_cash_amount_cr,
686 l_cash_amount_dr,
687 l_transaction_num;
688 EXIT WHEN c_cash_lines%NOTFOUND;
689 OPEN c_sla_lines FOR sql_stmt_sla using l_transaction_num,p_ledger_id, p_coa_id,p_ledger_id,p_le_id,l_source_id;
690 LOOP
691 FETCH c_sla_lines INTO l_ae_header_id,
692 l_ae_line_num,
693 l_transaction_date,
694 l_accounted_dr,
695 l_accounted_cr,
696 l_entered_dr,
697 l_entered_cr,
698 l_currency_code,
699 l_currency_conversion_rate,
700 l_currency_conversion_type,
701 l_currency_conversion_date,
702 l_application_id,
703 l_analytical_criterion_code,
704 l_ac_type_code,
705 l_amb_context_code,
706 l_ac_value,
707 l_ccid;--Fix bug#7334017 add
708 EXIT WHEN c_sla_lines%NOTFOUND;
709
710 --Get org id of the current sla ae line
711 Select xte.SECURITY_ID_INT_1
712 Into l_line_org_id
713 FROM XLA_TRANSACTION_ENTITIES xte, xla_ae_headers xah
714 Where xte.entity_id = xah.entity_id
715 And xte.application_id = xah.application_id
716 And xah.ae_header_id = l_ae_header_id
717 AND xah.application_id = l_application_id;
718
719 --Get the detailed cfs item when there is the mapping relationship of
720 --the current analytical criterion and sla ae line org id
721 --in the mapping table
722 BEGIN
723 Select jccl.detailed_cfs_item, jccl.org_id, jcch.event_class_code
724 Into l_detailed_cfs_item, L_mapping_org_id, l_event_class_code
725 From ja_cn_cfs_item_mapping_hdrs jcch,
726 ja_cn_cfs_item_mapping_lines jccl
727 Where jcch.Chart_of_Accounts_id = p_coa_id
728 And jcch.analytical_criterion_code = l_analytical_criterion_code
729 AND jcch.analytical_criterion_type_code = l_ac_type_code
730 AND jcch.amb_context_code = l_amb_context_code
731 And jcch.mapping_header_id = jccl.mapping_header_id
732 And jccl.ac_value = l_ac_value
733 AND nvl(jccl.effective_start_date, l_transaction_date) <=l_transaction_date
734 AND nvl(jccl.effective_end_date,l_transaction_date) >=l_transaction_date
735 And jccl.org_id = l_line_org_id;
736 EXCEPTION
737 WHEN no_data_found THEN
738 l_detailed_cfs_item := NULL;
739 l_mapping_org_id := NULL;
740 WHEN too_many_rows THEN
741 l_detailed_cfs_item := NULL;
742 l_mapping_org_id := NULL;
743 END;
744
745 If l_detailed_cfs_item is null THEN
746 -- Get the detailed cfs item when there is the mapping relationship of
747 -- the current analytical criterion in the mapping table and the org id is
748 -- null in the mapping table
749
750
751 BEGIN
752 Select jccl.detailed_cfs_item,jcch.event_class_code
753 Into l_detailed_cfs_item,l_event_class_code
754 From ja_cn_cfs_item_mapping_hdrs jcch,
755 ja_cn_cfs_item_mapping_lines jccl
756 Where jcch.Chart_of_Accounts_id = p_coa_id
757 And jcch.analytical_criterion_code = l_analytical_criterion_code
758 AND jcch.analytical_criterion_type_code = l_ac_type_code
759 AND jcch.amb_context_code = l_amb_context_code
760 And jcch.mapping_header_id = jccl.mapping_header_id
761 And jccl.ac_value = l_ac_value
762 AND nvl(jccl.effective_start_date,l_transaction_date) <= l_transaction_date
763 AND nvl(jccl.effective_end_date, l_transaction_date) >= l_transaction_date
764 And jccl.org_id is NULL;
765 EXCEPTION
766 WHEN no_data_found THEN
767 l_detailed_cfs_item := NULL;
768 WHEN too_many_rows THEN
769 l_detailed_cfs_item := NULL;
770 END;
771 If l_detailed_cfs_item is not null THEN
772
773 insert_SLA_data(P_COA_ID,
774 P_LEDGER_ID,
775 P_LE_ID,
776 p_period_set_name,
777 l_application_id,
778 l_ae_header_id,
779 l_ae_line_num,
780 l_transaction_date,
781 l_period_type,
782 l_func_currency_code,
783 l_currency_code,
784 l_currency_conversion_rate,
785 l_currency_conversion_type,
786 l_currency_conversion_date,
787 l_detailed_cfs_item,
788 l_EVENT_CLASS_CODE,
789 --l_application_id,
790 l_ANALYTICAL_CRITERION_CODE,
791 l_ac_value,
792 l_cash_date,
793 l_accounting_class,
794 l_cash_amount_cr,
795 l_cash_amount_dr,
796 l_accounted_dr,
797 l_entered_dr,
798 l_accounted_cr,
799 l_entered_cr,
800 l_ccid);--Fix bug#7334017 add
801 End IF;
802 Else
803 insert_SLA_data(P_COA_ID,
804 P_LEDGER_ID,
805 P_LE_ID,
806 p_period_set_name,
807 l_application_id,
808 l_ae_header_id,
809 l_ae_line_num,
810 l_transaction_date,
811 l_period_type,
812 l_func_currency_code,
813 l_currency_code,
814 l_currency_conversion_rate,
815 l_currency_conversion_type,
816 l_currency_conversion_date,
817 l_detailed_cfs_item,
818 l_EVENT_CLASS_CODE,
819 --l_application_id,
820 l_ANALYTICAL_CRITERION_CODE,
821 l_ac_value,
822 l_cash_date,
823 l_accounting_class,
824 l_cash_amount_cr,
825 l_cash_amount_dr,
826 l_accounted_dr,
827 l_entered_dr,
828 l_accounted_cr,
829 l_entered_cr ,
830 l_ccid);--Fix bug#7334017 add
831 End IF;
832 END LOOP;
833 CLOSE c_sla_lines;
834 COMMIT;
835 END LOOP;
836 CLOSE c_cash_lines;
837 IF( G_PROC_LEVEL >= g_debug_devel )
838 THEN
839 FND_LOG.STRING(G_PROC_LEVEL
840 ,G_MODULE_PREFIX||l_procedure_name||'.end'
841 ,'End procedure');
842 END IF; --( G_PROC_LEVEL >= g_debug_devel)
843 EXCEPTION
844 WHEN OTHERS THEN
845 IF(FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel)
846 THEN
847 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
848 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
849 , SQLCODE||':'||SQLERRM||p_coa_id);
850 END IF;
851 RAISE;
852 END collect_SLA_data;
853
854 end JA_CN_CFS_CLT_SLA_PKG;