[Home] [Help]
PACKAGE BODY: APPS.JA_CN_CFS_DATA_CLT_PKG
Source
1 PACKAGE BODY JA_CN_CFS_DATA_CLT_PKG AS
2 --$Header: JACNCDCB.pls 120.2.12010000.2 2008/10/28 06:13:51 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 GL/Intercompany/ |
13 --| AR/AP in the CNAO Project. |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Cfs_Data_Clt PUBLIC |
17 --| PROCEDURE Collect_GL_InterCom_Data PRIVATE |
18 --| FUNCTION GL_InterCom_Line_Check PRIVATE |
19 --| PROCEDURE Process_GL_Rounding PRIVATE |
20 --| PROCEDURE Collect_AR_Data PRIVATE |
21 --| PROCEDURE Collect_AP_Data PRIVATE |
22 --| PROCEDURE put_line PRIVATE |
23 --| PROCEDURE put_log PRIVATE |
24 --| FUNCTION get_period_name PUBLIC |
25 --| PROCEDURE collect_AR_data PUBLIC |
26 --| PROCEDURE process_AP_rounding PRIVATE |
27 --| PROCEDURE collect_AP_data PUBLIC |
28 --| |
29 --| HISTORY |
30 --| 03/01/2006 Andrew Liu Created |
31 --| 03/24/2006 Jogen Hu merge AR,AP parts |
32 --| 07/17/2006 Shujuan Yan In procedure Collect_AP_Data,Added |
33 --| the process when invoice amount is |
34 --| zero for bug 5393574. |
35 --| 30/10/2006 Andrew Liu Update Collect_GL_InterCom_Data for |
36 --| fix bug 5624013. |
37 --| 11/13/2006 Shujuan Added the logic of future dated |
38 --| payment for bug 5641261 |
39 --| 11/13/2006 Shujuan In procedure Collect_AP_Data, should|
40 --| store accounting date into gl_date |
41 --| of the table ja_cn_cfs_activities_gt|
42 --| for bug 5641324 |
43 --| 11/13/2006 Shujuan In procedure Collect_AR_Data, should|
44 --| store the gl_date into the table |
45 --| ja_cn_cfs_activities_all, not the |
46 --| posted gl date for bug 5651671 |
47 --| 5657210. |
48 --| 11/16/2006 Shujuan In procedure Collect_AP_Data,Payment|
49 --| should not be apporationed to the |
50 --| pre payment distribution line for |
51 --| bug 5664969 |
52 --| 16/11/2006 Andrew Liu Update GL_InterCom_Line_Check and |
53 --| Collect_GL_InterCom_Data for |
54 --| fix bug 5665083. |
55 --| 12/16/2006 Shujuan In the procedure Collect_AP_Data, |
56 --| should be based on payment base |
57 --| amount for bug 5700098 |
58 --| 12/16/2006 Shujuan In the procedure Process_AP_Rounding|
59 --| the cursor c_func_diff c_orig_diff |
60 --| should grouded by transaction id, |
61 --| functional amount and gl date for |
62 --| bug 5701909. |
63 --| 08/09/2008 Yao Zhang Fix bug 7334017 add balance segment |
64 --| value to table ja_cn_cfs_activities |
65 --| _all |
66 --| 17/10/2008 Yao Zhang Fix bug 7488191 TRX_NUMBER OF AGIS |
67 --| Data SHOULD Be BATCH NUMBER |
68 --| 17/10/2008 Yao Zhang Fix BUG 7488206 AGIS SOURCE |
69 --| TRANSACTION IS COLLECTED REPEATEDLY |
70 --| 21/10/2008 Yao Zhang Fix bug 7488223 DATA COLLECTION |
71 --| PROGRAM COLLECT AGIS DATA BEYOND BSV|
72 --| QUALIFICATION |
73 --+======================================================================*/
74
75 l_module_prefix VARCHAR2(100) :='JA_CN_CFS_DATA_CLT_PKG';
76
77 G_MODULE_PREFIX VARCHAR2(30):='JA_CN_CFS_DATA_CLT_PKG.';
78 G_PROC_LEVEL INT :=fnd_log.LEVEL_PROCEDURE;
79 G_STATEMENT_LEVEL INT :=fnd_log.LEVEL_STATEMENT;
80 g_debug_devel INT;
81 -- G_PERIOD_TYPE VARCHAR2(30):='Month';
82
83 --==========================================================================
84 -- FUNCTION NAME:
85 -- GL_InterCom_Line_Check private
86 --
87 -- DESCRIPTION:
88 -- This function checks whether line of GL journals OR Intercompany
89 -- transactions can be inserted or not.
90 --
91 -- PARAMETERS:
92 -- In: P_SOB_ID NUMBER ID of Set Of Book
93 -- In: P_LE_ID NUMBER ID of Legal Entity
94 -- In: P_SOURCE VARCHAR2 Source:GL/GIS
95 -- In: P_JT_ID VARCHAR2 ID of the Journal/Tr
96 -- In: P_LINE_NUM VARCHAR2 Number of the line
97 -- In: P_CCID NUMBER ID of chart of account
98 -- In: P_CASH_RELATED_ITEM VARCHAR2 Cash related item of the line
99 -- In: P_GIS_JNL_CRI VARCHAR2 Cash related item of the transfered
100 -- jounarl line of a transaction
101 -- RETURN:
102 -- VARCHAR2
103 -- 'Y' for passed and 'N' for not pass.
104 --
105 -- DESIGN REFERENCES:
106 -- None
107 --
108 -- CHANGE HISTORY:
109 -- 03/01/2006 Andrew Liu Created
110 -- 16/11/2006 Andrew Liu Added the logic of check cash related item
111 -- of transaction's transferred GL journal
112 -- for fix bug 5665083.
113 -- 04/21/2007 Yucheng Sun Updated:
114 --===========================================================================
115 FUNCTION GL_InterCom_Line_Check( P_COA_ID IN NUMBER
116 ,P_LEDGER_ID IN NUMBER
117 ,P_LE_ID IN NUMBER
118 ,P_SOURCE IN VARCHAR2
119 ,P_JT_ID IN NUMBER
120 ,P_LINE_NUM IN VARCHAR2
121 ,P_CCID IN NUMBER
122 ,P_CASH_RELATED_ITEM IN VARCHAR2
123 ,P_GIS_JNL_CRI OUT NOCOPY VARCHAR2
124 ) RETURN VARCHAR2 IS
125 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
126 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
127 l_proc_name VARCHAR2(100) :='GL_InterCom_Line_Check';
128
129 l_ledger_id NUMBER := P_LEDGER_ID; --
130 l_le_id NUMBER := P_LE_ID;
131 l_source VARCHAR2(10) := P_SOURCE;
132 l_coa_id NUMBER := P_COA_ID;
133 l_jt_id NUMBER := P_JT_ID;
134 l_line_num VARCHAR2(20) := P_LINE_NUM;
135 l_cc_id NUMBER := P_CCID;
136 l_csi_check varchar2(2) := P_CASH_RELATED_ITEM;
137 l_tr_csi_check varchar2(150);
138
139 l_seg_type FND_SEGMENT_ATTRIBUTE_VALUES.SEGMENT_ATTRIBUTE_TYPE%TYPE;
140 l_ffv_flex_value varchar2(150);
141 l_seg_fsav_gcc varchar2(150);
142
143 l_account_num varchar2(150); --account number of a line's account
144 l_com_seg varchar2(150); --company segment of a line's account
145 l_com_seg_check number; --flag of an account's company segment belongs to current LE or not
146 l_account_check number; --flag of an account in cash related table or not
147 l_line_check varchar2(1); --result of a line's validation
148
149 --Cursor to get FND_FLEX_VALUES.Flex_Value and segment FND_SEGMENT_ATTRIBUTE_VALUES.APPLICATION_COLUMN_NAME of gcc
150 CURSOR c_ffv IS
151 SELECT FFV.Flex_Value ffv_flex_value
152 ,DECODE(FSAV.APPLICATION_COLUMN_NAME, --segment FSAV.APPLICATION_COLUMN_NAME of gcc
153 'SEGMENT1',GCC.SEGMENT1, 'SEGMENT2',GCC.SEGMENT2, 'SEGMENT3',GCC.SEGMENT3,
154 'SEGMENT4',GCC.SEGMENT4, 'SEGMENT5',GCC.SEGMENT5, 'SEGMENT6',GCC.SEGMENT6,
155 'SEGMENT7',GCC.SEGMENT7, 'SEGMENT8',GCC.SEGMENT8, 'SEGMENT9',GCC.SEGMENT9,
156 'SEGMENT10',GCC.SEGMENT10, 'SEGMENT11',GCC.SEGMENT11, 'SEGMENT12',GCC.SEGMENT12,
157 'SEGMENT13',GCC.SEGMENT13, 'SEGMENT14',GCC.SEGMENT14, 'SEGMENT15',GCC.SEGMENT15,
158 'SEGMENT16',GCC.SEGMENT16, 'SEGMENT17',GCC.SEGMENT17, 'SEGMENT18',GCC.SEGMENT18,
159 'SEGMENT19',GCC.SEGMENT19, 'SEGMENT20',GCC.SEGMENT20, 'SEGMENT21',GCC.SEGMENT21,
160 'SEGMENT22',GCC.SEGMENT22, 'SEGMENT23',GCC.SEGMENT23, 'SEGMENT24',GCC.SEGMENT24,
161 'SEGMENT25',GCC.SEGMENT25, 'SEGMENT26',GCC.SEGMENT26, 'SEGMENT27',GCC.SEGMENT27,
162 'SEGMENT28',GCC.SEGMENT28, 'SEGMENT29',GCC.SEGMENT29, 'SEGMENT30',GCC.SEGMENT30)
163 seg_fsav_gcc
164 FROM GL_CODE_COMBINATIONS gcc
165 ,GL_LEDGERS ledger
166 ,FND_ID_FLEX_SEGMENTS FIFS
167 ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
168 ,FND_FLEX_VALUE_SETS FFVS
169 ,FND_FLEX_VALUES FFV
170 WHERE gcc.code_combination_id = l_cc_id --using variable l_cc_id
171 AND gcc.chart_of_accounts_id = l_coa_id --using variable P_COA_ID
172 AND ledger.chart_of_accounts_id = ledger.chart_of_accounts_id
173 AND ledger.ledger_id = l_ledger_id --using variable l_sob_id
174 AND FIFS.id_flex_num = gcc.chart_of_accounts_id
175 AND FIFS.id_flex_num = FSAV.id_flex_num
176 AND FIFS.application_id = 101 -- seeded data
177 AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
178 AND FIFS.application_id = FSAV.application_id
179 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = l_seg_type --using variable l_seg_type ,'gl_account'
180 AND FSAV.ATTRIBUTE_VALUE = 'Y'
181 AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
182 AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID;
183
184 BEGIN
185 --log for debug
186 IF (l_proc_level >= l_dbg_level)
187 THEN
188 FND_LOG.String( l_proc_level
189 ,l_module_prefix||'.'||l_proc_name||'.begin'
190 ,'Enter procedure for Journal/Transaction '||TO_CHAR(l_jt_id)
191 || '''s Line ' || l_line_num
192 );
193 END IF; --(l_proc_level >= l_dbg_level)
194
195 l_line_check := 'N';
196 P_GIS_JNL_CRI := null;
197
198 --Get account segment
199 l_seg_type := 'GL_ACCOUNT';
200 OPEN c_ffv;
201 LOOP
202 FETCH c_ffv INTO l_ffv_flex_value
203 ,l_seg_fsav_gcc;
204 EXIT WHEN c_ffv%NOTFOUND;
205 IF l_ffv_flex_value = l_seg_fsav_gcc THEN
206 l_account_num := l_ffv_flex_value;
207 EXIT;
208 END IF;
209 END LOOP;
210 CLOSE c_ffv;
211
212 IF l_account_num is not null
213 THEN
214 --check the line's account is cash related or not.
215 --Account is cash related one when its number is in table JA_CN_CASH_ACCOUNTS_ALL
216 SELECT count(*) row_count
217 INTO l_account_check
218 FROM JA_CN_CASH_ACCOUNTS_ALL cash_acc
219 WHERE cash_acc.ACCOUNT_SEGMENT_VALUE = TO_CHAR(l_account_num) --using variable l_account_num
220 AND cash_acc.chart_of_accounts_id = l_coa_id; --using variable P_COA_ID
221 --AND cash_acc.SET_OF_BOOKS_ID = l_sob_id; --using variable l_sob_id
222
223 IF l_source = 'GL'
224 THEN
225 --Get account's company segment
226 l_seg_type := 'GL_BALANCING';
227 -- ?? why Loop again ??
228 OPEN c_ffv;
229 LOOP
230 FETCH c_ffv INTO l_ffv_flex_value
231 ,l_seg_fsav_gcc;
232 EXIT WHEN c_ffv%NOTFOUND;
233 IF l_ffv_flex_value is not null AND l_seg_fsav_gcc is not null AND
234 l_ffv_flex_value = l_seg_fsav_gcc
235 THEN
236 l_com_seg := l_ffv_flex_value;
237 EXIT;
238 END IF;
239 END LOOP;
240 CLOSE c_ffv;
241
242 IF l_com_seg is not null
243 THEN
244 --check the company segment belongs to the current legal entity or not.
245 l_com_seg_check := 0;
246 SELECT count(*) row_count
247 INTO l_com_seg_check
248 FROM JA_CN_LEDGER_LE_BSV_GT tmpbsv
249 WHERE tmpbsv.LEGAL_ENTITY_ID = l_le_id --using variable l_le_id
250 AND tmpbsv.ledger_id = l_ledger_id --using variable l_ledger_id
251 AND tmpbsv.bal_seg_value = l_com_seg; --using variable l_com_seg
252
253 IF l_com_seg_check > 0 AND --the company segment belongs to the current legal entity
254 l_account_check > 0 AND --the line's account is cash related
255 l_csi_check = 'NB'
256 THEN
257 l_line_check := 'Y';
258 END IF;
259 END IF; --l_com_seg is not null
260
261 ELSE
262 --l_source = 'AGIS'
263 -- fix bug 5665083, 2006-11-16, Andrew:
264 -- should check the cash related item of GL journal transferred from this line
265 -- when its cash related item is balnk.
266 --IF l_csi_check = 'B' THEN
267 --Get company segment
268 l_seg_type := 'GL_BALANCING';
269
270 OPEN c_ffv;
271 LOOP
272 FETCH c_ffv INTO l_ffv_flex_value
273 ,l_seg_fsav_gcc;
274 EXIT WHEN c_ffv%NOTFOUND;
275 IF l_ffv_flex_value is not null AND l_seg_fsav_gcc is not null AND
276 l_ffv_flex_value = l_seg_fsav_gcc
277 THEN
278 l_com_seg := l_ffv_flex_value;
279 EXIT;
280 END IF;
281 END LOOP;
282 CLOSE c_ffv;
283
284 l_com_seg_check := 0;
285 IF l_com_seg is not null
286 THEN
287 --check the company segment belongs to the current legal entity or not.
288 l_com_seg_check := 0;
289 SELECT count(*) row_count
290 INTO l_com_seg_check
291 FROM JA_CN_LEDGER_LE_BSV_GT tmpbsv
292 WHERE tmpbsv.LEGAL_ENTITY_ID = l_le_id --using variable l_le_id
293 AND tmpbsv.ledger_id = l_ledger_id --using variable l_ledger_id
294 AND tmpbsv.bal_seg_value = TO_CHAR(l_com_seg); --using variable l_com_seg
295 END IF;
296 /*
297 ----------------------------for test--------------------------------------------
298 SELECT count(*) row_count
299 INTO l_com_seg_check
300 FROM JA_CN_LEDGER_LE_BSV_GT tmpbsv
301 WHERE tmpbsv.LEGAL_ENTITY_ID = l_le_id --using variable l_le_id
302 AND tmpbsv.ledger_id = l_ledger_id; --using variable l_ledger_id
303 ----------------------------fro test--------------------------------------------
304 */
305
306 IF l_com_seg_check >0 THEN
307 -- get the cash relate item from GL
308 BEGIN
309 SELECT decode(jel.context, dffa.context_code,
310 decode(dffa.attribute_column, 'ATTRIBUTE1',jel.attribute1, 'ATTRIBUTE2',jel.attribute2,
311 'ATTRIBUTE3',jel.attribute3, 'ATTRIBUTE4',jel.attribute4, 'ATTRIBUTE5',jel.attribute5,
312 'ATTRIBUTE6',jel.attribute6, 'ATTRIBUTE7',jel.attribute7, 'ATTRIBUTE8',jel.attribute8,
313 'ATTRIBUTE9',jel.attribute9, 'ATTRIBUTE10',jel.attribute10, 'ATTRIBUTE11',jel.attribute11,
314 'ATTRIBUTE12',jel.attribute12, 'ATTRIBUTE13',jel.attribute13, 'ATTRIBUTE14',jel.attribute14,
315 'ATTRIBUTE15',jel.attribute15)
316 ) cash_related_item
317 INTO l_tr_csi_check
318 FROM gl_je_lines jel
319 , fun_trx_headers trxh
320 , fun_trx_lines trxl
321 , fun_dist_lines distl
322 ,ja_cn_dff_assignments dffa --
323 WHERE distl.dist_id = l_jt_id -- transaction header id
324 AND distl.line_id=trxl.line_id
325 AND trxh.trx_id = trxl.trx_id
326 AND jel.reference_2 = TO_CHAR(trxh.batch_id)
327 AND jel.reference_3 = TO_CHAR(trxh.trx_id)
328 AND jel.reference_4 = TO_CHAR(trxl.line_id)
329 AND jel.reference_5 = TO_CHAR(distl.dist_id)
330 AND jel.ledger_id=l_ledger_id -- care only current ledgers'
331 AND jel.status='P' -- care only post journels from trxes
332 -- to locate cash flow item in dff_assignment
333 AND dffa.Application_Id = 101
334 AND dffa.chart_of_accounts_id = l_coa_id --using variable l_coa_id
335 AND dffa.dff_title_code='GLLI';
336 Exception
337 WHEN NO_DATA_FOUND THEN
338 l_tr_csi_check := null; -- not sure
339 -- the trx had been transfered to GL, but not post, which will be excluded
340 l_line_check := 'N';
341 return 'N';
342 END;
343
344 IF l_tr_csi_check is not null
345 THEN
346 P_GIS_JNL_CRI := l_tr_csi_check;
347 l_csi_check := 'NB';
348 END IF;
349 END IF;
350 --END IF;
351
352 IF l_account_check > 0 AND --the line's account is cash related
353 l_csi_check = 'NB'
354 AND l_com_seg_check >0 --fix bug 7488223 add
355 THEN
356 l_line_check := 'Y';
357 END IF;
358 END IF; --l_source = 'GL'/'GIS'
359 END IF; --l_account_num is not null
360
361 --log for debug
362 IF (l_proc_level >= l_dbg_level)
363 THEN
364 FND_LOG.String( l_proc_level
365 ,l_module_prefix||'.'||l_proc_name||'.end'
366 ,'Exit procedure for Journal/Transaction '||TO_CHAR(l_jt_id)
367 || '''s Line ' || l_line_num
368 ||' and its l_line_check is ' || l_line_check
369 );
370 END IF; --(l_proc_level >= l_dbg_level)
371 return l_line_check;
372
373 EXCEPTION
374 WHEN OTHERS THEN
375 IF (l_proc_level >= l_dbg_level)
376 THEN
377 FND_LOG.String( l_proc_level
378 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
379 ,SQLCODE||':'||SQLERRM
380 );
381 FND_LOG.String( l_proc_level
382 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
383 ,'For Journal/Transaction '||TO_CHAR(l_jt_id)
384 || '''s Line ' || l_line_num
385 || ', So set the check as ''N'', meaning fall the check.'
386 );
387 END IF; --(l_proc_level >= l_dbg_level)
388 return 'N';
389 END GL_InterCom_Line_Check;
390
391
392 --==========================================================================
393 -- FUNCTION NAME:
394 -- AGIS_Get_Curr_Rate_Type private
395 --
396 -- DESCRIPTION:
397 -- This function checks the type of item: sender or receiver
398 -- return the currency rate and type
399 --
400 -- PARAMETERS:
401 -- In: P_LEDGER_ID NUMBER ID of Set Of ledger
402 -- In: P_LE_ID NUMBER ID of Legal Entity
403 -- In: P_AGIS_LE_ID NUMBER Source:GL/GIS
404 -- In: P_AGIS_LEDGER_ID NUMBER ID of the ledger in agis
405 -- In: P_GL_DATE DATE ID of the legal entity in agis
406 -- In: P_AGIS_CURR_COV_TYPE VARCHAR2(30) Currency convert type in AGIS
407 -- In: P_AGIS_CURR_CODE VARCHAR2(15) Currenc code in AGIS
408 -- OUT: P_AGIS_CURR_RATE NUMBER Currency rate to be got form the daily rates table
409 --
410 -- RETURN:
411 -- boolean
412 -- true for getatable value and false for no value got.
413 --
414 -- DESIGN REFERENCES:
415 -- None
416 --
417 -- CHANGE HISTORY:
418 -- 04/10/2007 Yucheng Sun Created
419 --===========================================================================
420 FUNCTION AGIS_Get_Curr_Rate_Type( P_LEDGER_ID IN NUMBER
421 ,P_LE_ID IN NUMBER
422 ,P_AGIS_LE_ID IN NUMBER
423 ,P_AGIS_LEDGER_ID IN NUMBER
424 ,P_GL_DATE IN DATE
425 ,P_AGIS_CURR_COV_TYPE IN VARCHAR2
426 ,P_AGIS_CURR_CODE IN VARCHAR2
427 ,P_AGIS_CURR_RATE OUT NOCOPY NUMBER
428
429 ) RETURN boolean IS
430
431 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
432 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
433 l_proc_name VARCHAR2(100) :='GL_InterCom_Line_Check';
434
435 --l_trx_curr_cov_type GL_DAILY_RATES.CONVERSION_TYPE%TYPE;
436 --l_trx_curr_cov_rate GL_DAILY_RATES.CONVERSION_RATE%TYPE;
437 l_gl_curr_code GL_LEDGERS.Currency_Code%TYPE;
438
439 BEGIN
440 --log for debug
441 IF (l_proc_level >= l_dbg_level)
442 THEN
443 FND_LOG.String( l_proc_level
444 ,l_module_prefix||'.'||l_proc_name||'.begin'
445 ,'Enter procedure for Journal/Transaction '
446 --||TO_CHAR(l_jt_id) || '''s Line ' || l_line_num
447 );
448 END IF; --(l_proc_level >= l_dbg_level)
449
450 --check whether the currency code is the same to its function currency code
451 SELECT leg.currency_code into l_gl_curr_code
452 FROM GL_LEDGERS leg
453 WHERE leg.ledger_id=P_LEDGER_ID;
454
455 IF l_gl_curr_code=P_AGIS_CURR_CODE THEN
456 -- in the same currency code,the convert rate is 1
457 P_AGIS_CURR_RATE:=1;
458 RETURN TRUE;
459 END IF;
460
461 /*
462 -- ?? To deal with the null situation of currency convertion type
463 IF NVL(P_AGIS_CURR_COV_TYPE,'')='' THEN
464 -- there should be a error message
465 RETURN FALSE;
466 END IF;
467 */
468
469 -- get the covertion rate between different currency codes
470 SELECT cur.conversion_rate INTO P_AGIS_CURR_RATE
471 FROM GL_DAILY_RATES cur
472 WHERE cur.from_currency =P_AGIS_CURR_CODE
473 AND cur.to_currency=l_gl_curr_code
474 AND cur.conversion_type=P_AGIS_CURR_COV_TYPE
475 AND cur.conversion_date=P_GL_DATE;
476
477 IF P_AGIS_CURR_RATE IS NULL THEN
478 P_AGIS_CURR_RATE:=1;
479 RETURN FALSE;
480 ELSE
481 RETURN TRUE;
482 END IF;
483
484 EXCEPTION
485 WHEN OTHERS THEN
486 IF (l_proc_level >= l_dbg_level)
487 THEN
488 FND_LOG.String( l_proc_level
489 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
490 ,SQLCODE||':'||SQLERRM
491 );
492 FND_LOG.String( l_proc_level
493 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
494 ,--'For Journal/Transaction '||TO_CHAR(l_jt_id)
495 --|| '''s Line ' || l_line_num
496 ''|| ', So set the check as ''N'', meaning fall the check.'
497 );
498 END IF; --(l_proc_level >= l_dbg_level)
499 return FALSE;
500 END AGIS_Get_Curr_Rate_Type;
501
502
503 --==========================================================================
504 -- PROCEDURE NAME:
505 -- Process_GL_Rounding Public
506 --
507 -- DESCRIPTION:
508 -- This procedure is used to process amount rounding in GL/Intercompany.
509 --
510 -- PARAMETERS:
511 -- In: P_LE_ID VARCHAR2 ID of Legal Entity
512 -- P_AMOUNT NUMBER Amount
513 -- P_CURRENCY_CODE VARCHAR2 Code of the currency
514 -- Out:P_AMOUNT_ROUNDED NUMBER Rounded amount
515 --
516 -- DESIGN REFERENCES:
517 -- CNAO_CFS_Data_collection_TD.doc
518 --
519 -- CHANGE HISTORY:
520 -- 03/01/2006 Jogen Hu Created
521 --===========================================================================
522 PROCEDURE Process_GL_Rounding( P_LE_ID IN VARCHAR2
523 ,P_AMOUNT IN NUMBER
524 ,P_CURRENCY_CODE IN VARCHAR2
525 ,P_AMOUNT_ROUNDED OUT NOCOPY NUMBER
526 ) IS
527 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
528 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
529 l_proc_name VARCHAR2(100) :='Process_GL_Rounding';
530
531 l_amount NUMBER := P_AMOUNT;
532 l_currency_code VARCHAR2(15) := P_CURRENCY_CODE;
533 l_precision NUMBER;
534 l_amount_rounded NUMBER;
535 l_round_flag ja_cn_system_parameters_all.rounding_rule%TYPE;
536
537 BEGIN
538 --log for debug
539 IF (l_proc_level >= l_dbg_level)
540 THEN
541 FND_LOG.String( l_proc_level
542 ,l_module_prefix||'.'||l_proc_name||'.begin'
543 ,'Enter procedure'
544 );
545 END IF; --(l_proc_level >= l_dbg_level)
546
547 BEGIN
548 SELECT rounding_rule
549 INTO l_round_flag
550 FROM ja_cn_system_parameters_all
551 WHERE legal_entity_id = P_LE_ID; --Using parameter P_LE_ID
552
553 EXCEPTION
554 WHEN NO_DATA_FOUND THEN
555 IF(l_proc_level >= l_dbg_level)
556 THEN
557 FND_LOG.string( l_proc_level
558 ,l_module_prefix||'.'||l_proc_name||'.NO_DATA_FOUND'
559 ,'The legal entity has no system parameters defined'
560 );
561 END IF;
562 RAISE;
563 END;
564
565 SELECT PRECISION
566 INTO l_precision
567 FROM fnd_currencies
568 WHERE currency_code=l_currency_code;
569
570 --rounding
571 IF l_round_flag = 'N' --'NEAREST'
572 THEN
573 l_amount_rounded := round(l_amount, l_precision);
574 ELSIF l_round_flag = 'D' --'DOWN'
575 THEN
576 l_amount_rounded := trunc(l_amount, l_precision);
577 ELSE --l_round_flag = 'U' --'UP'
578 l_amount_rounded := ceil(l_amount*power(10,l_precision))
579 /power(10, l_precision);
580 END IF;
581
582 P_AMOUNT_ROUNDED := l_amount_rounded;
583
584 --log for debug
585 IF (l_proc_level >= l_dbg_level)
586 THEN
587 FND_LOG.String( l_proc_level
588 ,l_module_prefix||'.'||l_proc_name||'.end'
589 ,'Exit procedure'
590 );
591 END IF; --(l_proc_level >= l_dbg_level)
592
593 EXCEPTION
594 WHEN OTHERS THEN
595 IF (l_proc_level >= l_dbg_level)
596 THEN
597 FND_LOG.String( l_proc_level
598 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
599 ,SQLCODE||':'||SQLERRM
600 );
601 END IF; --(l_proc_level >= l_dbg_level)
602 RAISE;
603 END Process_GL_Rounding;
604
605
606 --==========================================================================
607 -- PROCEDURE NAME:
608 -- Collect_GL_InterCom_Data private
609 --
610 -- DESCRIPTION:
611 -- This procedure collects data from GL journals OR Intercompany transactions.
612 --
613 -- PARAMETERS:
614 -- In: P_SOB_ID NUMBER ID of Set Of Book
615 -- In: P_LE_ID NUMBER ID of Legal Entity
616 -- In: P_PERIOD_SET_NAME VARCHAR2 Name of the period set
617 -- in the set of book
618 -- In: P_GL_PERIOD_FROM VARCHAR2 Start period
619 -- In: P_GL_PERIOD_TO VARCHAR2 End period
620 -- In: P_SOURCE_APP_ID NUMBER The soure id
621 --
622 -- DESIGN REFERENCES:
623 -- None
624 --
625 -- CHANGE HISTORY:
626 -- 03/01/2006 Andrew Liu Created
627 -- 30/10/2006 Andrew Liu Added the logic of brother relationship
628 -- check in GIS collection to fix bug 5624013.
629 -- 16/11/2006 Andrew Liu Added the logic of exclude GIS, AP, AR
630 -- in GL collection for fix bug 5665083.
631 -- 04/02/2007 Yucheng Sun Updated to Release 12.0,
632 -- 08/09/2008 Yao Zhang Fix bug #7334017 for R12 enhancement
633 --===========================================================================
634 PROCEDURE Collect_GL_InterCom_Data( P_COA_ID IN NUMBER
635 ,P_LEDGER_ID IN NUMBER
636 ,P_LE_ID IN NUMBER
637 ,P_PERIOD_SET_NAME IN VARCHAR2
638 ,P_GL_PERIOD_FROM IN VARCHAR2
639 ,P_GL_PERIOD_TO IN VARCHAR2
640 ,P_SOURCE_APP_ID IN NUMBER
641 ) IS
642 --variables
643 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
644 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
645 l_proc_name VARCHAR2(100) :='Collect_GL_InterCom_Data';
646
647 l_le_id NUMBER :=P_LE_ID;
648 l_ledger_id NUMBER :=P_LEDGER_ID;
649 l_coa_id NUMBER :=P_COA_ID;
650 l_source_application_id NUMBER :=P_SOURCE_APP_ID;
651 l_source_name fnd_application.application_short_name%TYPE;
652 -- PERIODS e current item
653 l_period_name gl_periods.period_name%TYPE; --period name of th
654 l_period_year gl_periods.period_year%TYPE;
655 l_period_num gl_periods.period_num%TYPE;
656 l_period_start_date gl_periods.start_date%TYPE;
657 l_period_end_date gl_periods.end_date%TYPE;
658 l_period_name_from gl_periods.period_name%TYPE:=P_GL_PERIOD_FROM; --period name of th
659 l_period_name_to gl_periods.period_name%TYPE:=P_GL_PERIOD_TO; --period name of th
660
661 -- GL: GL_JE_HEADER
662 l_je_func_curr_code gl_sets_of_books.currency_code%TYPE;
663 l_je_header_id GL_JE_HEADERS.JE_HEADER_ID%TYPE;
664 l_je_catg GL_JE_HEADERS.je_category%TYPE;
665 l_je_jnl_name GL_JE_HEADERS.name%TYPE;
666 l_je_jnl_doc_seq_num GL_JE_HEADERS.Doc_Sequence_Value%TYPE;
667 l_je_curr_code GL_JE_HEADERS.currency_code%TYPE;
668 l_je_curr_cov_rate GL_JE_HEADERS.currency_conversion_rate%TYPE;
669 l_je_curr_cov_type GL_JE_HEADERS.currency_conversion_type%TYPE;
670 l_je_curr_cov_date GL_JE_HEADERS.currency_conversion_date%TYPE;
671 l_je_inter_flag GL_JE_HEADERS.global_attribute1%TYPE;
672 l_je_effective_date GL_JE_HEADERS.DEFAULT_EFFECTIVE_DATE%TYPE;
673 l_je_line_num GL_JE_LINES.je_line_num%TYPE;
674 l_je_line_desc GL_JE_LINES.description%TYPE;
675 l_je_line_amount NUMBER;
676 l_je_line_func_amount NUMBER;
677 -- GL: Gl_JE_LINES
678 l_ccid GL_CODE_COMBINATIONS.Code_Combination_Id%TYPE;
679 l_cash_related_item varchar2(150); --cash related item of a line
680 l_cash_related_item_1 varchar2(150); --copy of cash related item of a line
681 l_csi_check varchar2(2); --blank or not of cash related item
682 l_line_check varchar2(16); --result of a line's validation
683 -- AGIS: FUN_TRX_BATCHES
684 l_trxb_batch_id fun_trx_batches.batch_id%TYPE;
685 l_trxb_batch_num fun_trx_batches.batch_number%TYPE;
686 l_trxb_gl_date fun_trx_batches.gl_date%TYPE;
687 l_trxb_curr_code fun_trx_batches.currency_code%TYPE;
688 l_trxb_entered_date fun_trx_batches.batch_date%TYPE;
689 l_trxb_from_le_id fun_trx_batches.from_le_id%TYPE;
690 l_trxb_from_ledger_id fun_trx_batches.from_ledger_id%TYPE;
691 l_trxb_curr_cov_type fun_trx_batches.exchange_rate_type%TYPE; --currency convertion type
692 -- AGIS: FUN_TRX_HEADERES
693 l_trxh_header_id fun_trx_headers.trx_id%TYPE;
694 l_trxh_header_num fun_trx_headers.trx_number%TYPE;
695 l_trxh_desc fun_trx_headers.description%TYPE;
696 l_trxh_initiator_id fun_trx_headers.initiator_id%TYPE;
697 l_trxh_recipient_id fun_trx_headers.recipient_id%TYPE;
698 l_trxh_to_le_id fun_trx_headers.to_le_id%TYPE;
699 l_trxh_to_ledger_id fun_trx_headers.to_ledger_id%TYPE;
700 l_trxh_init_amount_cr fun_trx_headers.init_amount_cr%TYPE;
701 l_trxh_init_amount_dr fun_trx_headers.init_amount_dr%TYPE;
702 l_trxh_reci_amount_cr fun_trx_headers.reci_amount_cr%TYPE;
703 l_trxh_reci_amount_dr fun_trx_headers.reci_amount_dr%TYPE;
704 l_distl_cash_rel_item fun_dist_lines.attribute1%TYPE;
705 l_distl_cash_rel_item_l fun_dist_lines.attribute1%TYPE;
706 -- AGIS: FUN_TRX_LINES
707 l_trxl_num fun_trx_lines.line_id%TYPE;
708 l_trxl_id fun_trx_lines.trx_id%TYPE;
709 l_distl_party_id fun_dist_lines.party_id%Type;
710 l_distl_party_type_flg fun_dist_lines.party_type_flag%TYPE;
711 l_distl_dist_type_flg fun_dist_lines.dist_type_flag%TYPE;
712 l_distl_amount_cr fun_dist_lines.amount_cr%TYPE;
713 l_distl_amount_dr fun_dist_lines.amount_dr%TYPE;
714 l_distl_number fun_dist_lines.dist_number%TYPE;
715 l_distl_ccid fun_dist_lines.ccid%TYPE;
716 l_codecmb_coa_id gl_code_combinations.chart_of_accounts_id%TYPE;
717 l_codecmb_com_seg gl_code_combinations.segment1%TYPE;
718 l_distl_id NUMBER;
719
720 -- AGIS: CURRENCY RATE
721 l_trxh_curr_cov_rate GL_DAILY_RATES.CONVERSION_RATE%TYPE := 1;
722
723 -- AGIS: tempory parameters, to deal with the data in logic no matter it is sender or receiver;
724 l_current_initiator_id fun_trx_headers.initiator_id%TYPE;
725 l_current_recipient_id fun_trx_headers.recipient_id%TYPE;
726 l_current_amount_cr fun_trx_headers.init_amount_cr%TYPE;
727 l_current_amount_dr fun_trx_headers.init_amount_cr%TYPE;
728 l_get_trx_cov_rate_flg BOOLEAN := FALSE;
729 l_tr_func_amount number:=0;
730
731 --cursor
732 --GL
733 -- Cursor to get all periods between (P_GL_PERIOD_FROM, P_GL_PERIOD_TO).
734 CURSOR c_period_name IS
735 SELECT gp.period_name
736 , gp.period_year
737 , gp.period_num
738 , gp.start_date
739 , gp.end_date
740 FROM gl_periods gp, GL_LEDGERS ledger
741 WHERE ledger.ledger_id = l_ledger_id --using variable P_LEDGER_ID
742 AND ledger.period_set_name = gp.PERIOD_SET_NAME
743 AND ledger.accounted_period_type = gp.period_type
744 AND gp.start_date between
745 (SELECT start_date
746 FROM GL_PERIODS GP
747 WHERE ledger.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
748 AND ledger.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
749 AND GP.period_name = l_period_name_from) --using parameter P_START_PERIOD
750 and (SELECT start_date
751 FROM GL_PERIODS GP
752 WHERE ledger.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
753 AND ledger.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
754 AND GP.period_name = l_period_name_to) --using parameter P_END_PERIOD
755 ORDER BY gp.start_date
756 ;
757
758 -- Cursor to get information of GL journals in the specified period,
759 -- Only consider Journals whose categories is Cash Related and Status is Posted.
760 CURSOR c_gl IS
761 SELECT jeh.je_header_id jnl_id
762 ,jeh.je_category jnl_catg
763 ,jeh.name jnl_name
764 ,jeh.doc_sequence_value jnl_doc_seq_num
765 ,jeh.currency_code curr_code
766 ,jeh.currency_conversion_rate curr_cov_rate
767 ,jeh.currency_conversion_type curr_cov_type
768 ,jeh.currency_conversion_date curr_cov_date
769 ,decode(jeh.global_attribute_category, 'JE.CN.GLXJEENT.HEADER',
770 nvl(jeh.global_attribute1, 'N'), 'N'
771 ) inter_flag
772 ,jeh.DEFAULT_EFFECTIVE_DATE effective_date
773 FROM GL_JE_HEADERS jeh
774 ,gl_je_categories_tl jec
775 ,JA_CN_DFF_ASSIGNMENTS DFF
776 WHERE jeh.ledger_id = l_ledger_id --using variable P_LEDGER_ID
777 AND jeh.period_name = l_period_name --using variable l_period_name
778 --check the Journal Category is Cash Related or not
779 AND jeh.je_category = jec.je_category_name --user_je_category_name
780 AND DFF.DFF_TITLE_CODE = 'JOCA'
781 AND jec.context = DFF.CONTEXT_CODE
782 AND jec.language = userenv('LANG')
783 AND nvl(DECODE(DFF.ATTRIBUTE_COLUMN, 'ATTRIBUTE1',jec.attribute1, 'ATTRIBUTE2',jec.attribute2,
784 'ATTRIBUTE3',jec.attribute3, 'ATTRIBUTE4',jec.attribute4, 'ATTRIBUTE5',jec.attribute5),
785 'N') = 'Y'
786 -- to locate cash flow item in dff_assignment
787 AND dff.Application_Id = 101
788 AND dff.chart_of_accounts_id = l_coa_id --using variable l_coa_id
789 --check the Journal's Status
790 AND jeh.status = 'P'
791 --fix bug 5665083, 2006-11-16, Andrew/ 4/03/2007, Altered by Yucheng.Sun :
792 -- should exclude AGIS, AP, AR in GL data collection.
793 AND jeh.je_source NOT IN ('Intercompany')--, 'Payables', 'Receivables'
794 -- exclude the source defined in in cash flow item mapping form (ja_cn_cfs_item_mapping_hdrs)
795 AND jeh.je_source NOT IN ( SELECT FAPP.APPLICATION_SHORT_NAME
796 FROM FND_APPLICATION FAPP, JA_CN_CFS_ITEM_MAPPING_HDRS JCCIMH
797 WHERE FAPP.APPLICATION_ID=JCCIMH.APPLICATION_ID
798 )
799 ;
800
801 -- Cursor to get specified GL Journal's lines.
802 -- Accoding to the user's decision, there can be some lines have entered DR/CR
803 -- but the accounted DR/CR have no relevant values, or even null.
804 CURSOR c_gl_lines IS
805 SELECT jel.je_line_num line_num
806 ,nvl(jel.description,
807 jeh.description) line_desc
808 ,jel.code_combination_id account_ccid
809 ,decode(jel.context, dffa.context_code,
810 decode(dffa.attribute_column, 'ATTRIBUTE1',jel.attribute1, 'ATTRIBUTE2',jel.attribute2,
811 'ATTRIBUTE3',jel.attribute3, 'ATTRIBUTE4',jel.attribute4, 'ATTRIBUTE5',jel.attribute5,
812 'ATTRIBUTE6',jel.attribute6, 'ATTRIBUTE7',jel.attribute7, 'ATTRIBUTE8',jel.attribute8,
813 'ATTRIBUTE9',jel.attribute9, 'ATTRIBUTE10',jel.attribute10, 'ATTRIBUTE11',jel.attribute11,
814 'ATTRIBUTE12',jel.attribute12, 'ATTRIBUTE13',jel.attribute13, 'ATTRIBUTE14',jel.attribute14,
815 'ATTRIBUTE15',jel.attribute15) )cash_related_item
816 ,nvl(jel.ENTERED_DR, 0) -
817 nvl(jel.ENTERED_CR, 0) je_entered_amount
818 ,nvl(jel.ACCOUNTED_DR, 0) -
819 nvl(jel.ACCOUNTED_CR, 0) je_accounted_amount
820 FROM GL_JE_LINES jel
821 ,GL_JE_HEADERS jeh
822 ,ja_cn_dff_assignments dffa
823 WHERE jel.je_header_id = l_je_header_id --using variable l_je_header_id
824 AND jeh.je_header_id = jel.je_header_id
825 -- to locate cash flow item in dff_assignment
826 AND dffa.Application_Id = 101
827 AND dffa.chart_of_accounts_id = l_coa_id --using variable l_coa_id
828 AND dffa.dff_title_code='GLLI';
829
830
831 --AGIS
832 -- Cursor to get AGIS transactions in the specified period,
833 -- Only consider transactions whose accounts are Cash Related and Status are complete.
834 CURSOR c_trx_header IS
835 SELECT trxh.trx_id trxh_ID
836 , trxh.trx_number trxh_num
837 , nvl(trxh.description,nvl(trxb.description,''))
838 trxh_decription
839 , trxh.initiator_id trxh_initiator_ID
840 , trxh.recipient_id trxh_recipient_ID
841 , trxh.to_le_id trxh_to_le_ID
842 , trxh.to_ledger_id trxh_to_ledger_ID
843 --, trxh.status trxh_status
844 , trxh.init_amount_cr trxh_init_amount_cr
845 , trxh.init_amount_dr trxh_init_amount_dr
846 , trxh.reci_amount_cr trxh_reci_amount_cr
847 , trxh.reci_amount_dr trxh_reci_amount_dr
848 , trxb.batch_id trxb_batch_ID
849 , trxb.batch_number trxb_batch_num
850 , trxb.gl_date trxb_gl_date
851 , trxb.currency_code trxb_curr_code
852 , trxb.from_le_id trxb_from_le_ID
853 , trxb.from_ledger_id trxb_from_ledger_ID
854 , nvl(trxb.exchange_rate_type,'') trxb_curr_cov_rate
855 , trxb.batch_date trxb_batch_date
856 /* , nvl(DECODE(DFF.ATTRIBUTE_COLUMN, 'ATTRIBUTE1',trxh.attribute1, 'ATTRIBUTE2',trxh.attribute2,
857 'ATTRIBUTE3',trxh.attribute3, 'ATTRIBUTE4',trxh.attribute4, 'ATTRIBUTE5',trxh.attribute5,
858 'ATTRIBUTE6',trxh.attribute6, 'ATTRIBUTE7',trxh.attribute7, 'ATTRIBUTE8',trxh.attribute8,
859 'ATTRIBUTE9',trxh.attribute9, 'ATTRIBUTE10',trxh.attribute10, 'ATTRIBUTE11',trxh.attribute11,
860 'ATTRIBUTE12',trxh.attribute12, 'ATTRIBUTE13',trxh.attribute13, 'ATTRIBUTE14',trxh.attribute14,
861 'ATTRIBUTE15',trxh.attribute15)
862 , '') trxh_cash_related_item*/
863 FROM FUN_TRX_HEADERS trxh
864 , FUN_TRX_BATCHES trxb
865 , FUN_TRX_TYPES_TL trxtype
866 --, JA_CN_DFF_ASSIGNMENTS dff
867 WHERE trxh.batch_id=trxb.batch_id
868 AND trxb.trx_type_id=trxtype.trx_type_id
869 --
870 --AND dff.DFF_TITLE_CODE='IITL'--'JOCA'
871 -- check the transaction status, care only complete trx.
872 -- and its journel had been post to GL
873 AND trxh.status = 'COMPLETE'
874 AND trxb.status = 'COMPLETE'
875 -- determine the trx type is transfered to GL
876 AND trxh.invoice_flag='N'
877 -- add period limite , have to limite the gl_date in the period.
878 AND trxb.gl_date between l_period_start_date and l_perioD_end_date
879 AND trxtype.language=userenv('LANG')
880
881 /* -- FOR TEST-----------------------------------------------------------
882 and trxh.batch_id in (83193,83194)
883 -- FOR TEST-----------------------------------------------------------
884 */;
885
886 --Cursor to get specified transaction's lines, including both sender and receiver parts.
887 CURSOR c_trx_line IS
888 SELECT DISTINCT
889 trl.Line_Number line_num
890 ,trl.line_id line_id
891 ,trldist.dist_id distl_id
892 ,trldist.party_id distl_party_id
893 ,trldist.party_type_flag distl_party_flg
894 ,trldist.dist_type_flag distl_dist_flg
895 ,trldist.ccid distl_ccid
896 ,trldist.amount_cr distl_amount_cr
897 ,trldist.amount_dr distl_amount_dr
898 ,trldist.dist_number distl_number
899 ,codecmb.chart_of_accounts_id codecmb_coa_id
900 ,nvl(DECODE(DFF.ATTRIBUTE_COLUMN, 'ATTRIBUTE1',trldist.attribute1, 'ATTRIBUTE2',trldist.attribute2,
901 'ATTRIBUTE3',trldist.attribute3, 'ATTRIBUTE4',trldist.attribute4, 'ATTRIBUTE5',trldist.attribute5,
902 'ATTRIBUTE6',trldist.attribute6, 'ATTRIBUTE7',trldist.attribute7, 'ATTRIBUTE8',trldist.attribute8,
903 'ATTRIBUTE9',trldist.attribute9, 'ATTRIBUTE10',trldist.attribute10, 'ATTRIBUTE11',trldist.attribute11,
904 'ATTRIBUTE12',trldist.attribute12, 'ATTRIBUTE13',trldist.attribute13, 'ATTRIBUTE14',trldist.attribute14,
905 'ATTRIBUTE15',trldist.attribute15)
906 , '') distl_cash_related_item
907 FROM FUN_TRX_LINES trl
908 ,FUN_DIST_LINES trldist
909 ,GL_CODE_COMBINATIONS codecmb
910 ,FND_SEGMENT_ATTRIBUTE_VALUES fsav
911 --,JA_CN_LEDGER_LE_BSV_GT tmpbsv
912 ,JA_CN_DFF_ASSIGNMENTS dff
913 WHERE trl.line_id=trldist.line_id
914 AND trl.trx_id=trldist.trx_id
915 AND trldist.ccid=codecmb.code_combination_id
916 AND trl.trx_id=l_trxh_header_id --using variable l_trx_id
917 AND codecmb.chart_of_accounts_id=l_coa_id --using variable p_coa_id
918 AND dff.DFF_TITLE_CODE='IITL' --'JOCA'
919 --AND trldist.dist_type_flag='L' -- ?? not sure
920 /*AND tmpbsv.ledger_id = l_ledger_id
921 AND tmpbsv.legal_entity_id = l_le_id
922 AND DECODE(fsav.APPLICATION_COLUMN_NAME, --segment FSAV.APPLICATION_COLUMN_NAME of codecmb
923 'SEGMENT1',codecmb.SEGMENT1, 'SEGMENT2',codecmb.SEGMENT2, 'SEGMENT3',codecmb.SEGMENT3,
924 'SEGMENT4',codecmb.SEGMENT4, 'SEGMENT5',codecmb.SEGMENT5, 'SEGMENT6',codecmb.SEGMENT6,
925 'SEGMENT7',codecmb.SEGMENT7, 'SEGMENT8',codecmb.SEGMENT8, 'SEGMENT9',codecmb.SEGMENT9,
926 'SEGMENT10',codecmb.SEGMENT10, 'SEGMENT11',codecmb.SEGMENT11, 'SEGMENT12',codecmb.SEGMENT12,
927 'SEGMENT13',codecmb.SEGMENT13, 'SEGMENT14',codecmb.SEGMENT14, 'SEGMENT15',codecmb.SEGMENT15,
928 'SEGMENT16',codecmb.SEGMENT16, 'SEGMENT17',codecmb.SEGMENT17, 'SEGMENT18',codecmb.SEGMENT18,
929 'SEGMENT19',codecmb.SEGMENT19, 'SEGMENT20',codecmb.SEGMENT20, 'SEGMENT21',codecmb.SEGMENT21,
930 'SEGMENT22',codecmb.SEGMENT22, 'SEGMENT23',codecmb.SEGMENT23, 'SEGMENT24',codecmb.SEGMENT24,
931 'SEGMENT25',codecmb.SEGMENT25, 'SEGMENT26',codecmb.SEGMENT26, 'SEGMENT27',codecmb.SEGMENT27,
932 'SEGMENT28',codecmb.SEGMENT28, 'SEGMENT29',codecmb.SEGMENT29, 'SEGMENT30',codecmb.SEGMENT30
933 ) = tmpbsv.bal_seg_value --select only company segment list in the ja_cn_ledger_le_bsv_gt table */
934 --locate to the right segment attribute value
935 AND fsav.application_id = 101
936 AND fsav.id_flex_num = l_coa_id
937 AND fsav.attribute_value = 'Y'
938 AND fsav.segment_attribute_type = 'GL_BALANCING'
939 ;
940
941 --body
942 BEGIN
943 --log for debug
944 IF (l_proc_level >= l_dbg_level)
945 THEN
946 FND_LOG.String( l_proc_level
947 ,l_module_prefix||'.'||l_proc_name||'.begin'
948 ,'Enter procedure'
949 );
950 FND_LOG.String( l_proc_level
951 ,l_module_prefix||'.'||l_proc_name||'.P_SOURCE'
952 ,P_SOURCE_APP_ID
953 );
954 END IF; --(l_proc_level >= l_dbg_level)
955
956 --Get the BSV reffered to the current legal entity and ledger
957 DELETE
958 FROM JA_CN_LEDGER_LE_BSV_GT;
959 COMMIT ;
960 --
961 --ja_cn_utility_pkg.populate_ledger_le_bsv_gt( P_LEDGER_ID,P_LE_ID);
962
963 IF ja_cn_utility.populate_ledger_le_bsv_gt( P_LEDGER_ID,P_LE_ID) <> 'S' THEN
964 RETURN;
965 END IF;
966
967 --GET SOURCR SHORT NAME FROM FND_APPLICATION
968 SELECT appl.application_short_name
969 INTO l_source_name
970 FROM fnd_application appl
971 WHERE appl.application_id= l_source_application_id;
972
973 --Collect Data from GL/GIS
974
975 IF l_source_application_id = 101 THEN
976 --Collect Data from GL
977 --Get Functional currency code of Current ledger
978 SELECT ledger.currency_code
979 INTO l_je_func_curr_code
980 FROM GL_LEDGERS ledger
981 WHERE ledger.ledger_id=P_LEDGER_ID;
982
983 --Get all periods between (P_GL_PERIOD_FROM, P_GL_PERIOD_TO).
984 OPEN c_period_name;
985 LOOP
986 FETCH c_period_name INTO l_period_name
987 ,l_period_year
988 ,l_period_num
989 ,l_period_start_date
990 ,l_period_end_date ;
991 EXIT WHEN c_period_name%NOTFOUND;
992
993 --Delete all rows from GL and between FROM/TO Periods in table JA_CN_CFS_ACTIVITIES_ALL.
994 DELETE
995 FROM JA_CN_CFS_ACTIVITIES_ALL s
996 WHERE LEGAL_ENTITY_ID=P_LE_ID
997 -- Fix bug by arming delete start
998 -- AND SOURCE='GL'
999 -- Fix bug by arming delete end
1000 -- Fix bug by arming add start
1001 AND SOURCE=l_source_name
1002 AND NVL(UPGRADE_FLAG, ' ')<>'P'
1003 -- Fix bug by arming add end
1004 AND PERIOD_NAME=l_period_Name;
1005 --
1006 COMMIT;
1007
1008 --Collect Data from GL and between the FROM/TO Periods.
1009 -- Only consider Journals whose categories is Cash Related and Status is Posted.
1010 OPEN c_gl;
1011 LOOP
1012 FETCH c_gl INTO l_je_header_id
1013 ,l_je_catg
1014 ,l_je_jnl_name
1015 ,l_je_jnl_doc_seq_num
1016 ,l_je_curr_code
1017 ,l_je_curr_cov_rate
1018 ,l_je_curr_cov_type
1019 ,l_je_curr_cov_date
1020 ,l_je_inter_flag
1021 ,l_je_effective_date;
1022 EXIT WHEN c_gl%NOTFOUND;
1023 --
1024 OPEN c_gl_lines;
1025 LOOP
1026 FETCH c_gl_lines INTO l_je_line_num
1027 ,l_je_line_desc
1028 ,l_ccid
1029 ,l_cash_related_item
1030 ,l_je_line_amount
1031 ,l_je_line_func_amount;
1032 EXIT WHEN c_gl_lines%NOTFOUND;
1033
1034 l_csi_check := 'NB';
1035 IF l_cash_related_item is null
1036 THEN
1037 l_csi_check := 'B';
1038 END IF;
1039
1040 -- to check whether the gl is cash related AND its company segment belongs to current LE.
1041 l_line_check := GL_InterCom_Line_Check( P_COA_ID => l_coa_id--P_COA_ID
1042 ,P_LEDGER_ID => l_ledger_id
1043 ,P_LE_ID => l_le_id
1044 ,P_SOURCE => 'GL'
1045 ,P_JT_ID => l_je_header_id
1046 ,P_LINE_NUM => TO_CHAR(l_je_line_num)
1047 ,P_CCID => l_ccid
1048 ,P_CASH_RELATED_ITEM => l_csi_check
1049 ,P_GIS_JNL_CRI => l_cash_related_item_1
1050 );
1051 --The line's is Cash Related AND its company segment belongs to current LE.
1052 IF l_line_check = 'Y' THEN
1053 IF l_cash_related_item_1 is not null
1054 THEN
1055 l_cash_related_item := l_cash_related_item_1;
1056 END IF;
1057 --insert the row
1058 INSERT INTO JA_CN_CFS_ACTIVITIES_ALL
1059 ( CFS_ACTIVITY_ID
1060 ,LEGAL_ENTITY_ID
1061 ,LEDGER_ID
1062 ,ORG_ID
1063 ,TRX_ID
1064 ,TRX_NUMBER
1065 ,TRX_LINE_ID
1066 ,SOURCE_APPLICATION_ID
1067 ,TRANSACTION_TYPE
1068 ,DOCUMENT_SEQUENCE_NUMBER
1069 ,TRANSACTION_DATE
1070 ,GL_DATE
1071 ,PERIOD_NAME
1072 ,FUNC_CURR_CODE
1073 ,FUNC_AMOUNT
1074 ,ORIGINAL_CURR_CODE
1075 ,ORIGINAL_AMOUNT
1076 ,CURRENCY_CONVERSION_RATE
1077 ,CURRENCY_CONVERSION_TYPE
1078 ,CURRENCY_CONVERSION_DATE
1079 ,DESCRIPTION
1080 ,DETAILED_CFS_ITEM
1081 --,INTERCOMPANY_FLAG
1082 ,CREATION_DATE
1083 ,CREATED_BY
1084 ,LAST_UPDATE_DATE
1085 ,LAST_UPDATED_BY
1086 ,LAST_UPDATE_LOGIN
1087 ,source
1088 --,reference_number
1089 ,BALANCING_SEGMENT--Fix bug#7334017 add
1090 )
1091 VALUES( ja_cn_cfs_activities_s.nextval
1092 ,l_le_id
1093 ,P_LEDGER_ID -- ledger id
1094 ,null
1095 ,l_je_header_id
1096 ,l_je_jnl_name
1097 ,TO_CHAR(l_je_line_num)
1098 ,l_source_application_id
1099 ,'JOURNAL'
1100 ,l_je_jnl_doc_seq_num
1101 ,l_je_effective_date
1102 ,l_je_effective_date
1103 ,l_period_name
1104 ,l_je_func_curr_code -- currency code from gl_ledger
1105 ,l_je_line_func_amount
1106 ,l_je_curr_code
1107 ,l_je_line_amount
1108 ,l_je_curr_cov_rate
1109 ,l_je_curr_cov_type
1110 ,l_je_curr_cov_date
1111 ,l_je_line_desc
1112 ,l_cash_related_item --lines GDF
1113 --,l_je_inter_flag -- not sure
1114 ,SYSDATE
1115 ,fnd_global.user_id
1116 ,SYSDATE
1117 ,fnd_global.user_id
1118 ,fnd_global.LOGIN_ID
1119 ,l_source_name --'GL' -- for source,seeded data
1120 --,l_je_jnl_name --sanme as TRX_NUMBER
1121 ,get_balancing_segment(l_ccid)--Fix bug#7334017 add
1122 );
1123 commit;
1124 END IF;
1125 END LOOP;
1126 CLOSE c_gl_lines;
1127 END LOOP;
1128 CLOSE c_gl;
1129 END LOOP;
1130 CLOSE c_period_name;
1131
1132 -- ELSIF P_SOURCE = 'AGIS' THEN
1133 ELSIF l_source_application_id = 435 THEN
1134 --AGIS
1135 --Get Functional currency code of Current ledger
1136 SELECT ledger.currency_code
1137 INTO l_je_func_curr_code
1138 FROM GL_LEDGERS ledger
1139 WHERE ledger.ledger_id=P_LEDGER_ID;
1140
1141 --Collect Data from AGIS, Collect Data from Intercompany
1142 -- Get all periods between (P_START_PERIOD, P_END_PERIOD).
1143 OPEN c_period_name;
1144 LOOP
1145 FETCH c_period_name INTO l_period_name
1146 ,l_period_year
1147 ,l_period_num
1148 ,l_period_start_date
1149 ,l_period_end_date ;
1150 EXIT WHEN c_period_name%NOTFOUND;
1151
1152 --Delete all rows from GIS and between FROM/TO Periods in table JA_CN_CFS_ACTIVITIES_ALL.
1153 DELETE
1154 FROM JA_CN_CFS_ACTIVITIES_ALL
1155 WHERE LEGAL_ENTITY_ID = l_le_id
1156 AND PERIOD_NAME = l_period_name
1157 --AND SOURCE = 'GIS' --fix bug 7488206 delete
1158 AND SOURCE=l_source_name ;--fix bug 7488206 add
1159 --
1160 commit;
1161
1162 --Collect Data from Intercompany and between the FROM/TO Periods.
1163 -- Only consider Transactions whose sender and receiver transfer flag are 'Yes'.
1164 OPEN c_trx_header;
1165 LOOP
1166 FETCH c_trx_header INTO l_trxh_header_id
1167 ,l_trxh_header_num
1168 ,l_trxh_desc
1169 ,l_trxh_initiator_id -- sender
1170 ,l_trxh_recipient_id -- receivor
1171 ,l_trxh_to_le_id
1172 ,l_trxh_to_ledger_id
1173 ,l_trxh_init_amount_cr
1174 ,l_trxh_init_amount_dr
1175 ,l_trxh_reci_amount_cr
1176 ,l_trxh_reci_amount_dr
1177 ,l_trxb_batch_id
1178 ,l_trxb_batch_num
1179 ,l_trxb_gl_date
1180 ,l_trxb_curr_code
1181 ,l_trxb_from_le_id
1182 ,l_trxb_from_ledger_id
1183 ,l_trxb_curr_cov_type
1184 ,l_trxb_entered_date -- ?? not sure, the batch date or the header last update date
1185 --,l_trxh_cash_rel_item
1186 ;
1187 EXIT WHEN c_trx_header%NOTFOUND;
1188
1189 -- For each lines in current transaction
1190 OPEN c_trx_line;
1191 LOOP
1192 FETCH c_trx_line INTO l_trxl_num
1193 ,l_trxl_id
1194 ,l_distl_id
1195 ,l_distl_party_id
1196 ,l_distl_party_type_flg
1197 ,l_distl_dist_type_flg
1198 ,l_distl_ccid
1199 ,l_distl_amount_cr
1200 ,l_distl_amount_dr
1201 ,l_distl_number
1202 ,l_codecmb_coa_id
1203 ,l_distl_cash_rel_item
1204 ;
1205 EXIT WHEN c_trx_line%NOTFOUND;
1206
1207 -- get current currency rate from the gl_ledger and gl_daily_rates tables.
1208 IF l_distl_party_type_flg='I' THEN
1209 -- sender convertion rate
1210 l_get_trx_cov_rate_flg := FALSE;
1211 l_get_trx_cov_rate_flg := AGIS_Get_Curr_Rate_Type(
1212 P_LEDGER_ID => l_ledger_id
1213 ,P_LE_ID => l_le_id
1214 ,P_AGIS_LE_ID => l_trxb_from_le_id
1215 ,P_AGIS_LEDGER_ID => l_trxb_from_ledger_id
1216 ,P_GL_DATE => l_trxb_gl_date
1217 ,P_AGIS_CURR_COV_TYPE => l_trxb_curr_cov_type
1218 ,P_AGIS_CURR_CODE => l_trxb_curr_code
1219 ,P_AGIS_CURR_RATE => l_trxh_curr_cov_rate
1220 );
1221 IF l_get_trx_cov_rate_flg = FALSE THEN
1222 EXIT;
1223 END IF;
1224 ELSIF l_distl_party_type_flg='R' THEN
1225 -- receiver convertion rate
1226 l_get_trx_cov_rate_flg:=FALSE;
1227 l_get_trx_cov_rate_flg := AGIS_Get_Curr_Rate_Type(
1228 P_LEDGER_ID => l_ledger_id
1229 ,P_LE_ID => l_le_id
1230 ,P_AGIS_LE_ID => l_trxh_to_le_id
1231 ,P_AGIS_LEDGER_ID => l_trxh_to_ledger_id
1232 ,P_GL_DATE => l_trxb_gl_date
1233 ,P_AGIS_CURR_COV_TYPE => l_trxb_curr_cov_type
1234 ,P_AGIS_CURR_CODE => l_trxb_curr_code
1235 ,P_AGIS_CURR_RATE => l_trxh_curr_cov_rate
1236 );
1237 IF l_get_trx_cov_rate_flg = FALSE THEN
1238 EXIT;
1239 END IF;
1240
1241 END IF;
1242 -- set the currente amount
1243 l_current_amount_cr := nvl(l_distl_amount_cr,0);--nvl(l_trxh_reci_amount_cr,0);
1244 l_current_amount_dr := nvl(l_distl_amount_dr,0);--nvl(l_trxh_reci_amount_dr,0);
1245
1246 l_csi_check := 'NB';
1247 IF l_distl_cash_rel_item ='' OR l_distl_cash_rel_item is null THEN
1248 l_csi_check := 'B';
1249 END IF;
1250
1251 -- secondly: check whether there are cash related segment
1252 l_line_check := GL_InterCom_Line_Check( P_COA_ID => l_coa_id
1253 ,P_LEDGER_ID => l_ledger_id
1254 ,P_LE_ID => l_le_id
1255 ,P_SOURCE => 'AGIS'
1256 ,P_JT_ID => l_distl_id
1257 ,P_LINE_NUM => TO_CHAR(l_trxh_header_num)
1258 ,P_CCID => l_distl_ccid
1259 ,P_CASH_RELATED_ITEM => l_csi_check -- not sure
1260 ,P_GIS_JNL_CRI => l_distl_cash_rel_item_l -- not sure
1261 );
1262
1263 --The line's is Cash Related AND its company segment belongs to current LE.
1264 IF l_line_check = 'Y' THEN
1265 IF l_distl_cash_rel_item_l is not null
1266 THEN
1267 l_distl_cash_rel_item := l_distl_cash_rel_item_l;
1268 END IF;
1269
1270 Process_GL_Rounding( P_LE_ID => P_LE_ID
1271 ,P_AMOUNT => (l_current_amount_dr-l_current_amount_cr) * l_trxh_curr_cov_rate
1272 ,P_CURRENCY_CODE => l_trxb_curr_code
1273 ,P_AMOUNT_ROUNDED => l_tr_func_amount
1274 );
1275 --insert the row
1276 INSERT INTO JA_CN_CFS_ACTIVITIES_ALL
1277 ( CFS_ACTIVITY_ID
1278 ,LEGAL_ENTITY_ID
1279 ,LEDGER_ID
1280 ,ORG_ID
1281 ,TRX_ID
1282 ,TRX_NUMBER
1283 ,TRX_LINE_ID
1284 ,SOURCE_APPLICATION_ID
1285 ,TRANSACTION_TYPE
1286 ,DOCUMENT_SEQUENCE_NUMBER
1287 ,TRANSACTION_DATE
1288 ,GL_DATE
1289 ,PERIOD_NAME
1290 ,FUNC_CURR_CODE
1291 ,FUNC_AMOUNT
1292 ,ORIGINAL_CURR_CODE
1293 ,ORIGINAL_AMOUNT
1294 ,CURRENCY_CONVERSION_RATE
1295 ,CURRENCY_CONVERSION_TYPE
1296 ,CURRENCY_CONVERSION_DATE
1297 ,DESCRIPTION
1298 ,DETAILED_CFS_ITEM
1299 --,INTERCOMPANY_FLAG -- delete in R12
1300 ,CREATION_DATE
1301 ,CREATED_BY
1302 ,LAST_UPDATE_DATE
1303 ,LAST_UPDATED_BY
1304 ,LAST_UPDATE_LOGIN
1305 ,SOURCE
1306 --,reference_number
1307 ,BALANCING_SEGMENT
1308 )
1309 VALUES( ja_cn_cfs_activities_s.nextval
1310 ,l_le_id
1311 ,P_LEDGER_ID -- ledger id
1312 ,NULL
1313 ,l_trxh_header_id -- ?? batch id
1314 -- ,l_trxh_header_num -- ?? batch number: transaction header number fix bug 7488191 delete
1315 ,l_trxb_batch_num -- fix bug 7488191 add
1316 ,TO_CHAR(l_distl_number) -- ?? header number
1317 ,l_source_application_id -- application TD of AGIS
1318 ,'AGIS' -- seeded data
1319 ,NULL
1320 ,l_trxb_entered_date -- batches: batch_date
1321 ,l_trxb_gl_date -- batches: gl_date
1322 ,l_period_name -- parameters: period name
1323 ,l_je_func_curr_code -- function currency of current Ledger to FUNC_CURR_CODE
1324 ,l_tr_func_amount -- lines: acounted_dr-accounted_cr --?? not sure
1325 ,l_trxb_curr_code -- batches: currency code
1326 ,l_current_amount_dr-l_current_amount_cr -- lines: entered_dr-entered_cr --?? not sure
1327 ,l_trxh_curr_cov_rate -- GL_DAILY_RATES.CONVERSION_RATE
1328 ,l_trxb_curr_cov_type -- conversion_type of line's subsidary(s/r)
1329 ,l_trxb_gl_date -- batches: gl_date
1330 ,l_trxh_desc -- header: description
1331 ,l_distl_cash_rel_item -- the attribute* name
1332 --,l_je_inter_flag -- delete in R12
1333 ,SYSDATE
1334 ,fnd_global.user_id
1335 ,SYSDATE
1336 ,fnd_global.user_id
1337 ,fnd_global.LOGIN_ID
1338 ,l_source_name --'GIS' -- for source,seeded data
1339 --,l_je_jnl_name --sanme as TRX_NUMBER
1340 ,get_balancing_segment(l_distl_ccid)--Fix bug#7334017 add
1341 );
1342 --
1343 commit;
1344 END IF;
1345
1346 END LOOP;
1347 CLOSE c_trx_line;
1348 END LOOP;
1349 CLOSE c_trx_header;
1350 END LOOP;
1351 CLOSE c_period_name;
1352 END IF;
1353
1354 END Collect_GL_InterCom_Data;
1355
1356 --==========================================================================
1357 -- PROCEDURE NAME:
1358 -- Cfs_Data_Clt public
1359 --
1360 -- DESCRIPTION:
1361 -- This procedure calls data collection programs according to
1362 -- the specified source.
1363 --
1364 -- PARAMETERS:
1365 -- In: P_LEDGER_ID NUMBER ID of LEDGER
1366 -- In: P_LE_ID NUMBER ID of Legal Entity
1367 -- In: P_PERIOD_SET_NAME VARCHAR2 Name of the period set
1368 -- in the set of book
1369 -- In: P_GL_PERIOD_FROM VARCHAR2 Start period
1370 -- In: P_GL_PERIOD_TO VARCHAR2 End period
1371 -- In: P_SOURCE VARCHAR2 Source of the collection
1372 --
1373 -- In: P_DFT_ITEM VARCHAR2 default CFS item
1374 --
1375 -- DESIGN REFERENCES:
1376 -- None
1377 --
1378 -- CHANGE HISTORY:
1379 -- 03/01/2006 Andrew Liu Created
1380 -- 04/02/2007 Yucheng Sun Altered: Added the logic of AGIS model.
1381 -- Delete the AP,AR logic
1382 --===========================================================================
1383 PROCEDURE Cfs_Data_Clt( P_COA_ID IN NUMBER
1384 ,P_LEDGER_ID IN NUMBER
1385 ,P_LE_ID IN NUMBER
1386 ,P_PERIOD_SET_NAME IN VARCHAR2
1387 ,P_GL_PERIOD_FROM IN VARCHAR2
1388 ,P_GL_PERIOD_TO IN VARCHAR2
1389 ,P_SOURCE IN VARCHAR2
1390 ) IS
1391
1392 l_source_id NUMBER(15);
1393 --l_source varchar(200):=P_SOURCE;
1394 l_source VARCHAR2(15):= P_SOURCE;
1395
1396 BEGIN
1397 IF L_source is null
1398 THEN
1399 Collect_GL_InterCom_Data( P_COA_ID => P_COA_ID
1400 ,P_LEDGER_ID => P_LEDGER_ID
1401 ,P_LE_ID => P_LE_ID
1402 ,P_PERIOD_SET_NAME => P_PERIOD_SET_NAME
1403 ,P_GL_PERIOD_FROM => P_GL_PERIOD_FROM
1404 ,P_GL_PERIOD_TO => P_GL_PERIOD_TO
1405 ,P_SOURCE_APP_ID => 101 --Gl
1406 );
1407
1408 Collect_GL_InterCom_Data( P_COA_ID => P_COA_ID
1409 ,P_LEDGER_ID => P_LEDGER_ID
1410 ,P_LE_ID => P_LE_ID
1411 ,P_PERIOD_SET_NAME => P_PERIOD_SET_NAME
1412 ,P_GL_PERIOD_FROM => P_GL_PERIOD_FROM
1413 ,P_GL_PERIOD_TO => P_GL_PERIOD_TO
1414 ,P_SOURCE_APP_ID => 435 --AGIS
1415 );
1416
1417 JA_CN_CFS_CLT_SLA_PKG.Collect_SLA_Data( P_COA_ID =>P_COA_ID
1418 ,P_LEDGER_ID => P_LEDGER_ID
1419 ,P_LE_ID => P_LE_ID
1420 ,P_PERIOD_SET_NAME => P_PERIOD_SET_NAME
1421 ,P_GL_PERIOD_FROM => P_GL_PERIOD_FROM
1422 ,P_GL_PERIOD_TO => P_GL_PERIOD_TO
1423 ,P_SOURCE => 'ALL'
1424 );
1425 ELSE
1426 BEGIN
1427 SELECT application_id
1428 INTO l_source_id
1429 FROM fnd_application
1430 WHERE application_short_name = TO_CHAR(l_source);
1431 EXCEPTION
1432 WHEN no_data_found THEN
1433 l_source_id := NULL;
1434 WHEN too_many_rows THEN
1435 l_source_id := NULL;
1436 END;
1437 IF nvl(l_source_id,-1) = 101 or nvl(l_source_id ,-1)= 435 THEN
1438 Collect_GL_InterCom_Data( P_COA_ID =>P_COA_ID
1439 ,P_LEDGER_ID => P_LEDGER_ID
1440 ,P_LE_ID => P_LE_ID
1441 ,P_PERIOD_SET_NAME => P_PERIOD_SET_NAME
1442 ,P_GL_PERIOD_FROM => P_GL_PERIOD_FROM
1443 ,P_GL_PERIOD_TO => P_GL_PERIOD_TO
1444 ,P_SOURCE_APP_ID => l_source_id
1445 );
1446 ELSE
1447 JA_CN_CFS_CLT_SLA_PKG.Collect_SLA_Data( P_COA_ID =>P_COA_ID
1448 ,P_LEDGER_ID => P_LEDGER_ID
1449 ,P_LE_ID => P_LE_ID
1450 ,P_PERIOD_SET_NAME => P_PERIOD_SET_NAME
1451 ,P_GL_PERIOD_FROM => P_GL_PERIOD_FROM
1452 ,P_GL_PERIOD_TO => P_GL_PERIOD_TO
1453 ,P_SOURCE => P_SOURCE
1454 );
1455 END IF;
1456
1457 END IF;
1458 END Cfs_Data_Clt;
1459
1460
1461 --==========================================================================
1462 -- PROCEDURE NAME:
1463 -- get_balancing_segment private
1464 --
1465 -- DESCRIPTION:
1466 -- This procedure returns the balancing segment value of a CCID.
1467 --
1468 -- PARAMETERS:
1469 -- In: P_CC_ID NUMBER
1470 --
1471 -- DESIGN REFERENCES:
1472 -- None
1473 --
1474 -- CHANGE HISTORY:
1475 -- 09/01/2008 Yao Zhang Created
1476 --===========================================================================
1477 FUNCTION get_balancing_segment
1478 ( P_CC_ID IN NUMBER
1479 )
1480 RETURN VARCHAR2
1481 IS
1482 L_BALANCING_SEGMENT GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
1483 BEGIN
1484 SELECT
1485 DECODE(FSAV.APPLICATION_COLUMN_NAME, --segment FSAV.APPLICATION_COLUMN_NAME of gcc
1486 'SEGMENT1',GCC.SEGMENT1, 'SEGMENT2',GCC.SEGMENT2, 'SEGMENT3',GCC.SEGMENT3,
1487 'SEGMENT4',GCC.SEGMENT4, 'SEGMENT5',GCC.SEGMENT5, 'SEGMENT6',GCC.SEGMENT6,
1488 'SEGMENT7',GCC.SEGMENT7, 'SEGMENT8',GCC.SEGMENT8, 'SEGMENT9',GCC.SEGMENT9,
1489 'SEGMENT10',GCC.SEGMENT10, 'SEGMENT11',GCC.SEGMENT11, 'SEGMENT12',GCC.SEGMENT12,
1490 'SEGMENT13',GCC.SEGMENT13, 'SEGMENT14',GCC.SEGMENT14, 'SEGMENT15',GCC.SEGMENT15,
1491 'SEGMENT16',GCC.SEGMENT16, 'SEGMENT17',GCC.SEGMENT17, 'SEGMENT18',GCC.SEGMENT18,
1492 'SEGMENT19',GCC.SEGMENT19, 'SEGMENT20',GCC.SEGMENT20, 'SEGMENT21',GCC.SEGMENT21,
1493 'SEGMENT22',GCC.SEGMENT22, 'SEGMENT23',GCC.SEGMENT23, 'SEGMENT24',GCC.SEGMENT24,
1494 'SEGMENT25',GCC.SEGMENT25, 'SEGMENT26',GCC.SEGMENT26, 'SEGMENT27',GCC.SEGMENT27,
1495 'SEGMENT28',GCC.SEGMENT28, 'SEGMENT29',GCC.SEGMENT29, 'SEGMENT30',GCC.SEGMENT30)
1496 INTO L_BALANCING_SEGMENT
1497 FROM GL_CODE_COMBINATIONS GCC,
1498 FND_SEGMENT_ATTRIBUTE_VALUES FSAV
1499 WHERE GCC.CODE_COMBINATION_ID = P_CC_ID
1500 AND FSAV.ATTRIBUTE_VALUE = 'Y'
1501 AND FSAV.APPLICATION_ID = 101
1502 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
1503 AND FSAV.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
1504 AND FSAV.ID_FLEX_CODE = 'GL#';--Fix bug#7334017 add
1505
1506 RETURN L_BALANCING_SEGMENT;
1507 END get_balancing_segment;
1508 -- Fix bug#6359169 add end
1509
1510 --==========================================================================
1511
1512 --==========================================================================
1513 -- PROCEDURE NAME:
1514 -- Put_Line private
1515 --
1516 -- DESCRIPTION:
1517 -- This procedure write data to concurrent output file.
1518 --
1519 -- PARAMETERS:
1520 -- In: p_str VARCHAR2
1521 --
1522 -- DESIGN REFERENCES:
1523 -- None
1524 --
1525 -- CHANGE HISTORY:
1526 -- 03/01/2006 Jogen Hu Created
1527 --===========================================================================
1528 PROCEDURE put_line
1529 ( p_str IN VARCHAR2
1530 )
1531 IS
1532 BEGIN
1533 FND_FILE.Put_Line(FND_FILE.Output,p_str);
1534 END put_line;
1535
1536 --==========================================================================
1537 -- PROCEDURE NAME:
1538 -- Put_Line private
1539 --
1540 -- DESCRIPTION:
1541 -- This procedure write data to log file.
1542 --
1543 -- PARAMETERS:
1544 -- In: p_str VARCHAR2
1545 --
1546 -- DESIGN REFERENCES:
1547 -- None
1548 --
1549 -- CHANGE HISTORY:
1550 -- 03/01/2006 Jogen Hu Created
1551 --===========================================================================
1552 PROCEDURE put_log
1553 ( p_module IN VARCHAR2
1554 , p_message IN VARCHAR2
1555 )
1556 IS
1557 BEGIN
1558 --fnd_file.PUT_LINE(fnd_file.LOG,p_module||':'||p_message);
1559 IF( fnd_log.LEVEL_STATEMENT >= g_debug_devel )
1560 THEN
1561 fnd_log.STRING( LOG_LEVEL => fnd_log.LEVEL_STATEMENT
1562 , MODULE => p_module
1563 , MESSAGE => p_message
1564 );
1565 END IF;
1566 -- xx_jogen_log(p_module||':'||p_message);
1567 END put_log;
1568
1569 --==========================================================================
1570 -- FUNCTION NAME:
1571 -- get_period_name Public
1572 --
1573 -- DESCRIPTION:
1574 -- This FUNCTION is used to get period name from a period set and given date
1575 -- the period name is month type
1576 --
1577 -- PARAMETERS:
1578 -- In: p_period_set_name period set name
1579 -- p_gl_date date
1580 -- p_period_type period type
1581 -- return: period name
1582 --
1583 -- DESIGN REFERENCES:
1584 -- None
1585 --
1586 -- CHANGE HISTORY:
1587 -- 03/08/2006 Jogen Hu Created
1588 --===========================================================================
1589 FUNCTION get_period_name
1590 (
1591 p_period_set_name IN VARCHAR2
1592 , p_gl_date IN DATE
1593 , p_period_type IN VARCHAR2
1594 )RETURN VARCHAR2
1595 IS
1596 l_period_name VARCHAR2(30);
1597 BEGIN
1598 SELECT period_name
1599 INTO l_period_name
1600 FROM gl_periods
1601 WHERE period_set_name = p_period_set_name
1602 AND start_date <=p_gl_date
1603 AND End_Date >=p_gl_date
1604 AND period_type = p_period_type
1605 AND adjustment_period_flag = 'N';
1606
1607 RETURN l_period_name;
1608
1609 END get_period_name;
1610
1611
1612
1613
1614 /* IF( G_PROC_LEVEL >= g_debug_devel )
1615 THEN
1616 put_log(G_MODULE_PREFIX||l_procedure_name||'.period rowcount'
1617 ,l_row_count);
1618 END IF; --( G_PROC_LEVEL >= g_debug_devel)*/
1619 BEGIN
1620 g_debug_devel:=fnd_log.G_CURRENT_RUNTIME_LEVEL;
1621
1622 -- Initialization
1623 --l_resp_id := FND_PROFILE.VALUE('RESP_ID');
1624 --l_org_id := FND_PROFILE.VALUE('ORG_ID')
1625 --:$PROFILES$.JA_CN_LEGAL_ENTITY
1626 --null;
1627
1628 END JA_CN_CFS_DATA_CLT_PKG;
1629