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