[Home] [Help]
PACKAGE BODY: APPS.JA_CN_GL_INTER_VALID_PKG
Source
1 PACKAGE BODY JA_CN_GL_INTER_VALID_PKG AS
2 --$Header: JACNGIVB.pls 120.9 2011/06/16 09:42:19 choli ship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNGIVB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used for GL Journals and Intercompany Transactions|
13 --| Validation in the CNAO Project. |
14 --| |
15 --| PROCEDURE LIST |
16 --| FUNCTION Source_Meaning PRIVATE |
17 --| FUNCTION Line_Check PRIVATE |
18 --| PROCEDURE Get_Account_Combo_and_Desc PRIVATE |
19 --| PROCEDURE GL_Validation PUBLIC |
20 --| PROCEDURE Intercompany_Validation PUBLIC |
21 --| |
22 --| HISTORY |
23 --| 02/24/2006 Andrew Liu Created |
24 --| 04/30/2007 Yucheng Sun Updated |
25 --| 12/07/2007 Arming Chen Fix bug#6654759
26 --| 04/09/2008 Chaoqun Wu Updated for CNAO Enhancement |
27 --| 10/10/2008 Chaoqun Wu Fix bug#7475903 |
28 --| 14/10/2008 Chaoqun Wu Fix bug# 7481841 |
29 --| 17/10/2008 Chaoqun Wu Fix bug#7487439 |
30 -- | 16/06/2011 Chongwu Li Fix bug#12630377 |
31 --+======================================================================*/
32
33 l_module_prefix VARCHAR2(100) :='JA_CN_GL_INTER_VALID_PKG';
34
35 JA_CN_INCOMPLETE_DFF_ASSIGN exception;
36 JA_CN_NO_CASHFLOWITEM exception;
37 JA_CN_NO_CASHACCOUNT exception;
38 l_msg_incomplete_dff_assign VARCHAR2(2000); -- 'The descriptive flexfield assignments are incomplete...';
39 l_msg_no_cashflow_item VARCHAR2(2000); -- 'No cash flow item in the DFF';
40 l_msg_no_cash_account VARCHAR2(2000); -- 'No cash related account';
41
42 --==========================================================================
43 -- FUNCTION NAME:
44 -- Source_Meaning private
45 --
46 -- DESCRIPTION:
47 -- This function gets meaning of source, GL Journal/Intercompany Transaction.
48 --
49 -- PARAMETERS:
50 -- In: P_SOURCE VARCHAR2 Source: GLJE/INTR
51 -- RETURN:
52 -- VARCHAR2
53 -- Meaning of the source
54 --
55 -- DESIGN REFERENCES:
56 -- None
57 --
58 -- CHANGE HISTORY:
59 -- 02/24/2006 Andrew Liu Created
60 --==========================================================================
61 FUNCTION Source_Meaning( P_SOURCE IN VARCHAR2 )
62 RETURN VARCHAR2 IS
63 l_source VARCHAR2(150);
64 BEGIN
65 SELECT FLV.meaning source
66 INTO l_source
67 FROM FND_LOOKUP_VALUES FLV
68 WHERE FLV.lookup_code = P_SOURCE --using parameter P_SOURCE: 'GLJE'/'INTR'
69 AND FLV.lookup_type = 'JA_CN_CASHFLOW_SRC_TYPE'
70 AND FLV.LANGUAGE = userenv('LANG')
71 ;
72
73 RETURN l_source;
74 End Source_Meaning;
75
76 --==========================================================================
77 -- FUNCTION NAME:
78 -- Line_Check private
79 --
80 -- DESCRIPTION:
81 -- This function checks whether line of GL journals OR Intercompany
82 -- transactions can be output as a invalid one or not.
83 --
84 -- PARAMETERS:
85 -- IN: P_COA_ID NUMBER ID of chart of accounts
86 -- In: P_LEDGER_ID NUMBER ID of ledger
87 -- In: P_LE_ID NUMBER ID of Legal Entity
88 -- In: P_SOURCE VARCHAR2 Source:GL/GIS
89 -- In: P_COM_SEGMENT VARCHAR2 Specified company segment
90 -- In: P_JT_ID VARCHAR2 ID of the Journal/Tr
91 -- In: P_LINE_NUM VARCHAR2 Number of the line
92 -- In: P_CCID NUMBER ID of chart of account
93 -- In: P_CASH_RELATED_ITEM VARCHAR2 Cash related item of the line
94 -- RETURN:
95 -- VARCHAR2
96 -- 'NO_ITEM' for the line has cash related account but no such item
97 -- 'NO_ACCOUNT' for the line has cash related item but no such account
98 -- 'EXCLUDED' for the line should be excluded
99 -- 'OK' for the line is a good line
100 --
101 -- DESIGN REFERENCES:
102 -- None
103 --
104 -- CHANGE HISTORY:
105 -- 02/24/2006 Andrew Liu Created
106 -- 04/16/2007 Yucheng Sun Changed
107 -- 16/06/2011 Chongwu Li Fix bug#12630377
108 --===========================================================================
109 FUNCTION Line_Check( P_COA_ID IN NUMBER
110 ,P_LEDGER_ID IN NUMBER
111 ,P_LE_ID IN NUMBER
112 ,P_SOURCE IN VARCHAR2
113 ,P_JT_ID IN NUMBER
114 ,P_LINE_NUM IN VARCHAR2
115 ,P_CCID IN NUMBER
116 ,P_CASH_RELATED_ITEM IN VARCHAR2
117 ) RETURN VARCHAR2 IS
118 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
119 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
120 l_proc_name VARCHAR2(100) :='Line_Check';
121
122 l_coa_id number := P_COA_ID;
123 l_ledger_id NUMBER := P_LEDGER_ID;
124 l_le_id NUMBER := P_LE_ID;
125 l_source VARCHAR2(10) := P_SOURCE;
126 --l_com_segment VARCHAR2(150) := P_COM_SEGMENT;
127 l_jt_id NUMBER := P_JT_ID;
128 l_line_num VARCHAR2(20) := P_LINE_NUM;
129 l_cc_id NUMBER := P_CCID;
130 l_csi_check varchar2(2) := P_CASH_RELATED_ITEM;
131
132 l_seg_type FND_SEGMENT_ATTRIBUTE_VALUES.SEGMENT_ATTRIBUTE_TYPE%TYPE;
133 l_ffv_flex_value varchar2(150);
134 l_seg_fsav_gcc varchar2(150);
135
136 l_account_num varchar2(150); --account number of a line's account
137 l_com_seg varchar2(150); --company segment of a line's account
138 l_com_seg_check number; --flag of an account's company segment belongs to current LE or not
139 l_account_check number; --flag of an account in cash related table or not
140 l_line_chk varchar2(20); --result of a line's validation
141 l_cash_flow_item_from_GL gl_je_lines.attribute1%TYPE;
142
143 --Cursor to get FFV.Flex_Value and segment FSAV.APPLICATION_COLUMN_NAME of gcc
144 CURSOR c_ffv IS
145 SELECT FFV.Flex_Value ffv_flex_value
146 ,DECODE(FSAV.APPLICATION_COLUMN_NAME, --segment FSAV.APPLICATION_COLUMN_NAME of gcc
147 'SEGMENT1',GCC.SEGMENT1, 'SEGMENT2',GCC.SEGMENT2, 'SEGMENT3',GCC.SEGMENT3,
148 'SEGMENT4',GCC.SEGMENT4, 'SEGMENT5',GCC.SEGMENT5, 'SEGMENT6',GCC.SEGMENT6,
149 'SEGMENT7',GCC.SEGMENT7, 'SEGMENT8',GCC.SEGMENT8, 'SEGMENT9',GCC.SEGMENT9,
150 'SEGMENT10',GCC.SEGMENT10, 'SEGMENT11',GCC.SEGMENT11, 'SEGMENT12',GCC.SEGMENT12,
151 'SEGMENT13',GCC.SEGMENT13, 'SEGMENT14',GCC.SEGMENT14, 'SEGMENT15',GCC.SEGMENT15,
152 'SEGMENT16',GCC.SEGMENT16, 'SEGMENT17',GCC.SEGMENT17, 'SEGMENT18',GCC.SEGMENT18,
153 'SEGMENT19',GCC.SEGMENT19, 'SEGMENT20',GCC.SEGMENT20, 'SEGMENT21',GCC.SEGMENT21,
154 'SEGMENT22',GCC.SEGMENT22, 'SEGMENT23',GCC.SEGMENT23, 'SEGMENT24',GCC.SEGMENT24,
155 'SEGMENT25',GCC.SEGMENT25, 'SEGMENT26',GCC.SEGMENT26, 'SEGMENT27',GCC.SEGMENT27,
156 'SEGMENT28',GCC.SEGMENT28, 'SEGMENT29',GCC.SEGMENT29, 'SEGMENT30',GCC.SEGMENT30)
157 seg_fsav_gcc
158 FROM GL_CODE_COMBINATIONS gcc
159 ,GL_LEDGERS ledger
160 ,FND_ID_FLEX_SEGMENTS FIFS
161 ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
162 ,FND_FLEX_VALUE_SETS FFVS
163 ,FND_FLEX_VALUES FFV
164 WHERE gcc.code_combination_id = l_cc_id --using variable l_cc_id
165 AND ledger.chart_of_accounts_id = gcc.chart_of_accounts_id
166 AND ledger.ledger_id = l_ledger_id --using variable l_sob_id
167 AND FIFS.id_flex_num = gcc.chart_of_accounts_id
168 AND FIFS.id_flex_num = FSAV.id_flex_num
169 AND FIFS.application_id = 101
170 AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
171 AND FIFS.application_id = FSAV.application_id
172 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = l_seg_type --using variable l_seg_type
173 AND FSAV.ATTRIBUTE_VALUE = 'Y'
174 AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
175 AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
176 ;
177
178 BEGIN
179 --log for debug
180 IF (l_proc_level >= l_dbg_level)
181 THEN
182 FND_LOG.String( l_proc_level
183 ,l_module_prefix||'.'||l_proc_name||'.begin'
184 ,'Enter procedure for Journal/Transaction '||TO_CHAR(l_jt_id)
185 || '''s Line ' || l_line_num
186 );
187 END IF; --(l_proc_level >= l_dbg_level)
188
189 l_line_chk := '';
190
191 --Get account segment
192 l_seg_type := 'GL_ACCOUNT';
193 OPEN c_ffv;
194 LOOP
195 FETCH c_ffv INTO l_ffv_flex_value
196 ,l_seg_fsav_gcc
197 ;
198 EXIT WHEN c_ffv%NOTFOUND;
199 IF l_ffv_flex_value = l_seg_fsav_gcc THEN
200 l_account_num := l_ffv_flex_value;
201 EXIT;
202 END IF;
203 END LOOP;
204 CLOSE c_ffv;
205
206 IF l_account_num is not null
207 THEN
208 --check the line's account is cash related or not.
209 --Account is cash related one when its number is in table JA_CN_CASH_ACCOUNTS_ALL
210 SELECT count(*) row_count
211 INTO l_account_check
212 FROM JA_CN_CASH_ACCOUNTS_ALL cash_acc
213 WHERE cash_acc.ACCOUNT_SEGMENT_VALUE = l_account_num --using variable l_account_num
214 AND cash_acc.chart_of_accounts_id = l_coa_id --using variable l_coa_id
215 ;
216
217 IF l_source = 'GL' THEN
218 --GL
219 --Get account's company segment
220 l_seg_type := 'GL_BALANCING';
221 OPEN c_ffv;
222 LOOP
223 FETCH c_ffv INTO l_ffv_flex_value
224 ,l_seg_fsav_gcc
225 ;
226 EXIT WHEN c_ffv%NOTFOUND;
227 IF l_ffv_flex_value is not null AND
228 l_seg_fsav_gcc is not null AND
229 l_ffv_flex_value = l_seg_fsav_gcc
230 THEN
231 l_com_seg := l_ffv_flex_value;
232 EXIT;
233 END IF;
234 END LOOP;
235 CLOSE c_ffv;
236
237 /*
238 IF l_com_segment is not null AND
239 l_com_seg is not null AND
240 l_com_seg <> l_com_segment --?? not sure
241 THEN
242 l_com_seg := '';
243 END IF;
244 */
245
246 IF l_com_seg is not null
247 THEN
248 --check the company segment belongs to the current legal entity or not.
249 SELECT count(*) row_count
250 INTO l_com_seg_check
251 FROM JA_CN_LEDGER_LE_BSV_GT tmpbsv
252 WHERE tmpbsv.LEGAL_ENTITY_ID = l_le_id --using variable l_le_id
253 AND tmpbsv.ledger_id = l_ledger_id --using variable l_ledger_id
254 AND tmpbsv.bal_seg_value = l_com_seg; --using variable l_com_seg
255
256 IF l_com_seg_check > 0 THEN --the company segment belongs to the current legal entity
257 --line has cash related account AND the cash flow item DFF is blank, JA_CN_NO_CASHFLOWITEM
258 IF l_account_check > 0 AND l_csi_check = 'B' THEN
259 l_line_chk := 'NO_ITEM';
260 --line has no cash related account AND the cash flow item DFF is NOT blank, JA_CN_NO_CASHACCOUNT
261 ELSIF l_account_check < 1 AND l_csi_check = 'NB' THEN
262 l_line_chk := 'NO_ACCOUNT';
263 ELSE
264 l_line_chk := 'OK';
265 END IF;
266 END IF; --l_com_seg_check > 0
267 END IF; --l_com_seg is not null
268
269 ELSE
270 --'AGIS'
271 --line has cash related account AND the cash flow item DFF is blank, JA_CN_NO_CASHFLOWITEM
272 --IF l_account_check > 0 THEN
273 -- get the cash relate item from GL
274 BEGIN
275 /* SELECT decode(jel.context, dffa.context_code,
276 decode(dffa.attribute_column, 'ATTRIBUTE1',jel.attribute1, 'ATTRIBUTE2',jel.attribute2,
277 'ATTRIBUTE3',jel.attribute3, 'ATTRIBUTE4',jel.attribute4, 'ATTRIBUTE5',jel.attribute5,
278 'ATTRIBUTE6',jel.attribute6, 'ATTRIBUTE7',jel.attribute7, 'ATTRIBUTE8',jel.attribute8,
279 'ATTRIBUTE9',jel.attribute9, 'ATTRIBUTE10',jel.attribute10, 'ATTRIBUTE11',jel.attribute11,
280 'ATTRIBUTE12',jel.attribute12, 'ATTRIBUTE13',jel.attribute13, 'ATTRIBUTE14',jel.attribute14,
281 'ATTRIBUTE15',jel.attribute15)
282 ) cash_related_item*/
283 SELECT global_attribute6 cash_related_item
284 INTO l_cash_flow_item_from_GL
285 FROM gl_je_lines jel
286 , fun_trx_headers trxh
287 , fun_trx_lines trxl
288 , fun_dist_lines distl
289 --,ja_cn_dff_assignments dffa
290 WHERE distl.dist_id = l_jt_id -- transaction header id
291 AND distl.line_id=trxl.line_id
292 AND trxh.trx_id = trxl.trx_id
293 AND jel.reference_2 = TO_CHAR(trxh.batch_id)
294 AND jel.reference_3 = TO_CHAR(trxh.trx_id)
295 AND jel.reference_4 = TO_CHAR(trxl.line_id)
296 AND jel.reference_5 = TO_CHAR(distl.dist_id)
297 AND jel.ledger_id=l_ledger_id -- care only current ledgers'
298 --AND jel.status='P' -- care only post journels from trxes --?? NOT SURE
299 -- to locate cash flow item in dff_assignment
300 -- AND dffa.Application_Id = 101
301 -- AND dffa.chart_of_accounts_id = l_coa_id --using variable l_coa_id
302 -- AND dffa.dff_title_code='GLLI'
303 ;
304 Exception
305 WHEN NO_DATA_FOUND THEN
306 l_cash_flow_item_from_GL := null;
307 END;
308 --END IF;
309
310 -- set the cash flow item check status If the journel cash flow item is not null
311 IF l_cash_flow_item_from_GL IS NOT NULL THEN
312 l_csi_check := 'NB';
313 END IF;
314
315 IF l_account_check > 0 AND l_csi_check = 'B'
316 THEN
317 l_line_chk := 'NO_ITEM';
318 --line has no cash related account AND the cash flow item DFF is NOT blank, JA_CN_NO_CASHACCOUNT
319 ELSIF l_account_check < 1 AND l_csi_check = 'NB'
320 THEN
321 l_line_chk := 'NO_ACCOUNT';
322 ELSE
323 l_line_chk := 'OK';
324 END IF;
325 END IF; --l_source = 'GL'/'GIS'
326 END IF; --l_account_num is not null
327
328 IF l_line_chk is null --The account should be excluded
329 THEN
330 l_line_chk := 'EXCLUDED';
331 END IF;
332
333 --log for debug
334 IF (l_proc_level >= l_dbg_level)
335 THEN
336 FND_LOG.String( l_proc_level
337 ,l_module_prefix||'.'||l_proc_name||'.end'
338 ,'Exit procedure for Journal/Transaction '||TO_CHAR(l_jt_id)
339 || '''s Line ' || l_line_num
340 ||' and its l_line_chk is ' || l_line_chk
341 );
342 END IF; --(l_proc_level >= l_dbg_level)
343 return l_line_chk;
344
345 EXCEPTION
346 WHEN OTHERS THEN
347 IF (l_proc_level >= l_dbg_level)
348 THEN
349 FND_LOG.String( l_proc_level
350 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
351 ,SQLCODE||':'||SQLERRM
352 );
353 FND_LOG.String( l_proc_level
354 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
355 ,'For Journal/Transaction '||TO_CHAR(l_jt_id)
356 || '''s Line ' || l_line_num
357 || ', So set the check as ''EXCLUDED'', meaning excluded the line.'
358 );
359 END IF; --(l_proc_level >= l_dbg_level)
360 return 'EXCLUDED';
361 END Line_Check;
362
363 --==========================================================================
364 -- PROCEDURE NAME:
365 -- Get_Account_Combo_and_Desc public
366 --
367 -- DESCRIPTION:
368 -- This procedure gets flexfiled and description of specified account.
369 --
370 -- PARAMETERS:
371 -- In: P_LEDGER_ID NUMBER Chart of accounts ID
372 -- In: P_CCID NUMBER ID of code commbination
373 -- Out: P_ACCOUNT VARCHAR2 Combined account
374 -- Out: P_ACCOUNT_DESC VARCHAR2 Description of account
375 --
376 -- DESIGN REFERENCES:
377 -- None
378 --
379 -- CHANGE HISTORY:
380 -- 02/24/2006 Andrew Liu Created
381 -- 04/21/2007 Yucheng Sun Updated
382 --===========================================================================
383 PROCEDURE Get_Account_Combo_and_Desc( P_LEDGER_ID IN NUMBER
384 ,P_CCID IN NUMBER
385 ,P_ACCOUNT OUT NOCOPY VARCHAR2
386 ,P_ACCOUNT_DESC OUT NOCOPY VARCHAR2
387 ) IS
388 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
389 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
390 l_proc_name VARCHAR2(100) :='Get_Account_Combo_and_Desc';
391
392 l_ledger_id number := P_LEDGER_ID;
393 l_cc_id number := P_CCID;
394
395 l_delimiter_label FND_ID_FLEX_STRUCTURES.Concatenated_Segment_Delimiter%TYPE;
396
397 TYPE t_segemnt_type IS RECORD ( seg_num FND_ID_FLEX_SEGMENTS.SEGMENT_NUM%TYPE
398 ,seg_name FND_ID_FLEX_SEGMENTS.Segment_Name%TYPE
399 ,column_name FND_ID_FLEX_SEGMENTS.APPLICATION_COLUMN_NAME%TYPE
400 ,value_set_id FND_ID_FLEX_SEGMENTS.FLEX_VALUE_SET_ID%TYPE
401 );
402 TYPE t_segemnt_array IS TABLE OF t_segemnt_type;
403 l_all_segemnts t_segemnt_array;
404 l_segemnt t_segemnt_type;
405
406 TYPE t_acc_seg_type IS RECORD ( seg_num FND_ID_FLEX_SEGMENTS.SEGMENT_NUM%TYPE
407 ,seg_name FND_ID_FLEX_SEGMENTS.SEGMENT_NAME%TYPE
408 ,flex_val FND_FLEX_VALUES.Flex_Value%TYPE
409 ,flex_desc FND_FLEX_VALUES_TL.Description%TYPE
410 );
411 TYPE t_acc_segt_array IS TABLE OF t_acc_seg_type;
412 l_all_acc_seg t_acc_segt_array;
413 l_acc_seg t_acc_seg_type;
414 l_has_the_seg number;
415
416 --l_sql varchar2(4000);
417
418 BEGIN
419 --Get delimiter label
420 SELECT FIFStr.Concatenated_Segment_Delimiter
421 INTO l_delimiter_label
422 FROM GL_CODE_COMBINATIONS gcc
423 ,FND_ID_FLEX_STRUCTURES FIFStr
424 WHERE gcc.code_combination_id = l_cc_id --using variable l_cc_id
425 AND FIFStr.APPLICATION_ID=101
426 AND FIFStr.ID_FLEX_CODE='GL#'
427 AND FIFStr.ID_FLEX_NUM = gcc.chart_of_accounts_id
428 ;
429
430 --Get all segments of ACCOUNTING FLEXFIELD
431 BEGIN
432 SELECT FIFS.SEGMENT_NUM
433 ,FIFS.Segment_Name
434 ,FIFS.APPLICATION_COLUMN_NAME
435 ,FIFS.FLEX_VALUE_SET_ID
436 BULK COLLECT INTO l_all_segemnts
437 FROM GL_CODE_COMBINATIONS gcc
438 ,FND_ID_FLEX_SEGMENTS FIFS
439 WHERE gcc.code_combination_id = l_cc_id --using variable l_cc_id
440 AND FIFS.APPLICATION_ID=101
441 AND FIFS.ID_FLEX_CODE='GL#' -- using standard flex code, without it the output will be reduplicate
442 AND FIFS.ID_FLEX_NUM = gcc.chart_of_accounts_id
443 ORDER BY FIFS.SEGMENT_NUM
444 ;
445 EXCEPTION
446 WHEN NO_DATA_FOUND THEN
447 null;
448 END;
449
450 /*
451 --Get value and description of all segments
452 l_sql :=
453 'SELECT DISTINCT '
454 ||' FIFS.SEGMENT_NUM seg_num' --the output will order by it!
455 ||' ,FIFS.SEGMENT_NAME seg_name'
456 ||' ,FFV.Flex_Value flex_value'
457 ||' ,FFVT.Description flex_desc'
458 ||' BULK COLLECT INTO :1'
459 ||' FROM GL_CODE_COMBINATIONS gcc'
460 ||' ,GL_SETS_OF_BOOKS sob'
461 ||' ,FND_ID_FLEX_SEGMENTS FIFS'
462 ||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV'
463 ||' ,FND_FLEX_VALUE_SETS FFVS'
464 ||' ,FND_FLEX_VALUES FFV'
465 ||' ,FND_FLEX_VALUES_TL FFVT'
466 ||' WHERE gcc.code_combination_id = :2' --using variable l_cc_id
467 ||' AND sob.chart_of_accounts_id = gcc.chart_of_accounts_id'
468 ||' AND sob.set_of_books_id = :3' --using variable l_sob_id
469 ||' AND FIFS.id_flex_num = gcc.chart_of_accounts_id'
470 ||' AND FIFS.id_flex_num = FSAV.id_flex_num'
471 ||' AND FIFS.application_id = 101'
472 ||' AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME'
473 --AND FSAV.SEGMENT_ATTRIBUTE_TYPE = l_seg_type --Just not check the type!
474 ||' AND FIFS.application_id = FSAV.application_id'
475 ||' AND FSAV.ATTRIBUTE_VALUE = ''Y'''
476 ||' AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID'
477 ||' AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID'
478 ||' AND GCC.' || FSAV.APPLICATION_COLUMN_NAME || ' = FFV.Flex_Value'
479 ||' and FFVT.flex_value_id = FFV.flex_value_id'
480 ||' and nvl(FFVT.LANGUAGE, userenv(''LANG'')) = userenv(''LANG'')';
481
482 execute immediate l_sql using l_all_acc_seg, l_cc_id, l_sob_id;
483 */
484
485 --Get flex_value and description of account segments in ACCOUNTING FLEXFIELD
486 BEGIN
487 SELECT DISTINCT
488 FIFS.SEGMENT_NUM seg_num --the output will order by it!
489 ,FIFS.SEGMENT_NAME seg_name
490 ,FFV.Flex_Value flex_value
491 ,FFVT.Description flex_desc
492 BULK COLLECT INTO l_all_acc_seg
493 FROM GL_CODE_COMBINATIONS gcc
494 ,GL_LEDGERS ledger
495 ,FND_ID_FLEX_SEGMENTS FIFS
496 ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
497 ,FND_FLEX_VALUE_SETS FFVS
498 ,FND_FLEX_VALUES FFV
499 ,FND_FLEX_VALUES_TL FFVT
500 WHERE gcc.code_combination_id = l_cc_id --using variable l_cc_id
501 AND ledger.chart_of_accounts_id = gcc.chart_of_accounts_id
502 AND ledger.ledger_id = l_ledger_id --using variable l_ledger_id
503 AND FIFS.id_flex_num = gcc.chart_of_accounts_id
504 AND FIFS.id_flex_num = FSAV.id_flex_num
505 AND FIFS.application_id = 101
506 AND FIFS.ID_FLEX_CODE='GL#' -- using standard flex code, without it the output will be reduplicate
507 AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
508 --AND FSAV.SEGMENT_ATTRIBUTE_TYPE = l_seg_type --Just not check the type!
509 AND FSAV.ATTRIBUTE_VALUE = 'Y'
510 AND FIFS.application_id = FSAV.application_id
511 AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
512 AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
513 AND DECODE(FSAV.APPLICATION_COLUMN_NAME, --segment FSAV.APPLICATION_COLUMN_NAME of gcc
514 'SEGMENT1',GCC.SEGMENT1, 'SEGMENT2',GCC.SEGMENT2, 'SEGMENT3',GCC.SEGMENT3,
515 'SEGMENT4',GCC.SEGMENT4, 'SEGMENT5',GCC.SEGMENT5, 'SEGMENT6',GCC.SEGMENT6,
516 'SEGMENT7',GCC.SEGMENT7, 'SEGMENT8',GCC.SEGMENT8, 'SEGMENT9',GCC.SEGMENT9,
517 'SEGMENT10',GCC.SEGMENT10, 'SEGMENT11',GCC.SEGMENT11, 'SEGMENT12',GCC.SEGMENT12,
518 'SEGMENT13',GCC.SEGMENT13, 'SEGMENT14',GCC.SEGMENT14, 'SEGMENT15',GCC.SEGMENT15,
519 'SEGMENT16',GCC.SEGMENT16, 'SEGMENT17',GCC.SEGMENT17, 'SEGMENT18',GCC.SEGMENT18,
520 'SEGMENT19',GCC.SEGMENT19, 'SEGMENT20',GCC.SEGMENT20, 'SEGMENT21',GCC.SEGMENT21,
521 'SEGMENT22',GCC.SEGMENT22, 'SEGMENT23',GCC.SEGMENT23, 'SEGMENT24',GCC.SEGMENT24,
522 'SEGMENT25',GCC.SEGMENT25, 'SEGMENT26',GCC.SEGMENT26, 'SEGMENT27',GCC.SEGMENT27,
523 'SEGMENT28',GCC.SEGMENT28, 'SEGMENT29',GCC.SEGMENT29, 'SEGMENT30',GCC.SEGMENT30) = FFV.Flex_Value
524 AND FFVT.flex_value_id = FFV.flex_value_id
525 AND nvl(FFVT.LANGUAGE, userenv('LANG')) = userenv('LANG')
526 ;
527 EXCEPTION
528 WHEN NO_DATA_FOUND THEN
529 null;
530 END;
531
532 --Check account's all segments one by one, and
533 --Generate its flexfield and description
534 P_ACCOUNT := '';
535 P_ACCOUNT_DESC := '';
536 IF l_all_segemnts.first is not null
537 THEN
538 FOR i IN l_all_segemnts.first .. l_all_segemnts.last LOOP
539 l_segemnt := l_all_segemnts(i);
540
541 l_has_the_seg := 0;
542 IF l_all_acc_seg.first is not null
543 THEN
544 FOR j IN l_all_acc_seg.first .. l_all_acc_seg.last LOOP
545 l_acc_seg := l_all_acc_seg(j);
546 IF l_acc_seg.seg_num = l_segemnt.seg_num
547 THEN
548 l_has_the_seg := 1;
549 EXIT;
550 END IF;
551 END LOOP;
552 END IF;
553
554 IF l_has_the_seg = 0 --the account don't have this segment
555 THEN
556 P_ACCOUNT := P_ACCOUNT || l_delimiter_label;
557 P_ACCOUNT_DESC := P_ACCOUNT_DESC || l_delimiter_label;
558 ELSE
559 P_ACCOUNT := P_ACCOUNT || l_delimiter_label || l_acc_seg.flex_val;
560 P_ACCOUNT_DESC := P_ACCOUNT_DESC || l_delimiter_label || l_acc_seg.flex_desc;
561 END IF; --l_has_the_seg = 0 OR 1
562 END LOOP; --FOR i IN l_all_segemnts.first .. l_all_segemnts.last LOOP
563 END IF;
564
565 --trim off the delimiter before first segment
566 P_ACCOUNT := SUBSTR(P_ACCOUNT, 2, length(P_ACCOUNT)-1);
567 P_ACCOUNT_DESC := SUBSTR(P_ACCOUNT_DESC, 2, length(P_ACCOUNT_DESC)-1);
568
569 --dbms_output.put_line(P_ACCOUNT);
570 --dbms_output.put_line(P_ACCOUNT_DESC);
571
572 EXCEPTION
573 WHEN OTHERS THEN
574 IF (l_proc_level >= l_dbg_level)
575 THEN
576 FND_LOG.String( l_proc_level
577 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
578 ,SQLCODE||':'||SQLERRM);
579 END IF; --(l_proc_level >= l_dbg_level)
580 P_ACCOUNT := '';
581 P_ACCOUNT_DESC := '';
582 END Get_Account_Combo_and_Desc;
583
584 --==========================================================================
585 -- PROCEDURE NAME:
586 -- GL_Validation Public
587 --
588 -- DESCRIPTION:
589 -- This procedure checks GL Journals and output the invalid ones.
590 --
591 -- PARAMETERS:
592 -- Out: errbuf NOCOPY VARCHAR2
593 -- Out: retcode NOCOPY VARCHAR2
594 -- IN: P_COA_ID NUMBER ID of chart of accounts
595 -- In: P_LE_ID NUMBER ID of Legal Entity
596 -- In: P_LEDGER_ID NUMBER ID of the ledger
597 -- In: P_START_PERIOD VARCHAR2 Start period
598 -- In: P_END_PERIOD VARCHAR2 End period
599 -- In: P_SOURCE VARCHAR2 Specified journal source
600 -- In: P_JOURNAL_CTG VARCHAR2 Specified journal category,All the cash related journal categories.
601 --
602 -- DESIGN REFERENCES:
603 -- None
604 --
605 -- CHANGE HISTORY:
606 -- 02/24/2006 Andrew Liu Created
607 -- 04/21/2007 Yucheng Sun Updated
608 -- delete parameter: P_COM_SEGMENT
609 -- 03/09/2008 Chaoqun Wu Updated
610 -- CNAO Enhancement: add company segment
611 -- 10/10/2008 Chaoqun Wu Fix bug#7475903
612 -- 14/10/2008 Chaoqun Wu Fix bug#7481841
613 -- 15/12/2008 Shujuan Yan Fix bug#7626489
614 -- 16/06/2011 Chongwu Li Fix bug#12630377
615 --===========================================================================
616 PROCEDURE GL_Validation( errbuf OUT NOCOPY VARCHAR2
617 ,retcode OUT NOCOPY VARCHAR2
618 ,P_COA_ID IN NUMBER
619 ,P_LE_ID IN NUMBER
620 ,P_LEDGER_ID IN NUMBER
621 ,P_START_PERIOD IN VARCHAR2
622 ,P_END_PERIOD IN VARCHAR2
623 ,P_SOURCE IN VARCHAR2
624 ,P_JOURNAL_CTG IN VARCHAR2
625 ,P_STATUS IN VARCHAR2
626 ,P_COM_SEG IN VARCHAR2 --Added for CNAO Enhancement
627 ) IS
628 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
629 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
630 l_proc_name VARCHAR2(100) :='GL_Validation';
631
632 l_le_id NUMBER := P_LE_ID;
633 l_coa_id NUMBER := P_COA_ID;
634 l_ledger_id NUMBER := P_LEDGER_ID;
635 l_period_from GL_PERIODS.period_name%TYPE := P_START_PERIOD;
636 l_period_to GL_PERIODS.period_name%TYPE := P_END_PERIOD;
637 l_source gl_je_sources_tl.je_source_name%TYPE :=P_SOURCE;
638 l_journal_ctg gl_je_categories_tl.user_je_category_name%TYPE :=P_JOURNAL_CTG;
639 l_le_name VARCHAR2(240);
640 l_status GL_JE_HEADERS.STATUS%TYPE := P_STATUS;
641 l_sts_settle VARCHAR2(200) := ''; -- the 'where' limitation sqls for certain status
642 l_check_flg VARCHAR2(10) :='FALSE'; -- Flag to determine whether the check of JOURNAL will be continued
643
644 -- leger parameters
645 --l_sob_id NUMBER := P_SOB_ID;
646 l_ledger_name VARCHAR2(30);
647
648 l_xml_item XMLTYPE;
649 l_xml_line_items XMLTYPE;
650 l_xml_line XMLTYPE;
651 l_xml_jnl_items XMLTYPE;
652 l_xml_journal XMLTYPE;
653 l_xml_all XMLTYPE;
654 l_xml_root XMLTYPE;
655
656 l_dff_check VARCHAR2(1); --result of DFF Assignment check
657 --l_source_meaning varchar2(150); --meaning of GL journal OR InterCom Transaction
658 l_period_name gl_periods.period_name%TYPE;
659 l_period_year gl_periods.period_year%TYPE;
660 l_period_num gl_periods.period_num%TYPE;
661 l_ccid GL_CODE_COMBINATIONS.Code_Combination_Id%TYPE;
662 l_account VARCHAR2(2000); --account's flexfield
663 l_account_desc VARCHAR2(4000);--account's description
664 l_cash_related_item varchar2(150); --cash related item of a line
665 l_csi_check varchar2(2); --blank or not of cash related item
666 l_line_check varchar2(20); --result of a line's validation
667 l_line_err_msg varchar2(2000);--error message of the line
668
669 l_jnl_count number; --count of all journals
670 l_jnl_valided number; --flag of a journal is valided or not
671 l_invalid_jnl_count number; --count of all invalid journals
672 l_invalid_lines number; --count of all invalid lines
673 l_invalid_line_4_jnl number; --count of invalid lines of a journal
674
675 type t_bulk_jnl_catg is table of gl_je_categories_tl.je_category_name%type;
676 l_all_jnl_catg t_bulk_jnl_catg;
677 l_jnl_catg gl_je_categories_tl.je_category_name%TYPE;
678 l_jnl_src gl_je_sources_tl.je_source_name%TYPE;
679
680 l_je_header_id GL_JE_HEADERS.JE_HEADER_ID%TYPE;
681 l_je_status GL_JE_HEADERS.STATUS%TYPE; -- JOURNAL status
682 l_je_batch_name GL_JE_BATCHES.name%TYPE;
683 l_je_name GL_JE_HEADERS.name%TYPE;
684 l_je_source GL_JE_HEADERS.je_source%TYPE;
685 l_je_usr_source gl_je_sources_tl.user_je_source_name%TYPE;
686 l_je_catg GL_JE_HEADERS.je_category%TYPE;
687 l_je_usr_catg gl_je_categories_tl.user_je_category_name%TYPE;
688 l_je_eff_date GL_JE_HEADERS.default_effective_date%TYPE;
689 l_je_desc GL_JE_HEADERS.description%TYPE;
690 l_je_line_num GL_JE_LINES.je_line_num%TYPE;
691 l_je_line_desc GL_JE_LINES.description%TYPE;
692 l_language VARCHAR(100):=userenv('LANG');
693 l_characterset varchar(245);
694
695 --Cursor to get all periods between (P_START_PERIOD, P_END_PERIOD).
696 CURSOR c_period_name IS
697 SELECT gp.period_name, gp.period_year, gp.period_num
698 FROM gl_periods gp, GL_LEDGERS ledger
699 WHERE ledger.ledger_id = l_ledger_id --using variable P_LEDGER_ID
700 AND ledger.period_set_name = gp.PERIOD_SET_NAME
701 AND ledger.accounted_period_type = gp.period_type
702 AND gp.start_date between
703 (SELECT start_date
704 FROM GL_PERIODS GP
705 WHERE ledger.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
706 AND ledger.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
707 AND GP.period_name = l_period_from) --using parameter P_START_PERIOD
708 and (SELECT start_date
709 FROM GL_PERIODS GP
710 WHERE ledger.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
711 AND ledger.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
712 AND GP.period_name = l_period_to) --using parameter P_END_PERIOD
713 ORDER BY gp.start_date
714 ;
715
716 --Cursor to get all Cash Related Journal Categories
717 -- ?? not sure
718 CURSOR c_all_jnl_catg IS
719 SELECT jec.je_category_name catg_name
720 --,jec.user_je_category_name catg_user_name
721 FROM gl_je_categories_tl jec
722 ,JA_CN_DFF_ASSIGNMENTS DFF
723 WHERE DFF.DFF_TITLE_CODE = 'JOCA'
724 AND jec.context = DFF.CONTEXT_CODE
725 AND jec.language = userenv('LANG')
726 AND nvl(DECODE(DFF.ATTRIBUTE_COLUMN, 'ATTRIBUTE1',jec.attribute1, 'ATTRIBUTE2',jec.attribute2,
727 'ATTRIBUTE3',jec.attribute3, 'ATTRIBUTE4',jec.attribute4, 'ATTRIBUTE5',jec.attribute5),
728 'N') = 'Y'
729 AND DFF.Chart_Of_Accounts_Id = l_coa_id --Added for fixing bug#7475903
730 ;
731
732 /*--Cursor to get all Journal Sources
733 CURSOR c_all_jnl_src IS
734 SELECT jes.je_source_name src_name
735 --,jes.user_je_source_name src_user_name
736 FROM gl_je_sources_tl jes
737 WHERE jes.language = userenv('LANG')
738 ;
739 */
740
741 --Cursor to get information of GL journals in the specified period,
742 -- whose categories are the specified one and thus surely cash ralated.
743 CURSOR c_gl IS
744 SELECT DISTINCT
745 jeh.je_header_id jnl_id
746 ,jeb.name batch
747 ,jeh.name jnl_name
748 ,jeh.je_source jnl_source
749 ,jeh.je_category jnl_catg
750 ,jeh.default_effective_date jnl_eff_date
751 ,jeh.description jnl_des
752 ,src_t.user_je_source_name jnl_usr_source
753 ,catg_t.user_je_category_name jnl_usr_catg
754 ,jeh.status jeh_status
755 FROM GL_JE_BATCHES jeb
756 ,GL_JE_HEADERS jeh
757 ,gl_je_sources_tl src_t
758 ,gl_je_categories_tl catg_t
759 WHERE jeh.ledger_id = l_ledger_id --using variable l_sob_id
760 AND jeb.je_batch_id + 0 = jeh.je_batch_id + 0
761 AND jeb.je_batch_id > 0
762 AND jeh.period_name = l_period_name --using variable l_period_name
763 AND jeh.je_category = l_jnl_catg --using variable l_jnl_catg
764 AND jeh.je_source = l_jnl_src --using variable l_jnl_src
765 -- Select certain transactions settle for the certain P_STATUS :
766 -- While 'null' return all the status,while not return transactions with status of 'P_status'
767 AND (jeh.status = NVL(P_STATUS,'') or P_STATUS IS NULL)
768 AND src_t.je_source_name = jeh.je_source
769 AND src_t.language = userenv('LANG') -- ?? l_language
770 AND catg_t.je_category_name = jeh.je_category
771 AND catg_t.language = userenv('LANG') -- ?? l_language
772 ;
773
774 --Cursor to get specified GL Journal's lines.
775 --Choli updated the cursor, in CNAO V2 solution, GDF global_attribute6 is enable to replace
776 --the old DFF solution, refer to bug 12630377.
777 /*CURSOR c_gl_lines IS
778 SELECT DISTINCT --Added for fixing bug#7475903
779 jel.je_line_num line_num
780 ,jel.description line_desc
781 ,jel.code_combination_id account_ccid
782 ,decode(jel.context, dffa.context_code,
783 decode(dffa.attribute_column, 'ATTRIBUTE1',jel.attribute1, 'ATTRIBUTE2',jel.attribute2,
784 'ATTRIBUTE3',jel.attribute3, 'ATTRIBUTE4',jel.attribute4, 'ATTRIBUTE5',jel.attribute5,
785 'ATTRIBUTE6',jel.attribute6, 'ATTRIBUTE7',jel.attribute7, 'ATTRIBUTE8',jel.attribute8,
786 'ATTRIBUTE9',jel.attribute9, 'ATTRIBUTE10',jel.attribute10, 'ATTRIBUTE11',jel.attribute11,
787 'ATTRIBUTE12',jel.attribute12, 'ATTRIBUTE13',jel.attribute13, 'ATTRIBUTE14',jel.attribute14,
788 'ATTRIBUTE15',jel.attribute15)
789 ) cash_related_item
790 FROM GL_JE_LINES jel
791 ,ja_cn_dff_assignments dffa
792 ,GL_CODE_COMBINATIONS codecmb --Added for CNAO Enhancement
793 ,FND_SEGMENT_ATTRIBUTE_VALUES fsav
794 WHERE jel.je_header_id = l_je_header_id --using variable l_je_header_id
795 -- to locate cash flow item in dff_assignment
796 AND dffa.Application_Id = 101
797 AND dffa.chart_of_accounts_id = l_coa_id --using variable l_coa_id
798 AND dffa.dff_title_code='GLLI'
799
800 --Added for CNAO Enhancement begin
801 AND codecmb.chart_of_accounts_id=dffa.chart_of_accounts_id
802 AND codecmb.code_combination_id = jel.code_combination_id
803 AND fsav.application_id = 101
804 AND fsav.id_flex_num = l_coa_id
805 AND fsav.attribute_value = 'Y'
806 AND fsav.segment_attribute_type = 'GL_BALANCING'
807 AND (P_COM_SEG is null
808 OR P_COM_SEG =
809 DECODE(FSAV.APPLICATION_COLUMN_NAME,
810 'SEGMENT1',codecmb.SEGMENT1, 'SEGMENT2',codecmb.SEGMENT2, 'SEGMENT3',codecmb.SEGMENT3,
811 'SEGMENT4',codecmb.SEGMENT4, 'SEGMENT5',codecmb.SEGMENT5, 'SEGMENT6',codecmb.SEGMENT6,
812 'SEGMENT7',codecmb.SEGMENT7, 'SEGMENT8',codecmb.SEGMENT8, 'SEGMENT9',codecmb.SEGMENT9,
813 'SEGMENT10',codecmb.SEGMENT10, 'SEGMENT11',codecmb.SEGMENT11, 'SEGMENT12',codecmb.SEGMENT12,
814 'SEGMENT13',codecmb.SEGMENT13, 'SEGMENT14',codecmb.SEGMENT14, 'SEGMENT15',codecmb.SEGMENT15,
815 'SEGMENT16',codecmb.SEGMENT16, 'SEGMENT17',codecmb.SEGMENT17, 'SEGMENT18',codecmb.SEGMENT18,
816 'SEGMENT19',codecmb.SEGMENT19, 'SEGMENT20',codecmb.SEGMENT20, 'SEGMENT21',codecmb.SEGMENT21,
817 'SEGMENT22',codecmb.SEGMENT22, 'SEGMENT23',codecmb.SEGMENT23, 'SEGMENT24',codecmb.SEGMENT24,
818 'SEGMENT25',codecmb.SEGMENT25, 'SEGMENT26',codecmb.SEGMENT26, 'SEGMENT27',codecmb.SEGMENT27,
819 'SEGMENT28',codecmb.SEGMENT28, 'SEGMENT29',codecmb.SEGMENT29, 'SEGMENT30',codecmb.SEGMENT30
820 )
821 )
822 ORDER BY jel.je_line_num ASC;*/
823 --Added for CNAO Enhancement end
824 CURSOR c_gl_lines IS
825 SELECT DISTINCT --Added for fixing bug#7475903
826 jel.je_line_num line_num
827 ,jel.description line_desc
828 ,jel.code_combination_id account_ccid
829 ,jel.global_attribute6 cash_related_item
830 FROM GL_JE_LINES jel
831 ,GL_CODE_COMBINATIONS codecmb --Added for CNAO Enhancement
832 ,FND_SEGMENT_ATTRIBUTE_VALUES fsav
833 WHERE jel.je_header_id = l_je_header_id --using variable l_je_header_id
834 AND codecmb.code_combination_id = jel.code_combination_id
835 AND fsav.application_id = 101
836 AND fsav.id_flex_num = l_coa_id
837 AND fsav.attribute_value = 'Y'
838 AND fsav.segment_attribute_type = 'GL_BALANCING'
839 AND (P_COM_SEG is null
840 OR P_COM_SEG =
841 DECODE(FSAV.APPLICATION_COLUMN_NAME,
842 'SEGMENT1',codecmb.SEGMENT1, 'SEGMENT2',codecmb.SEGMENT2, 'SEGMENT3',codecmb.SEGMENT3,
843 'SEGMENT4',codecmb.SEGMENT4, 'SEGMENT5',codecmb.SEGMENT5, 'SEGMENT6',codecmb.SEGMENT6,
844 'SEGMENT7',codecmb.SEGMENT7, 'SEGMENT8',codecmb.SEGMENT8, 'SEGMENT9',codecmb.SEGMENT9,
845 'SEGMENT10',codecmb.SEGMENT10, 'SEGMENT11',codecmb.SEGMENT11, 'SEGMENT12',codecmb.SEGMENT12,
846 'SEGMENT13',codecmb.SEGMENT13, 'SEGMENT14',codecmb.SEGMENT14, 'SEGMENT15',codecmb.SEGMENT15,
847 'SEGMENT16',codecmb.SEGMENT16, 'SEGMENT17',codecmb.SEGMENT17, 'SEGMENT18',codecmb.SEGMENT18,
848 'SEGMENT19',codecmb.SEGMENT19, 'SEGMENT20',codecmb.SEGMENT20, 'SEGMENT21',codecmb.SEGMENT21,
849 'SEGMENT22',codecmb.SEGMENT22, 'SEGMENT23',codecmb.SEGMENT23, 'SEGMENT24',codecmb.SEGMENT24,
850 'SEGMENT25',codecmb.SEGMENT25, 'SEGMENT26',codecmb.SEGMENT26, 'SEGMENT27',codecmb.SEGMENT27,
851 'SEGMENT28',codecmb.SEGMENT28, 'SEGMENT29',codecmb.SEGMENT29, 'SEGMENT30',codecmb.SEGMENT30
852 )
853 )
854 ORDER BY jel.je_line_num ASC;
855
856 BEGIN
857
858 --log for debug
859 IF (l_proc_level >= l_dbg_level)
860 THEN
861 FND_LOG.String( l_proc_level
862 ,l_module_prefix||'.'||l_proc_name||'.begin'
863 ,'Enter procedure'
864 );
865 FND_LOG.String( l_proc_level
866 ,l_module_prefix||'.'||l_proc_name||'.parameters'
867 ,'P_LE_ID '||P_LE_ID
868 );
869 FND_LOG.String( l_proc_level
870 ,l_module_prefix||'.'||l_proc_name||'.parameters'
871 ,'P_START_PERIOD '||P_START_PERIOD
872 );
873 FND_LOG.String( l_proc_level
874 ,l_module_prefix||'.'||l_proc_name||'.parameters'
875 ,'P_END_PERIOD '||P_END_PERIOD
876 );
877 FND_LOG.String( l_proc_level
878 ,l_module_prefix||'.'||l_proc_name||'.parameters'
879 ,'P_SOURCE '||P_SOURCE
880 );
881 FND_LOG.String( l_proc_level
882 ,l_module_prefix||'.'||l_proc_name||'.parameters'
883 ,'P_JOURNAL_CTG '||P_JOURNAL_CTG
884 );
885 FND_LOG.String( l_proc_level --Added for CNAO Enhancement
886 ,l_module_prefix||'.'||l_proc_name||'.parameters'
887 ,'P_COM_SEG '||P_COM_SEG
888 );
889 END IF; --(l_proc_level >= l_dbg_level)
890
891 --Check Profile
892 IF NOT(JA_CN_UTILITY.Check_Profile)
893 THEN
894 retcode := 1;
895 errbuf := '';
896 RETURN;
897 END IF;
898
899 --Get the BSV reffered to the current legal entity and ledger
900 DELETE
901 FROM JA_CN_LEDGER_LE_BSV_GT
902 ;
903 COMMIT ;
904 --
905 IF ja_cn_utility.populate_ledger_le_bsv_gt( P_LEDGER_ID,P_LE_ID) <> 'S' THEN
906 RETURN;
907 END IF;
908
909 --Start the XML file
910 -- Updated by shujuan for bug 7626489
911 l_characterset :=Fnd_Profile.VALUE(NAME => 'ICX_CLIENT_IANA_ENCODING');
912 FND_FILE.put_line(FND_FILE.output,'<?xml version="1.0" encoding= '||'"'||l_characterset||'"?>');
913
914 --FND_FILE.put_line(FND_FILE.output,'<?xml version="1.0" encoding="utf-8" ?>');
915 /*FND_FILE.put_line(FND_FILE.output, '<GL_INVALID_JOURNALS>');
916 FND_FILE.put_line(FND_FILE.output, '<P_START_PERIOD>' ||P_START_PERIOD||'</P_START_PERIOD>');
917 FND_FILE.put_line(FND_FILE.output, '<P_END_PERIOD>' ||P_END_PERIOD||'</P_END_PERIOD>');
918 FND_FILE.put_line(FND_FILE.output, '<P_COMPANY_SEGMENT>' ||P_COM_SEGMENT||'</P_COMPANY_SEGMENT>');
919 FND_FILE.put_line(FND_FILE.output, '<P_SOURCE>' ||P_SOURCE||'</P_SOURCE>');
920 FND_FILE.put_line(FND_FILE.output, '<P_JOURNAL_CTG>' ||P_JOURNAL_CTG||'</P_JOURNAL_CTG>');*/
921 SELECT XMLELEMENT( "P_START_PERIOD",P_START_PERIOD ) INTO l_xml_item FROM dual;
922 l_xml_all := l_xml_item;
923 SELECT XMLELEMENT( "P_END_PERIOD",P_END_PERIOD ) INTO l_xml_item FROM dual;
924 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
925 SELECT XMLELEMENT( "P_COMPANY_SEGMENT",P_COM_SEG ) INTO l_xml_item FROM dual; -- not sure
926 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
927 -- ?? not sure of the source
928 SELECT XMLELEMENT( "P_SOURCE",P_SOURCE ) INTO l_xml_item FROM dual;
929 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
930 SELECT XMLELEMENT( "P_JOURNAL_CTG",P_JOURNAL_CTG ) INTO l_xml_item FROM dual;
931 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
932 SELECT XMLELEMENT( "P_STATUS",P_STATUS ) INTO l_xml_item FROM dual; --Fix bug# 7481841 added
933 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
934
935 --Get ledger Name
936 SELECT ledger.name
937 INTO l_ledger_name
938 FROM GL_LEDGERS ledger
939 WHERE ledger.ledger_id = l_ledger_id
940 ;
941 -- FND_FILE.put_line(FND_FILE.output, '<SOB_NAME>' ||l_sob_name||'</SOB_NAME>');
942 SELECT XMLELEMENT( "LEDGER_NAME",l_ledger_name ) INTO l_xml_item FROM dual;
943 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
944
945 -- Fix bug#6654759 delete start
946 /*
947 --Get LE Name
948 -- ?? not sure , the name from hr_all_organization looks make no sense.
949 SELECT HAOTL.name -- hao.name
950 INTO l_le_name
951 FROM HR_ALL_ORGANIZATION_UNITS HAO
952 ,HR_ALL_ORGANIZATION_UNITS_TL HAOTL
953 WHERE HAO.ORGANIZATION_ID = l_le_id
954 AND HAO.ORGANIZATION_ID = HAOTL.ORGANIZATION_ID
955 AND HAOTL.LANGUAGE = USERENV('LANG')
956 ;*/
957 -- Fix bug#6654759 delete end
958
959 -- Fix bug#6654759 add start
960 --Get LE Name
961 SELECT XEP.name
962 INTO l_le_name
963 FROM XLE_ENTITY_PROFILES XEP
964 WHERE XEP.LEGAL_ENTITY_ID = l_le_id;
965 -- Fix bug#6654759 add end
966
967 /*FND_FILE.put_line(FND_FILE.output, '<LE_NAME>' ||l_le_name||'</LE_NAME>');*/
968 SELECT XMLELEMENT( "LE_NAME",l_le_name ) INTO l_xml_item FROM dual;
969 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
970
971 --1. Check whether the DFF assignment of Journal Categories has been set or not.
972 BEGIN
973 SELECT DECODE(nvl(DFF.CONTEXT_CODE, ''), '', 'N',
974 DECODE(nvl(DFF.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
975 INTO l_dff_check
976 FROM JA_CN_DFF_ASSIGNMENTS DFF
977 WHERE DFF.DFF_TITLE_CODE = 'JOCA'
978 AND DFF.CHART_OF_ACCOUNTS_ID=l_coa_id
979 ;
980 EXCEPTION
981 WHEN NO_DATA_FOUND THEN
982 l_dff_check := 'N';
983 END;
984 IF l_dff_check = 'N'
985 THEN
986 raise JA_CN_INCOMPLETE_DFF_ASSIGN;
987 END IF;
988
989 --l_source_meaning := Source_Meaning(P_SOURCE => 'GLJE'); --Get source meaning
990
991 --Get Journal Source; P_SOURCE is a required parameter.
992 SELECT jes.je_source_name
993 INTO l_jnl_src
994 FROM gl_je_sources_tl jes
995 WHERE jes.user_je_source_name = l_source -- ?? not sure
996 AND jes.language = l_language; -- ?? userenv('LANG');
997
998 --Get cash related Journal Categories: specified in P_JOURNAL_CTG or all.
999 l_all_jnl_catg := t_bulk_jnl_catg();
1000 l_all_jnl_catg.EXTEND(1);
1001 IF l_journal_ctg is not null
1002 THEN
1003 SELECT distinct jec.je_category_name catg_name
1004 INTO l_jnl_catg
1005 FROM gl_je_categories_tl jec
1006 ,JA_CN_DFF_ASSIGNMENTS DFF
1007 WHERE DFF.DFF_TITLE_CODE = 'JOCA'
1008 AND jec.context = DFF.CONTEXT_CODE
1009 AND jec.language = l_language
1010 AND jec.user_je_category_name = l_journal_ctg
1011 AND nvl(DECODE(DFF.ATTRIBUTE_COLUMN, 'ATTRIBUTE1',jec.attribute1,
1012 'ATTRIBUTE2',jec.attribute2, 'ATTRIBUTE3',jec.attribute3,
1013 'ATTRIBUTE4',jec.attribute4, 'ATTRIBUTE5',jec.attribute5),
1014 'N') = 'Y'
1015 ;
1016 l_all_jnl_catg(1) := l_jnl_catg;
1017 ELSE
1018 OPEN c_all_jnl_catg;
1019 FETCH c_all_jnl_catg BULK COLLECT INTO l_all_jnl_catg;
1020 CLOSE c_all_jnl_catg;
1021 END IF;
1022
1023 --2. Get invalid journals
1024 l_jnl_count := 0;
1025 l_invalid_jnl_count := 0;
1026 l_invalid_lines := 0;
1027
1028 IF l_all_jnl_catg.first is not null
1029 THEN
1030 FOR i IN l_all_jnl_catg.first .. l_all_jnl_catg.last LOOP --For cash related Categories.
1031 l_jnl_catg := l_all_jnl_catg(i);
1032
1033 OPEN c_period_name; --Get name of all periods between (P_START_PERIOD, P_END_PERIOD).
1034 LOOP
1035 FETCH c_period_name INTO l_period_name
1036 ,l_period_year
1037 ,l_period_num
1038 ;
1039 EXIT WHEN c_period_name%NOTFOUND;
1040
1041 -- For all journals in the specified Category, Source, and period.
1042 -- Do not check journals' status.
1043 OPEN c_gl;
1044 LOOP
1045 FETCH c_gl INTO l_je_header_id
1046 ,l_je_batch_name
1047 ,l_je_name
1048 ,l_je_source
1049 ,l_je_catg
1050 ,l_je_eff_date
1051 ,l_je_desc
1052 ,l_je_usr_source
1053 ,l_je_usr_catg
1054 ,l_je_status
1055 ;
1056 EXIT WHEN c_gl%NOTFOUND;
1057
1058 /* -- Select certain ledger settle for the certain P_STATUS :
1059 l_check_flg := 'FALSE'; --flag to determin whether the check will continue
1060
1061 IF NVL(l_je_status,'')<>NVL(l_status,'NULL') THEN
1062 IF l_je_status<>'P' OR l_je_status<>'U' THEN
1063 l_check_flg:='TRUE';
1064 END IF;
1065 ELSE
1066 l_check_flg:='TRUE';
1067 END IF;
1068
1069 IF l_check_flg = 'TRUE' THEN*/
1070
1071 l_jnl_count := l_jnl_count + 1; --This journal is a new one
1072 l_invalid_line_4_jnl := 0;
1073 l_jnl_valided := 0;
1074 l_xml_jnl_items := null;
1075
1076 OPEN c_gl_lines; --for the journal's lines.
1077 LOOP
1078 FETCH c_gl_lines INTO l_je_line_num
1079 ,l_je_line_desc
1080 ,l_ccid
1081 ,l_cash_related_item
1082 ;
1083 EXIT WHEN c_gl_lines%NOTFOUND;
1084
1085 l_csi_check := 'NB';
1086 IF l_cash_related_item is null
1087 THEN
1088 l_csi_check := 'B';
1089 END IF;
1090
1091 l_line_check := Line_Check( P_COA_ID => P_COA_ID
1092 ,P_LEDGER_ID => P_LEDGER_ID
1093 ,P_LE_ID => l_le_id
1094 ,P_SOURCE => 'GL'
1095 ,P_JT_ID => l_je_header_id
1096 ,P_LINE_NUM => TO_CHAR(l_je_line_num)
1097 ,P_CCID => l_ccid
1098 ,P_CASH_RELATED_ITEM => l_csi_check
1099 );
1100 IF l_line_check <> 'EXCLUDED'
1101 THEN --The line has been checked, so the journal is valided.
1102 l_jnl_valided := 1;
1103 END IF;
1104
1105 IF l_line_check = 'NO_ITEM' or l_line_check = 'NO_ACCOUNT'
1106 THEN
1107 l_invalid_line_4_jnl := l_invalid_line_4_jnl + 1;
1108 Get_Account_Combo_and_Desc( P_LEDGER_ID => P_LEDGER_ID
1109 ,P_CCID => l_ccid
1110 ,P_ACCOUNT => l_account
1111 ,P_ACCOUNT_DESC => l_account_desc
1112 );
1113
1114 IF l_line_check = 'NO_ITEM'
1115 THEN
1116 l_line_err_msg := l_msg_no_cashflow_item;
1117 ELSE
1118 l_line_err_msg := l_msg_no_cash_account;
1119 END IF;
1120
1121 --Before first line of the journal, output the journal info
1122 IF l_invalid_line_4_jnl = 1
1123 THEN
1124 /*FND_FILE.put_line(FND_FILE.output, '<JOURNAL>');
1125 FND_FILE.put_line(FND_FILE.output, '<BATCH>' ||l_je_batch_name||'</BATCH>');
1126 FND_FILE.put_line(FND_FILE.output, '<JOURNAL_NAME>' ||l_je_name||'</JOURNAL_NAME>');
1127 FND_FILE.put_line(FND_FILE.output, '<SOURCE>' ||l_je_usr_source||'</SOURCE>');
1128 FND_FILE.put_line(FND_FILE.output, '<JOURNAL_CTG>' ||l_je_usr_catg||'</JOURNAL_CTG>');
1129 FND_FILE.put_line(FND_FILE.output, '<JOURNAL_EFF_DATE>' ||l_je_eff_date||'</JOURNAL_EFF_DATE>');
1130 FND_FILE.put_line(FND_FILE.output, '<DESCRIPTION>' ||l_je_desc||'</DESCRIPTION>');*/
1131 SELECT XMLELEMENT( "BATCH",l_je_batch_name ) INTO l_xml_item FROM dual;
1132 l_xml_jnl_items := l_xml_item;
1133 SELECT XMLELEMENT( "JOURNAL_NAME",l_je_name ) INTO l_xml_item FROM dual;
1134 SELECT XMLCONCAT( l_xml_jnl_items,l_xml_item) INTO l_xml_jnl_items FROM dual;
1135 SELECT XMLELEMENT( "SOURCE",l_je_usr_source ) INTO l_xml_item FROM dual;
1136 SELECT XMLCONCAT( l_xml_jnl_items,l_xml_item) INTO l_xml_jnl_items FROM dual;
1137 SELECT XMLELEMENT( "JOURNAL_CTG",l_je_usr_catg ) INTO l_xml_item FROM dual;
1138 SELECT XMLCONCAT( l_xml_jnl_items,l_xml_item) INTO l_xml_jnl_items FROM dual;
1139 SELECT XMLELEMENT( "JOURNAL_EFF_DATE",l_je_eff_date ) INTO l_xml_item FROM dual;
1140 SELECT XMLCONCAT( l_xml_jnl_items,l_xml_item) INTO l_xml_jnl_items FROM dual;
1141 SELECT XMLELEMENT( "DESCRIPTION",l_je_desc ) INTO l_xml_item FROM dual;
1142 SELECT XMLCONCAT( l_xml_jnl_items,l_xml_item) INTO l_xml_jnl_items FROM dual;
1143 END IF;
1144
1145 --output the line
1146 /*FND_FILE.put_line(FND_FILE.output, '<LINE>');
1147 FND_FILE.put_line(FND_FILE.output, '<LINE_NUMBER>' ||l_je_line_num||'</LINE_NUMBER>');
1148 FND_FILE.put_line(FND_FILE.output, '<ACCOUNT>' ||l_account||'</ACCOUNT>');
1149 FND_FILE.put_line(FND_FILE.output, '<ACCOUNT_DESC>' ||l_account_desc||'</ACCOUNT_DESC>');
1150 FND_FILE.put_line(FND_FILE.output, '<CASH_FLOW_ITEM>' ||l_cash_related_item||'</CASH_FLOW_ITEM>');
1151 FND_FILE.put_line(FND_FILE.output, '<EXC_REASON>' ||l_line_err_msg||'</EXC_REASON>');
1152 FND_FILE.put_line(FND_FILE.output, '</LINE>');*/
1153 SELECT XMLELEMENT( "LINE_NUMBER",l_je_line_num ) INTO l_xml_item FROM dual;
1154 l_xml_line_items := l_xml_item;
1155 SELECT XMLELEMENT( "ACCOUNT",l_account ) INTO l_xml_item FROM dual;
1156 SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
1157 SELECT XMLELEMENT( "ACCOUNT_DESC",l_account_desc ) INTO l_xml_item FROM dual;
1158 SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
1159 SELECT XMLELEMENT( "CASH_FLOW_ITEM",l_cash_related_item ) INTO l_xml_item FROM dual;
1160 SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
1161 SELECT XMLELEMENT( "EXC_REASON",l_line_err_msg ) INTO l_xml_item FROM dual;
1162 SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
1163
1164 SELECT XMLELEMENT( "LINE",l_xml_line_items ) INTO l_xml_line FROM dual;
1165 --To concatenate the XML line as a journal item
1166 SELECT XMLCONCAT( l_xml_jnl_items,l_xml_line ) INTO l_xml_jnl_items FROM dual;
1167 END IF; --l_line_check = 'NO_ITEM' or l_line_check = 'NO_ACCOUNT'
1168 END LOOP;
1169 CLOSE c_gl_lines;
1170
1171 IF l_jnl_valided = 0 --The journal has no line been checked, so excluded it.
1172 THEN
1173 l_jnl_count := l_jnl_count -1;
1174 END IF;
1175
1176 IF l_invalid_line_4_jnl > 0 --Has invalid lines, so the journal is a invalid one
1177 THEN
1178 l_invalid_jnl_count := l_invalid_jnl_count + 1; --This journal is a invalid one
1179 l_invalid_lines := l_invalid_lines + l_invalid_line_4_jnl; --Add the invalid lines of this journal
1180
1181 --Has output lines, should end the journal
1182 /*FND_FILE.put_line(FND_FILE.output, '<INVALID_LINES_4_JNL>' ||l_invalid_line_4_jnl||'</INVALID_LINES_4_JNL>');
1183 FND_FILE.put_line(FND_FILE.output, '</JOURNAL>');*/
1184 SELECT XMLELEMENT( "INVALID_LINES_4_JNL",l_invalid_line_4_jnl ) INTO l_xml_item FROM dual;
1185 SELECT XMLCONCAT( l_xml_jnl_items,l_xml_item) INTO l_xml_jnl_items FROM dual;
1186
1187 SELECT XMLELEMENT( "JOURNAL",l_xml_jnl_items ) INTO l_xml_journal FROM dual;
1188 --To concatenate the journal into the output
1189 SELECT XMLCONCAT( l_xml_all,l_xml_journal ) INTO l_xml_all FROM dual;
1190 END IF; --l_invalid_line_4_jnl
1191
1192 --END IF; --cancle the Status judgment for Post and Unpost
1193
1194 END LOOP;
1195 CLOSE c_gl;
1196 END LOOP;
1197 CLOSE c_period_name;
1198 END LOOP; --FOR i IN l_all_jnl_catg.first .. l_all_jnl_catg.last LOOP
1199 END IF;
1200
1201 --End the XML file
1202 /*FND_FILE.put_line(FND_FILE.output, '<TOTAL_COUNT>' || TO_CHAR(l_jnl_count) || '</TOTAL_COUNT>');
1203 FND_FILE.put_line(FND_FILE.output, '<TOTAL_INVALID_JNL>' || TO_CHAR(l_invalid_jnl_count) || '</TOTAL_INVALID_JNL>');
1204 FND_FILE.put_line(FND_FILE.output, '<TOTAL_INVALID_LINES>' || TO_CHAR(l_invalid_lines) || '</TOTAL_INVALID_LINES>');
1205 FND_FILE.put_line(FND_FILE.output, '</GL_INVALID_JOURNALS>');*/
1206 SELECT XMLELEMENT( "TOTAL_COUNT",TO_CHAR(l_jnl_count) ) INTO l_xml_item FROM dual;
1207 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1208 SELECT XMLELEMENT( "TOTAL_INVALID_JNL",TO_CHAR(l_invalid_jnl_count) ) INTO l_xml_item FROM dual;
1209 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1210 SELECT XMLELEMENT( "TOTAL_INVALID_LINES",TO_CHAR(l_invalid_lines) ) INTO l_xml_item FROM dual;
1211 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1212
1213 --To add root node for the xml output and then output it
1214 SELECT XMLELEMENT( "GL_INVALID_JOURNALS",l_xml_all ) INTO l_xml_root FROM dual;
1215 --FND_FILE.put_line(FND_FILE.output,l_xml_root.getclobval());
1216
1217 JA_CN_UTILITY.Output_Conc(l_xml_root.getclobval());
1218
1219 --log for debug
1220 IF (l_proc_level >= l_dbg_level)
1221 THEN
1222 FND_LOG.String( l_proc_level
1223 ,l_module_prefix||'.'||l_proc_name||'.end'
1224 ,'Exit procedure'
1225 );
1226 END IF; --(l_proc_level >= l_dbg_level)
1227
1228 EXCEPTION
1229 WHEN JA_CN_INCOMPLETE_DFF_ASSIGN THEN
1230 IF (l_proc_level >= l_dbg_level)
1231 THEN
1232 FND_LOG.String( l_proc_level
1233 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_INCOMPLETE_DFF_ASSIGN '
1234 ,l_msg_incomplete_dff_assign);
1235 END IF; --(l_proc_level >= l_dbg_level)
1236 /*FND_FILE.put_line(FND_FILE.output, '<TOTAL_COUNT>0</TOTAL_COUNT>');
1237 FND_FILE.put_line(FND_FILE.output, '<DFF_EXCEPTION>' || l_msg_incomplete_dff_assign || '</DFF_EXCEPTION>');
1238 FND_FILE.put_line(FND_FILE.output, '</GL_INVALID_JOURNALS>');*/
1239 SELECT XMLELEMENT( "TOTAL_COUNT",0 ) INTO l_xml_item FROM dual;
1240 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1241 SELECT XMLELEMENT( "DFF_EXCEPTION",l_msg_incomplete_dff_assign ) INTO l_xml_item FROM dual;
1242 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1243 --To add root node for the xml output and then output it
1244 SELECT XMLELEMENT( "GL_INVALID_JOURNALS",l_xml_all ) INTO l_xml_root FROM dual;
1245 JA_CN_UTILITY.Output_Conc(l_xml_root.getclobval());
1246
1247 retcode := 1;
1248 errbuf := l_msg_incomplete_dff_assign;
1249 /*WHEN JA_CN_NO_CASHACCOUNT THEN
1250 Report it with l_msg_no_cash_account;
1251 WHEN JA_CN_NO_CASHFLOWITEM THEN
1252 Report it with l_msg_no_cashflow_item;*/
1253 WHEN OTHERS THEN
1254 IF (l_proc_level >= l_dbg_level)
1255 THEN
1256 FND_LOG.String( l_proc_level
1257 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
1258 ,SQLCODE||':'||SQLERRM);
1259 END IF; --(l_proc_level >= l_dbg_level)
1260 /*FND_FILE.put_line(FND_FILE.output, '<TOTAL_COUNT>0</TOTAL_COUNT>');
1261 FND_FILE.put_line(FND_FILE.output, '<OTHER_EXCEPTION>' || 'Other_Exception' || '</OTHER_EXCEPTION>');
1262 FND_FILE.put_line(FND_FILE.output, '</GL_INVALID_JOURNALS>');*/
1263 SELECT XMLELEMENT( "TOTAL_COUNT",0 ) INTO l_xml_item FROM dual;
1264 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1265 SELECT XMLELEMENT( "DFF_EXCEPTION",'Other_Exception' ) INTO l_xml_item FROM dual;
1266 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1267 --To add root node for the xml output and then output it
1268 SELECT XMLELEMENT( "GL_INVALID_JOURNALS",l_xml_all ) INTO l_xml_root FROM dual;
1269 JA_CN_UTILITY.Output_Conc(l_xml_root.getclobval());
1270
1271 retcode := 2;
1272 errbuf := SQLCODE||':'||SQLERRM;
1273 END GL_Validation;
1274
1275 --==========================================================================
1276 -- PROCEDURE NAME:
1277 -- Intercompany_Validation Public
1278 --
1279 -- DESCRIPTION:
1280 -- This procedure checks Intercompany transactions and output
1281 -- the invalid ones.
1282 --
1283 -- PARAMETERS:
1284 -- Out: errbuf NOCOPY VARCHAR2
1285 -- Out: retcode NOCOPY VARCHAR2
1286 -- In: P_COA_ID NUMBER chart of accounts ID
1287 -- In: P_LE_ID NUMBER ID of Legal Entity
1288 -- In: P_LEDGER_ID NUMBER ID of the ledger
1289 -- In: P_START_PERIOD VARCHAR2 Start period
1290 -- In: P_END_PERIOD VARCHAR2 End period
1291 -- In: P_STATUS VARCHAR2 The gl status transfered from AGIS
1292 --
1293 --
1294 -- DESIGN REFERENCES:
1295 -- None
1296 --
1297 -- CHANGE HISTORY:
1298 -- 02/24/2006 Andrew Liu Created
1299 -- 04/21/2007 Yucheng Sun Updated
1300 -- delete parameter: P_COM_SEGMENT
1301 -- 02/09/2008 Chaoqun Wu Updated
1302 -- CNAO Enhancement: add company segment
1303 -- 14/10/2008 Chaoqun Wu Fix bug#7481841
1304 -- 17/10/2008 Chaoqun Wu Fix bug#7487439
1305 -- 15/12/2008 Shujuan Yan Fix bug#7626489
1306 --===========================================================================
1307 PROCEDURE Intercompany_Validation( errbuf OUT NOCOPY VARCHAR2
1308 ,retcode OUT NOCOPY VARCHAR2
1309 ,P_COA_ID IN NUMBER
1310 ,P_LE_ID IN NUMBER
1311 ,P_LEDGER_ID IN NUMBER
1312 ,P_START_PERIOD IN VARCHAR2
1313 ,P_END_PERIOD IN VARCHAR2
1314 ,P_STATUS IN VARCHAR2
1315 ,P_COM_SEG IN VARCHAR2 --Added for CNAO Enhancement
1316 ) IS
1317 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1318 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1319 l_proc_name VARCHAR2(100) :='Intercompany_Validation';
1320
1321 l_coa_id NUMBER := P_COA_ID;
1322 l_le_id NUMBER := P_LE_ID;
1323 l_le_name VARCHAR2(240);
1324 l_ledger_id NUMBER := P_LEDGER_ID;
1325 l_period_from gl_periods.period_name%TYPE := P_START_PERIOD;
1326 l_period_to gl_periods.period_name%TYPE := P_END_PERIOD;
1327 l_com_seg VARCHAR2(25) := P_COM_SEG; --Added for CNAO Enhancement
1328 l_ledger_name VARCHAR2(30);
1329 --l_com_segment VARCHAR2(150) := P_COM_SEGMENT;
1330 --l_status FUN_TRX_HEADERS.STATUS%TYPE := P_STATUS;
1331 --l_sts_settle VARCHAR2(200) := '';
1332
1333 l_xml_item XMLTYPE;
1334 l_xml_line_items XMLTYPE;
1335 l_xml_line XMLTYPE;
1336 l_xml_tr_items XMLTYPE;
1337 l_xml_tr XMLTYPE;
1338 l_xml_all XMLTYPE;
1339 l_xml_root XMLTYPE;
1340
1341 l_dff_check varchar2(1); --result of DFF Assignment check
1342 --l_source_meaning varchar2(150); --meaning of GL journal OR InterCom Transaction
1343 l_period_name gl_periods.period_name%TYPE;
1344 l_period_year gl_periods.period_year%TYPE;
1345 l_period_num gl_periods.period_num%TYPE;
1346 l_ccid GL_CODE_COMBINATIONS.Code_Combination_Id%TYPE;
1347 l_account VARCHAR2(2000); --account's flexfield
1348 l_account_desc VARCHAR2(4000); --account's description
1349 l_cash_related_item varchar2(150); --cash related item of a line
1350 l_csi_check varchar2(2); --blank or not of cash related item
1351 l_line_check varchar2(20); --result of a line's validation
1352 l_line_err_msg varchar2(2000); --error message of the line
1353
1354 -- trx_batch
1355 l_trxb_id fun_trx_batches.batch_id%TYPE;
1356 l_trxb_num fun_trx_batches.batch_number%TYPE;
1357
1358 -- trx_headers
1359 l_trx_id fun_trx_headers.Trx_Id%TYPE;
1360 l_trx_num fun_trx_headers.Trx_Number%TYPE;
1361 l_trx_type fun_trx_types_tl.trx_type_name%TYPE;
1362 l_trx_send_name hz_parties.party_name%TYPE;
1363 l_trx_recv_name hz_parties.party_name%TYPE;
1364 l_trx_gl_date fun_trx_batches.gl_date%TYPE;
1365 l_trx_desc fun_trx_headers.description%TYPE;
1366 l_trx_line_num varchar2(20);
1367 l_trx_line_sob NUMBER;
1368 l_trx_line_le NUMBER;
1369 l_trx_line_com_seg fun_trx_batches.description%TYPE;
1370
1371 -- trx_lines
1372 l_trxl_num fun_trx_lines.line_id%TYPE;
1373 l_trxl_id fun_trx_lines.trx_id%TYPE;
1374 l_distl_id NUMBER;
1375 l_distl_num fun_dist_lines.dist_number%TYPE;
1376 l_distl_party_id fun_dist_lines.party_id%Type;
1377 l_distl_party_type_flg fun_dist_lines.party_type_flag%TYPE;
1378 l_distl_dist_type_flg fun_dist_lines.dist_type_flag%TYPE;
1379 l_distl_ccid fun_dist_lines.ccid%TYPE;
1380 l_codecmb_coa_id gl_code_combinations.chart_of_accounts_id%TYPE;
1381 l_codecmb_com_seg gl_code_combinations.segment1%TYPE;
1382
1383 --trx date
1384 l_period_start_date date;
1385 l_perioD_end_date date;
1386
1387 -- l_trx_send_com_seg gl_iea_subsidiaries.company_value%TYPE;
1388 -- l_trx_recv_com_seg gl_iea_subsidiaries.company_value%TYPE;
1389 l_trx_send_ledger_id NUMBER; --ledger id of tr's sender
1390 l_trx_recv_ledger_id NUMBER; --ledger id of tr's receiver
1391 l_trx_send_le_id NUMBER; --legal entity id of tr's sender
1392 l_trx_recv_le_id NUMBER; --legal entity id of tr's receiver
1393 l_trx_sender_c NUMBER; --flag of tr's sender is in current LE or not
1394 l_trx_receiver_c NUMBER; --flag of tr's receiver is in current LE or not
1395
1396 -- globale flg for transactions
1397 l_trx_sr_flg varchar2(1):='X'; -- flag to distinguish the diference between sender(S) and receiver(R)(S/R/X).
1398 l_trx_inter_flg varchar2(1):='N'; -- flag to distinguish whether the header is a intercompany operation(Y/N).
1399 l_trx_line_inter_flag varchar2(1):='N'; -- flag to distinguish whether the dist_line is a intercompany operation(Y/N).
1400 l_tr_valided_flg varchar2(1):='N'; -- flag to distinguish whether the header in one loop is recorded;
1401 -- counters
1402 l_tr_count number; --count of all transactions
1403 l_tr_valided number; --flag of a transaction is valided or not
1404 l_invalid_tr_count number; --count of all invalid transactions
1405 l_invalid_lines number; --count of all invalid lines
1406 l_invalid_line_4_tr number; --count of invalid lines of a transaction
1407 l_characterset varchar(245);
1408
1409 --Cursor to get all periods between (P_START_PERIOD, P_END_PERIOD).
1410 CURSOR c_period_name IS
1411 SELECT gp.period_name
1412 , gp.period_year
1413 , gp.period_num
1414 , gp.start_date
1415 , gp.end_date
1416 FROM gl_periods gp, GL_LEDGERS ledger
1417 WHERE ledger.ledger_id = l_ledger_id --using variable l_ledger_id
1418 AND ledger.period_set_name = GP.PERIOD_SET_NAME
1419 AND ledger.accounted_period_type = gp.period_type
1420 AND gp.start_date between
1421 (SELECT start_date
1422 FROM GL_PERIODS GP
1423 WHERE ledger.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
1424 AND ledger.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
1425 AND gp.period_name = l_period_from) --using parameter P_START_PERIOD
1426 and (SELECT start_date
1427 FROM GL_PERIODS GP
1428 WHERE ledger.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
1429 AND ledger.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
1430 AND gp.period_name = l_period_to) --using parameter P_END_PERIOD
1431 ORDER BY gp.start_date
1432 ;
1433 /*
1434 --Cursor to get the BSV according to the current legal entity and ledger id
1435 CURSOR c_bsvs IS
1436 SELECT bsv.*
1437 FROM JA_CN_LEDGER_LE_BSV_GT bsv
1438 WHERE bsv.ledger_id = P_LEDGER_ID
1439 AND bsv.legal_entity_id = P_LE_ID
1440 AND bsv.chart_of_accounts_id = P_COA_ID
1441 ;
1442 */
1443
1444 --Cursor to get information of Transactions in the specified period.
1445 --Only consider Transactions whose sender and receiver transfer flag are 'Yes'.
1446 CURSOR c_tr IS
1447 SELECT trxh.trx_id trxh_id
1448 ,trxh.trx_number trxh_number
1449 ,trxtype.trx_type_name trxtype_name
1450 ,party_init.party_name trxh_send_name
1451 ,party_reci.party_name trxh_recv_name
1452 ,trxb.gl_date trxb_gl_date
1453 ,trxh.description trxb_desc
1454 ,trxb.from_ledger_id trxb_send_ledger_id
1455 ,trxh.to_ledger_id trxh_recv_ledger_id
1456 ,trxb.from_le_id trxb_send_le_id
1457 ,trxh.to_le_id trxh_recv_le_id
1458 ,trxb.batch_number trxb_number
1459 /* ,nvl(DECODE(DFF.ATTRIBUTE_COLUMN, 'ATTRIBUTE1',trxh.attribute1, 'ATTRIBUTE2',trxh.attribute2,
1460 'ATTRIBUTE3',trxh.attribute3, 'ATTRIBUTE4',trxh.attribute4, 'ATTRIBUTE5',trxh.attribute5,
1461 'ATTRIBUTE6',trxh.attribute6, 'ATTRIBUTE7',trxh.attribute7, 'ATTRIBUTE8',trxh.attribute8,
1462 'ATTRIBUTE9',trxh.attribute9, 'ATTRIBUTE10',trxh.attribute10, 'ATTRIBUTE11',trxh.attribute11,
1463 'ATTRIBUTE12',trxh.attribute12, 'ATTRIBUTE13',trxh.attribute13, 'ATTRIBUTE14',trxh.attribute14,
1464 'ATTRIBUTE15',trxh.attribute15)
1465 , '') trxh_cash_related_item*/
1466 FROM FUN_TRX_HEADERS trxh
1467 ,FUN_TRX_BATCHES trxb
1468 ,FUN_TRX_TYPES_TL trxtype
1469 ,HZ_PARTIES party_init
1470 ,HZ_PARTIES party_reci
1471 --,JA_CN_DFF_ASSIGNMENTS dff
1472 WHERE trxh.batch_id=trxb.batch_id
1473 AND trxb.trx_type_id=trxtype.trx_type_id
1474 AND party_init.party_id=trxh.initiator_id
1475 AND party_reci.Party_Id=trxh.recipient_id
1476 -- AND dff.DFF_TITLE_CODE='IITL'--'JOCA'
1477 --AND trxh.status = 'COMPLETE'
1478 --AND trxb.status = 'COMPLETE'
1479 -- Select certain transactions settle for the certain P_STATUS :
1480 -- While 'null' return all the status,while not return transactions with status of 'P_status'
1481 AND (trxh.status = NVL(P_STATUS,'') or P_STATUS IS NULL)
1482 -- determine the trx type is transfered to GL
1483 AND trxh.invoice_flag='N'
1484 -- add period limite , have to limite the gl_date in the period.
1485 AND trxb.gl_date between l_period_start_date and l_perioD_end_date
1486 AND trxtype.language=userenv('LANG');
1487
1488 --Cursor to get specified transaction's lines, including both sender and receiver parts.
1489 CURSOR c_tr_lines IS
1490 SELECT DISTINCT
1491 trl.Line_Number line_num
1492 ,trl.line_id line_id
1493 ,trldist.dist_id distl_id
1494 ,trldist.dist_number distl_num
1495 ,trldist.party_id distl_party_id
1496 ,trldist.party_type_flag distl_party_flg
1497 ,trldist.dist_type_flag distl_dist_flg
1498 ,trldist.ccid distl_ccid
1499 ,codecmb.chart_of_accounts_id codecmb_coa_id
1500 --segment FSAV.APPLICATION_COLUMN_NAME of codecmb
1501 ,DECODE(FSAV.APPLICATION_COLUMN_NAME,
1502 'SEGMENT1',codecmb.SEGMENT1, 'SEGMENT2',codecmb.SEGMENT2, 'SEGMENT3',codecmb.SEGMENT3,
1503 'SEGMENT4',codecmb.SEGMENT4, 'SEGMENT5',codecmb.SEGMENT5, 'SEGMENT6',codecmb.SEGMENT6,
1504 'SEGMENT7',codecmb.SEGMENT7, 'SEGMENT8',codecmb.SEGMENT8, 'SEGMENT9',codecmb.SEGMENT9,
1505 'SEGMENT10',codecmb.SEGMENT10, 'SEGMENT11',codecmb.SEGMENT11, 'SEGMENT12',codecmb.SEGMENT12,
1506 'SEGMENT13',codecmb.SEGMENT13, 'SEGMENT14',codecmb.SEGMENT14, 'SEGMENT15',codecmb.SEGMENT15,
1507 'SEGMENT16',codecmb.SEGMENT16, 'SEGMENT17',codecmb.SEGMENT17, 'SEGMENT18',codecmb.SEGMENT18,
1508 'SEGMENT19',codecmb.SEGMENT19, 'SEGMENT20',codecmb.SEGMENT20, 'SEGMENT21',codecmb.SEGMENT21,
1509 'SEGMENT22',codecmb.SEGMENT22, 'SEGMENT23',codecmb.SEGMENT23, 'SEGMENT24',codecmb.SEGMENT24,
1510 'SEGMENT25',codecmb.SEGMENT25, 'SEGMENT26',codecmb.SEGMENT26, 'SEGMENT27',codecmb.SEGMENT27,
1511 'SEGMENT28',codecmb.SEGMENT28, 'SEGMENT29',codecmb.SEGMENT29, 'SEGMENT30',codecmb.SEGMENT30
1512 ) fsav_com_seg
1513 -- get cash flow item
1514 ,nvl(DECODE(DFF.ATTRIBUTE_COLUMN, 'ATTRIBUTE1',trldist.attribute1, 'ATTRIBUTE2',trldist.attribute2,
1515 'ATTRIBUTE3',trldist.attribute3, 'ATTRIBUTE4',trldist.attribute4, 'ATTRIBUTE5',trldist.attribute5,
1516 'ATTRIBUTE6',trldist.attribute6, 'ATTRIBUTE7',trldist.attribute7, 'ATTRIBUTE8',trldist.attribute8,
1517 'ATTRIBUTE9',trldist.attribute9, 'ATTRIBUTE10',trldist.attribute10, 'ATTRIBUTE11',trldist.attribute11,
1518 'ATTRIBUTE12',trldist.attribute12, 'ATTRIBUTE13',trldist.attribute13, 'ATTRIBUTE14',trldist.attribute14,
1519 'ATTRIBUTE15',trldist.attribute15)
1520 , '') trxh_cash_related_item
1521 FROM FUN_TRX_LINES trl
1522 ,FUN_DIST_LINES trldist
1523 ,GL_CODE_COMBINATIONS codecmb
1524 ,FND_SEGMENT_ATTRIBUTE_VALUES fsav
1525 ,JA_CN_DFF_ASSIGNMENTS dff
1526 WHERE trl.line_id=trldist.line_id
1527 AND trl.trx_id=trldist.trx_id
1528 AND trldist.ccid=codecmb.code_combination_id
1529 AND trl.trx_id=l_trx_id --using variable l_trx_id
1530 AND codecmb.chart_of_accounts_id=P_COA_ID --using variable p_coa_id
1531 AND trldist.dist_type_flag='L' --select ones only transfered to GL
1532 --locate to the right segment attribute value
1533 AND fsav.application_id = 101
1534 AND fsav.id_flex_num = P_COA_ID
1535 AND fsav.attribute_value = 'Y'
1536 AND fsav.segment_attribute_type = 'GL_BALANCING'
1537 AND dff.DFF_TITLE_CODE='IITL'--'JOCA'
1538 --Added for CNAO Enhancement begin
1539 AND (l_com_seg is null
1540 OR l_com_seg =
1541 DECODE(FSAV.APPLICATION_COLUMN_NAME,
1542 'SEGMENT1',codecmb.SEGMENT1, 'SEGMENT2',codecmb.SEGMENT2, 'SEGMENT3',codecmb.SEGMENT3,
1543 'SEGMENT4',codecmb.SEGMENT4, 'SEGMENT5',codecmb.SEGMENT5, 'SEGMENT6',codecmb.SEGMENT6,
1544 'SEGMENT7',codecmb.SEGMENT7, 'SEGMENT8',codecmb.SEGMENT8, 'SEGMENT9',codecmb.SEGMENT9,
1545 'SEGMENT10',codecmb.SEGMENT10, 'SEGMENT11',codecmb.SEGMENT11, 'SEGMENT12',codecmb.SEGMENT12,
1546 'SEGMENT13',codecmb.SEGMENT13, 'SEGMENT14',codecmb.SEGMENT14, 'SEGMENT15',codecmb.SEGMENT15,
1547 'SEGMENT16',codecmb.SEGMENT16, 'SEGMENT17',codecmb.SEGMENT17, 'SEGMENT18',codecmb.SEGMENT18,
1548 'SEGMENT19',codecmb.SEGMENT19, 'SEGMENT20',codecmb.SEGMENT20, 'SEGMENT21',codecmb.SEGMENT21,
1549 'SEGMENT22',codecmb.SEGMENT22, 'SEGMENT23',codecmb.SEGMENT23, 'SEGMENT24',codecmb.SEGMENT24,
1550 'SEGMENT25',codecmb.SEGMENT25, 'SEGMENT26',codecmb.SEGMENT26, 'SEGMENT27',codecmb.SEGMENT27,
1551 'SEGMENT28',codecmb.SEGMENT28, 'SEGMENT29',codecmb.SEGMENT29, 'SEGMENT30',codecmb.SEGMENT30
1552 )
1553 )
1554
1555 --Added for CNAO Enhancement end
1556 -- Fix bug#7487439 added begin
1557 AND EXISTS
1558 (
1559 SELECT * FROM FUN_TRX_HEADERS trxh
1560 ,FUN_TRX_BATCHES trxb
1561 WHERE trxh.batch_id=trxb.batch_id
1562 AND trxh.invoice_flag='N'
1563 AND trxh.trx_id=l_trx_id --using variable l_trx_id
1564 AND
1565 (
1566 ( trxh.to_le_id = l_le_id --using variable l_le_id
1567 AND trxh.to_ledger_id = l_ledger_id --using variable l_le_id
1568 AND trldist.party_type_flag='R'
1569 AND trldist.dist_type_flag ='L'
1570 )
1571 OR
1572 ( trxb.from_le_id = l_le_id --using variable l_le_id
1573 AND trxb.from_ledger_id = l_ledger_id --using variable l_le_id
1574 AND trldist.party_type_flag='I'
1575 AND trldist.dist_type_flag ='L'
1576 )
1577 )
1578 )
1579 -- Fix bug#7487439 added end
1580 ;
1581 BEGIN
1582
1583 --log for debug
1584 IF (l_proc_level >= l_dbg_level)
1585 THEN
1586 FND_LOG.String( l_proc_level
1587 ,l_module_prefix||'.'||l_proc_name||'.begin'
1588 ,'Enter procedure'
1589 );
1590 FND_LOG.String( l_proc_level
1591 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1592 ,'P_LE_ID '||P_LE_ID
1593 );
1594 FND_LOG.String( l_proc_level
1595 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1596 ,'P_START_PERIOD '||l_period_from
1597 );
1598 FND_LOG.String( l_proc_level
1599 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1600 ,'P_END_PERIOD '||l_period_to
1601 );
1602 FND_LOG.String( l_proc_level --Added for CNAO Enhancement
1603 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1604 ,'P_COM_SEG '||l_com_seg
1605 );
1606 END IF; --(l_proc_level >= l_dbg_level)
1607
1608
1609 --Get the BSV reffered to the current legal entity and ledger
1610 DELETE
1611 FROM JA_CN_LEDGER_LE_BSV_GT
1612 ;
1613 COMMIT ;
1614 --
1615 IF ja_cn_utility.populate_ledger_le_bsv_gt( P_LEDGER_ID,P_LE_ID) <> 'S' THEN
1616 RETURN;
1617 END IF;
1618
1619 --Check Profile
1620 IF NOT(JA_CN_UTILITY.Check_Profile)
1621 THEN
1622 retcode := 1;
1623 errbuf := '';
1624 RETURN;
1625 END IF;
1626
1627 --Start the XML file
1628 -- Updated by shujuan for bug 7626489
1629 l_characterset :=Fnd_Profile.VALUE(NAME => 'ICX_CLIENT_IANA_ENCODING');
1630 FND_FILE.put_line(FND_FILE.output,'<?xml version="1.0" encoding= '||'"'||l_characterset||'"?>');
1631
1632 --FND_FILE.put_line(FND_FILE.output,'<?xml version="1.0" encoding="utf-8" ?>');
1633 /*FND_FILE.put_line(FND_FILE.output, '<GIS_INVALID_TRANSACTIONS>');
1634 FND_FILE.put_line(FND_FILE.output, '<P_START_PERIOD>' ||P_START_PERIOD||'</P_START_PERIOD>');
1635 FND_FILE.put_line(FND_FILE.output, '<P_END_PERIOD>' ||P_END_PERIOD||'</P_END_PERIOD>');
1636 FND_FILE.put_line(FND_FILE.output, '<P_COMPANY_SEGMENT>' ||P_COM_SEGMENT||'</P_COMPANY_SEGMENT>');*/
1637 SELECT XMLELEMENT( "P_START_PERIOD",l_period_from ) INTO l_xml_item FROM dual;
1638 l_xml_all := l_xml_item;
1639 SELECT XMLELEMENT( "P_END_PERIOD",l_period_to ) INTO l_xml_item FROM dual;
1640 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1641 SELECT XMLELEMENT( "P_COMPANY_SEGMENT",l_com_seg) INTO l_xml_item FROM dual;
1642 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1643 SELECT XMLELEMENT( "P_STATUS",P_STATUS) INTO l_xml_item FROM dual; --Fix bug# 7481841
1644 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1645
1646 --Get ledger Name
1647 SELECT ledger.name
1648 INTO l_ledger_name
1649 FROM GL_LEDGERS ledger
1650 WHERE ledger.ledger_id = l_ledger_id
1651 ;
1652 /*FND_FILE.put_line(FND_FILE.output, '<LEDGRT_NAME>' ||l_LEDGER_name||'</LEDGER_NAME>');*/
1653 -- sob name --> ledger name ,
1654 -- didn't change the xml schemal,
1655 SELECT XMLELEMENT( "LEDGER_NAME",l_ledger_name ) INTO l_xml_item FROM dual; --Fix bug#7481545
1656 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1657
1658 --Get LE Name
1659 /* SELECT HAOTL.name
1660 INTO l_le_name
1661 FROM HR_ALL_ORGANIZATION_UNITS HAO
1662 ,HR_ALL_ORGANIZATION_UNITS_TL HAOTL
1663 WHERE HAO.ORGANIZATION_ID = l_le_id
1664 AND HAO.ORGANIZATION_ID = HAOTL.ORGANIZATION_ID
1665 AND HAOTL.LANGUAGE = USERENV('LANG')
1666 ;*/
1667 SELECT XEP.name --Updated to fix the issue that no legal entity name was found based on current legal entity id
1668 INTO l_le_name
1669 FROM XLE_ENTITY_PROFILES XEP
1670 WHERE XEP.LEGAL_ENTITY_ID = l_le_id
1671 ;
1672
1673 /*FND_FILE.put_line(FND_FILE.output, '<LE_NAME>' ||l_le_name||'</LE_NAME>');*/
1674 SELECT XMLELEMENT( "LE_NAME",l_le_name ) INTO l_xml_item FROM dual;
1675 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1676
1677 --1. Check whether the DFF assignment of Intercompany Transaction Lines has been set or not.
1678 BEGIN
1679 SELECT distinct DECODE(nvl(DFF.CONTEXT_CODE, ''), '', 'N',
1680 DECODE(nvl(DFF.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
1681 INTO l_dff_check
1682 FROM JA_CN_DFF_ASSIGNMENTS DFF
1683 WHERE DFF.DFF_TITLE_CODE = 'IITL'
1684 AND DFF.chart_of_accounts_id=l_coa_id
1685 ;
1686 EXCEPTION
1687 WHEN NO_DATA_FOUND THEN
1688 l_dff_check := 'N';
1689 END;
1690
1691 IF l_dff_check = 'N' THEN
1692 raise JA_CN_INCOMPLETE_DFF_ASSIGN;
1693 END IF;
1694
1695 -- l_source_meaning := Source_Meaning(P_SOURCE => 'INTR'); --Get source meaning
1696
1697 --2. Get invalid transactions.
1698 --Note:
1699 -- a) Only consider Transactions whose sender's and receiver's invoice_flag are 'N'.
1700 -- b) Only check the lines of Current SOB AND under Current LE, AND
1701 -- their company segment should be the specified one if user inputed.
1702 l_tr_count := 0;
1703 l_invalid_tr_count := 0;
1704 l_invalid_lines := 0;
1705
1706 OPEN c_period_name; --Get year and month of all periods between (P_START_PERIOD, P_END_PERIOD).
1707 LOOP
1708 FETCH c_period_name INTO l_period_name
1709 ,l_period_year
1710 ,l_period_num
1711 ,l_period_start_date
1712 ,l_perioD_end_date
1713 ;
1714 EXIT WHEN c_period_name%NOTFOUND;
1715 -- initial the header counter
1716 l_tr_count :=0;
1717 l_tr_valided_flg := 'N';
1718 --For all transfered transactions in the period.
1719
1720 OPEN c_tr;
1721 LOOP
1722 FETCH c_tr INTO l_trx_id
1723 ,l_trx_num
1724 ,l_trx_type
1725 ,l_trx_send_name
1726 ,l_trx_recv_name
1727 ,l_trx_gl_date
1728 ,l_trx_desc
1729 ,l_trx_send_ledger_id
1730 ,l_trx_recv_ledger_id
1731 ,l_trx_send_le_id
1732 ,l_trx_recv_le_id
1733 ,l_trxb_num
1734 --,l_cash_related_item
1735 ;
1736 EXIT WHEN c_tr%NOTFOUND;
1737
1738 -- Initial the counters
1739 -- l_tr_count := l_tr_count + 1; --This transaction is a new one
1740 l_invalid_line_4_tr := 0;
1741 l_tr_valided := 0;
1742 l_xml_tr_items := null;
1743 l_tr_valided_flg := 'N';
1744
1745 -- For each lines in current transaction
1746 OPEN c_tr_lines;
1747 LOOP
1748 FETCH c_tr_lines INTO l_trxl_num
1749 ,l_trxl_id
1750 ,l_distl_id
1751 ,l_distl_num
1752 ,l_distl_party_id
1753 ,l_distl_party_type_flg
1754 ,l_distl_dist_type_flg
1755 ,l_distl_ccid
1756 ,l_codecmb_coa_id
1757 ,l_codecmb_com_seg
1758 ,l_cash_related_item
1759 ;
1760 EXIT WHEN c_tr_lines%NOTFOUND;
1761
1762 -- initial the lines' error message
1763 l_line_err_msg :='';
1764 --BEGIN
1765
1766 -- check current line whether its company setment belong to the BSV
1767 -- and whether it is a intercompay operation
1768 -- sender amounts;
1769 BEGIN
1770 SELECT count(*)
1771 INTO l_trx_sender_c
1772 FROM JA_CN_LEDGER_LE_BSV_GT tmp_bsv
1773 WHERE tmp_bsv.ledger_id = l_trx_send_ledger_id -- ?? not sure
1774 AND tmp_bsv.legal_entity_id = l_trx_send_le_id -- ?? not sure
1775 AND tmp_bsv.bal_seg_value = l_codecmb_com_seg
1776 ;
1777
1778
1779 EXCEPTION
1780 WHEN NO_DATA_FOUND THEN
1781 l_trx_sender_c := 0;
1782 END;
1783 -- receiver amounts;
1784 BEGIN
1785 SELECT count(*)
1786 INTO l_trx_receiver_c
1787 FROM JA_CN_LEDGER_LE_BSV_GT tmp_bsv
1788 WHERE tmp_bsv.ledger_id = l_trx_recv_ledger_id -- ?? not sure
1789 AND tmp_bsv.legal_entity_id = l_trx_recv_le_id -- ?? not sure
1790 AND tmp_bsv.bal_seg_value = l_codecmb_com_seg
1791 ;
1792
1793
1794 EXCEPTION
1795 WHEN NO_DATA_FOUND THEN
1796 l_trx_receiver_c := 0;
1797 END;
1798 -- check the relationship between S/R
1799 l_trx_line_inter_flag := 'N';
1800 l_trx_sr_flg := 'X';
1801
1802 IF l_trx_sender_c < 1 AND l_trx_receiver_c < 1 THEN
1803 --no one belongs to Current LE
1804 l_trx_line_inter_flag := 'N'; --ignore the tr
1805 l_trx_sr_flg := 'X'; --neither sender nor receiver
1806 ELSIF l_trx_sender_c >= 1 AND l_trx_receiver_c < 1 AND l_distl_party_type_flg='I' THEN
1807 l_trx_line_inter_flag := 'Y'; -- it is a intercompany operation
1808 l_trx_sr_flg := 'S'; -- it is sender
1809 ELSIF l_trx_sender_c < 1 AND l_trx_receiver_c >= 1 AND l_distl_party_type_flg='R' THEN
1810 l_trx_line_inter_flag := 'Y'; -- it is a intercompany operation
1811 l_trx_sr_flg := 'R'; -- it is sender
1812 ELSE --The transaction should be gone through all its lines
1813 l_trx_line_inter_flag := 'N'; --ignore the tr
1814 l_trx_sr_flg := 'X'; --neither sender nor receiver
1815 END IF ;
1816
1817 -- Check only the intercompay operations
1818 IF l_trx_line_inter_flag <> 'X' THEN --l_trx_line_inter_flag = 'y'
1819 -- init
1820 l_csi_check := 'NB';
1821
1822 IF l_cash_related_item is null
1823 THEN
1824 l_csi_check := 'B';
1825 END IF;
1826
1827 l_line_check := Line_Check( P_COA_ID => l_coa_id -- ?? not sure
1828 ,P_LEDGER_ID => l_ledger_id
1829 ,P_LE_ID => l_le_id --l_trx_line_le
1830 ,P_SOURCE => 'AGIS'
1831 ,P_JT_ID => l_distl_id
1832 ,P_LINE_NUM => l_trx_line_num -- ?? NOT SURE
1833 ,P_CCID => l_distl_ccid
1834 ,P_CASH_RELATED_ITEM => l_csi_check
1835 );
1836
1837 -- set header validated amounts
1838 -- if the transactio is ok then increase the trx amount
1839 IF l_line_check ='OK' AND l_tr_valided_flg = 'N' THEN
1840 --The line has been checked, so the transaction is valided.
1841 l_tr_count := l_tr_count + 1;
1842 l_tr_valided_flg := 'Y';
1843 END IF;
1844 -- If the trx amount had increased but in the next line, found that
1845 -- the trx is wrong, then decrease the trx amount at the first wrong time
1846 IF l_line_check <>'OK' AND l_line_check <>'EXCLUDE' AND l_tr_valided_flg = 'Y' THEN
1847 --The line has been checked, so the transaction is valided.
1848 l_tr_count := l_tr_count - 1;
1849 l_tr_valided_flg := 'X';
1850 END IF;
1851
1852 IF l_line_check = 'NO_ITEM' or l_line_check = 'NO_ACCOUNT' THEN
1853 -- increase the line counter
1854 l_invalid_line_4_tr := l_invalid_line_4_tr + 1;
1855
1856 -- get the cash flow item if its cash releted item is null
1857 Get_Account_Combo_and_Desc( P_LEDGER_ID => l_ledger_id -- ?? not sure
1858 ,P_CCID => l_distl_ccid
1859 ,P_ACCOUNT => l_account
1860 ,P_ACCOUNT_DESC => l_account_desc
1861 );
1862
1863 IF l_line_check = 'NO_ITEM' THEN
1864 l_line_err_msg := l_msg_no_cashflow_item;
1865 ELSE
1866 l_line_err_msg := l_msg_no_cash_account;
1867 END IF;
1868
1869 --Before first line of the transaction, output the transaction info
1870 IF l_invalid_line_4_tr = 1 THEN
1871 /*FND_FILE.put_line(FND_FILE.output, '<TRANSACTION>');
1872 FND_FILE.put_line(FND_FILE.output, '<TR_NUMBER>' ||l_tr_num||'</TR_NUMBER>');
1873 FND_FILE.put_line(FND_FILE.output, '<TR_TYPE>' ||l_tr_type||'</TR_TYPE>');
1874 FND_FILE.put_line(FND_FILE.output, '<SENDER>' ||l_tr_send_name||'</SENDER>');
1875 FND_FILE.put_line(FND_FILE.output, '<RECEIVER>' ||l_tr_recv_name||'</RECEIVER>');
1876 FND_FILE.put_line(FND_FILE.output, '<GL_DATE>' ||l_tr_gl_date||'</GL_DATE>');*/
1877
1878 -- using batch_number/trx_number to show the current item
1879 SELECT XMLELEMENT( "TR_NUMBER",l_trxb_num||'/'||l_trx_num ) INTO l_xml_item FROM dual;
1880 l_xml_tr_items := l_xml_item;
1881 SELECT XMLELEMENT( "TR_TYPE",l_trx_type ) INTO l_xml_item FROM dual;
1882 SELECT XMLCONCAT( l_xml_tr_items,l_xml_item) INTO l_xml_tr_items FROM dual;
1883 SELECT XMLELEMENT( "SENDER",l_trx_send_name ) INTO l_xml_item FROM dual;
1884 SELECT XMLCONCAT( l_xml_tr_items,l_xml_item) INTO l_xml_tr_items FROM dual;
1885 SELECT XMLELEMENT( "RECEIVER",l_trx_recv_name ) INTO l_xml_item FROM dual;
1886 SELECT XMLCONCAT( l_xml_tr_items,l_xml_item) INTO l_xml_tr_items FROM dual;
1887 SELECT XMLELEMENT( "GL_DATE",l_trx_gl_date ) INTO l_xml_item FROM dual;
1888 SELECT XMLCONCAT( l_xml_tr_items,l_xml_item) INTO l_xml_tr_items FROM dual;
1889 END IF;
1890
1891 --output the line
1892 /*FND_FILE.put_line(FND_FILE.output, '<LINE>');
1893 FND_FILE.put_line(FND_FILE.output, '<LINE_NUMBER>' ||l_tr_line_num||'</LINE_NUMBER>');
1894 FND_FILE.put_line(FND_FILE.output, '<ACCOUNT>' ||l_account||'</ACCOUNT>');
1895 FND_FILE.put_line(FND_FILE.output, '<ACCOUNT_DESC>' ||l_account_desc||'</ACCOUNT_DESC>');
1896 FND_FILE.put_line(FND_FILE.output, '<CASH_FLOW_ITEM>' ||l_cash_related_item||'</CASH_FLOW_ITEM>');
1897 FND_FILE.put_line(FND_FILE.output, '<EXC_REASON>' ||l_line_err_msg||'</EXC_REASON>');
1898 FND_FILE.put_line(FND_FILE.output, '</LINE>');*/
1899
1900 SELECT XMLELEMENT( "LINE_NUMBER",l_trxl_num ) INTO l_xml_item FROM dual; -- using trx line number to markup recorde with error
1901 l_xml_line_items := l_xml_item;
1902 SELECT XMLELEMENT( "ACCOUNT",l_account ) INTO l_xml_item FROM dual;
1903 SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
1904 SELECT XMLELEMENT( "ACCOUNT_DESC",l_account_desc ) INTO l_xml_item FROM dual;
1905 SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
1906 SELECT XMLELEMENT( "CASH_FLOW_ITEM",l_cash_related_item ) INTO l_xml_item FROM dual;
1907 SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
1908 SELECT XMLELEMENT( "EXC_REASON",l_line_err_msg ) INTO l_xml_item FROM dual;
1909 SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
1910
1911 SELECT XMLELEMENT( "LINE",l_xml_line_items ) INTO l_xml_line FROM dual;
1912 --To concatenate the XML line as a transaction item
1913 SELECT XMLCONCAT( l_xml_tr_items,l_xml_line ) INTO l_xml_tr_items FROM dual;
1914
1915 END IF; -- line check
1916 END IF; -- line inner flag
1917 END LOOP; -- line loop
1918 CLOSE c_tr_lines;
1919
1920 /*
1921 IF l_tr_valided = 0 --The transaction has no line been checked, so excluded it.
1922 THEN
1923 l_tr_count := l_tr_count -1;
1924 END IF;
1925 */
1926 IF l_invalid_line_4_tr > 0 THEN--Has invalid lines, so the transaction is a invalid one
1927 l_invalid_tr_count := l_invalid_tr_count + 1; --This transaction is a invalid one
1928 l_invalid_lines := l_invalid_lines + l_invalid_line_4_tr; --Add the invalid lines of this transaction
1929
1930 --Has output lines, should end the transaction
1931 /*FND_FILE.put_line(FND_FILE.output, '<INVALID_LINES_4_TR>' ||l_invalid_line_4_tr||'</INVALID_LINES_4_TR>');
1932 FND_FILE.put_line(FND_FILE.output, '</TRANSACTION>');*/
1933 SELECT XMLELEMENT( "INVALID_LINES_4_TR",l_invalid_line_4_tr ) INTO l_xml_item FROM dual;
1934 SELECT XMLCONCAT( l_xml_tr_items,l_xml_item) INTO l_xml_tr_items FROM dual;
1935
1936 SELECT XMLELEMENT( "TRANSACTION",l_xml_tr_items ) INTO l_xml_tr FROM dual;
1937 --To concatenate the transaction into the output
1938 SELECT XMLCONCAT( l_xml_all,l_xml_tr ) INTO l_xml_all FROM dual;
1939 END IF; --l_invalid_line_4_tr > 0
1940
1941 END LOOP; -- loop header
1942 CLOSE c_tr;
1943
1944 END LOOP;-- loop period
1945 CLOSE c_period_name;
1946
1947 --End the XML file
1948 /*FND_FILE.put_line(FND_FILE.output, '<TOTAL_COUNT>' || TO_CHAR(l_tr_count) || '</TOTAL_COUNT>');
1949 FND_FILE.put_line(FND_FILE.output, '<TOTAL_INVALID_TR>' || TO_CHAR(l_invalid_tr_count) || '</TOTAL_INVALID_TR>');
1950 FND_FILE.put_line(FND_FILE.output, '<TOTAL_INVALID_LINES>' || TO_CHAR(l_invalid_lines) || '</TOTAL_INVALID_LINES>');
1951 FND_FILE.put_line(FND_FILE.output, '</GIS_INVALID_TRANSACTIONS>');*/
1952 SELECT XMLELEMENT( "TOTAL_COUNT",TO_CHAR(l_tr_count) ) INTO l_xml_item FROM dual;
1953 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1954 SELECT XMLELEMENT( "TOTAL_INVALID_TR",TO_CHAR(l_invalid_tr_count) ) INTO l_xml_item FROM dual;
1955 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1956 SELECT XMLELEMENT( "TOTAL_INVALID_LINES",TO_CHAR(l_invalid_lines) ) INTO l_xml_item FROM dual;
1957 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1958
1959 --To add root node for the xml output and then output it
1960 SELECT XMLELEMENT( "GIS_INVALID_TRANSACTIONS",l_xml_all ) INTO l_xml_root FROM dual;
1961 --FND_FILE.put_line(FND_FILE.output,l_xml_root.getclobval());
1962 JA_CN_UTILITY.Output_Conc(l_xml_root.getclobval());
1963
1964 --log for debug
1965 IF (l_proc_level >= l_dbg_level) THEN
1966 FND_LOG.String(l_proc_level
1967 ,l_module_prefix||'.'||l_proc_name||'.end'
1968 ,'Exit procedure'
1969 );
1970 END IF; --(l_proc_level >= l_dbg_level)
1971
1972 EXCEPTION
1973 WHEN JA_CN_INCOMPLETE_DFF_ASSIGN THEN
1974 IF (l_proc_level >= l_dbg_level) THEN
1975 FND_LOG.String( l_proc_level
1976 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_INCOMPLETE_DFF_ASSIGN '
1977 ,l_msg_incomplete_dff_assign);
1978 END IF; --(l_proc_level >= l_dbg_level)
1979 /*FND_FILE.put_line(FND_FILE.output, '<TOTAL_COUNT>0</TOTAL_COUNT>');
1980 FND_FILE.put_line(FND_FILE.output, '<DFF_EXCEPTION>' || l_msg_incomplete_dff_assign || '</DFF_EXCEPTION>');
1981 FND_FILE.put_line(FND_FILE.output, '</GIS_INVALID_TRANSACTIONS>');*/
1982 SELECT XMLELEMENT( "TOTAL_COUNT",0 ) INTO l_xml_item FROM dual;
1983 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1984 SELECT XMLELEMENT( "DFF_EXCEPTION",l_msg_incomplete_dff_assign ) INTO l_xml_item FROM dual;
1985 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
1986 --To add root node for the xml output and then output it
1987 SELECT XMLELEMENT( "GIS_INVALID_TRANSACTIONS",l_xml_all ) INTO l_xml_root FROM dual;
1988 JA_CN_UTILITY.Output_Conc(l_xml_root.getclobval());
1989
1990 retcode := 1;
1991 errbuf := l_msg_incomplete_dff_assign;
1992 /*WHEN JA_CN_NO_CASHACCOUNT THEN
1993 Report it with l_msg_no_cash_account;
1994 WHEN JA_CN_NO_CASHFLOWITEM THEN
1995 Report it with l_msg_no_cashflow_item;*/
1996 WHEN OTHERS THEN
1997 IF (l_proc_level >= l_dbg_level) THEN
1998 FND_LOG.String( l_proc_level
1999 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
2000 ,SQLCODE||':'||SQLERRM);
2001 END IF; --(l_proc_level >= l_dbg_level)
2002 /*FND_FILE.put_line(FND_FILE.output, '<TOTAL_COUNT>0</TOTAL_COUNT>');
2003 FND_FILE.put_line(FND_FILE.output, '<OTHER_EXCEPTION>' || 'Other_Exception' || '</OTHER_EXCEPTION>');
2004 FND_FILE.put_line(FND_FILE.output, '</GIS_INVALID_TRANSACTIONS>');*/
2005 SELECT XMLELEMENT( "TOTAL_COUNT",0 ) INTO l_xml_item FROM dual;
2006 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
2007 SELECT XMLELEMENT( "DFF_EXCEPTION",'Other_Exception' ) INTO l_xml_item FROM dual;
2008 SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
2009 --To add root node for the xml output and then output it
2010 SELECT XMLELEMENT( "GIS_INVALID_TRANSACTIONS",l_xml_all ) INTO l_xml_root FROM dual;
2011 JA_CN_UTILITY.Output_Conc(l_xml_root.getclobval());
2012
2013 retcode := 2;
2014 errbuf := SQLCODE||':'||SQLERRM;
2015 END Intercompany_Validation;
2016
2017 BEGIN
2018 -- Initialization
2019 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
2020 ,NAME => 'JA_CN_INCOMPLETE_DFF_ASSIGN'
2021 );
2022 l_msg_incomplete_dff_assign := FND_MESSAGE.Get;
2023
2024 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
2025 ,NAME => 'JA_CN_NO_CASHFLOWITEM'
2026 );
2027 l_msg_no_cashflow_item := FND_MESSAGE.Get;
2028
2029 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
2030 ,NAME => 'JA_CN_NO_CASHACCOUNT'
2031 );
2032 l_msg_no_cash_account := FND_MESSAGE.Get;
2033
2034 END JA_CN_GL_INTER_VALID_PKG;
2035