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