[Home] [Help]
PACKAGE BODY: APPS.JA_CN_COA_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_COA_EXPORT_PKG AS
2 --$Header: JACNCOAB.pls 120.5 2010/06/08 08:39:58 jianliu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNCOAB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export COA |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE Add_COA |
16 --| PROCEDURE Get_Acc_Subs_View |
17 --| |
18 --| |
19 --| HISTORY |
20 --| 02/20/2010 Jason Liu Created |
21 --+======================================================================*/
22
23
24 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_COA_EXPORT_PKG';
25 --==========================================================================
26 -- PROCEDURE NAME:
27 -- Get_Acc_Subs_View private
28 --
29 -- DESCRIPTION:
30 -- This procedure gets account number, level, subsidiary account flag,
31 -- and item of project, third party, cost center and personnel, and
32 -- Balance Side of all accounts INTO view 'JA_CN_ACC_SUBS_V'.
33 --
34 -- PARAMETERS:
35 -- In: P_LEDGER_ID NUMBER ID of Ledger
36 -- In: P_COA_ID NUMBER chart of accounts ID
37 --
38 -- DESIGN REFERENCES:
39 -- None
40 --
41 -- CHANGE HISTORY:
42 -- 03/03/2006 Andrew Liu
43 -- 04/27/2007 Yucheng Sun
44 --==========================================================================
45 PROCEDURE Get_Acc_Subs_View(P_LEDGER_ID IN number
46 ,P_COA_ID IN NUMBER) IS
47 l_ledger_id NUMBER := P_LEDGER_ID;
48 l_coa_id NUMBER := P_COA_ID;
49 l_sql_str varchar2(30000):='';
50 l_acc_level_context JA_CN_DFF_ASSIGNMENTS.CONTEXT_CODE%TYPE;
51 l_acc_sub_context JA_CN_DFF_ASSIGNMENTS.CONTEXT_CODE%TYPE;
52 l_acc_bal_context JA_CN_DFF_ASSIGNMENTS.CONTEXT_CODE%TYPE;
53 l_acc_level_position JA_CN_DFF_ASSIGNMENTS.ATTRIBUTE_COLUMN%TYPE;
54 l_sub_pj_position JA_CN_DFF_ASSIGNMENTS.ATTRIBUTE_COLUMN%TYPE;
55 l_sub_tp_position JA_CN_DFF_ASSIGNMENTS.ATTRIBUTE_COLUMN%TYPE;
56 l_sub_cc_position JA_CN_DFF_ASSIGNMENTS.ATTRIBUTE_COLUMN%TYPE;
57 l_sub_person_position JA_CN_DFF_ASSIGNMENTS.ATTRIBUTE_COLUMN%TYPE;
58 l_acc_bal_position JA_CN_DFF_ASSIGNMENTS.ATTRIBUTE_COLUMN%TYPE;
59
60 BEGIN
61 --Get positions of
62 -- account level, project, third party, cost center and personnel,and Balance Side
63 --Generally speaking, the context code of these 6 item are the same one ('subsidary').
64 SELECT nvl(DFF1.CONTEXT_CODE,'') acc_level_context
65 ,nvl(NULL,'') acc_sub_context
66 ,nvl(DFF6.CONTEXT_CODE,'') acc_bal_context
67 ,nvl(DFF1.ATTRIBUTE_COLUMN, '') acc_level_position
68 ,nvl(NULL, '') sub_pj_position
69 ,nvl(NULL, '') sub_tp_position
70 ,nvl(NULL, '') sub_cc_position
71 ,nvl(NULL, '') sub_person_position
72 ,nvl(DFF6.ATTRIBUTE_COLUMN, '') acc_bal_position
73 INTO l_acc_level_context
74 ,l_acc_sub_context
75 ,l_acc_bal_context
76 ,l_acc_level_position
77 ,l_sub_pj_position
78 ,l_sub_tp_position
79 ,l_sub_cc_position
80 ,l_sub_person_position
81 ,l_acc_bal_position
82 FROM JA_CN_DFF_ASSIGNMENTS DFF1
83 ,JA_CN_DFF_ASSIGNMENTS DFF6
84 WHERE DFF1.DFF_TITLE_CODE = 'ACLE' -- Account Level
85 AND DFF6.DFF_TITLE_CODE = 'ACBS' -- Balance Side
86 -- Check whether the flexfields had been set for current COA_ID
87 AND DFF1.CHART_OF_ACCOUNTS_ID=l_coa_id
88 AND DFF6.CHART_OF_ACCOUNTS_ID=l_coa_id
89 ;
90
91 --Combine sql of view. The view will get account number, level, subsidiary account flag,
92 -- and item of project, third party, cost center and personnel and Balance Side
93 -- of all accounts.
94
95 -- add Global Data Elements supporting
96 -- while using Global Data Elements, the value of FND_FLEX_VALUES.VALUE_CATEGORY will be null
97 IF 'Global Data Elements'=nvl(l_acc_level_context,'') THEN
98 l_sql_str :=
99 'SELECT DISTINCT '
100 ||' FFV.FLEX_VALUE acc_number '
101 ||' ,nvl(FFV.' || l_acc_level_position ||', '''') '
102 ||' acc_level '
103 ||' ,''0'' sub_flag '
104 ||' , ''/'' sub_item '
105 ||' ,nvl(FFV.' || l_acc_bal_position ||', '''') '
106 ||' acc_bal '
107 ||' FROM FND_ID_FLEX_SEGMENTS FIFS '
108 ||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV '
109 ||' ,FND_FLEX_VALUE_SETS FFVS '
110 ||' ,FND_FLEX_VALUES FFV '
111 --||' ,GL_LEDGERS LEDGER '
112 ||' WHERE '
113 --Get all correct row of FFV
114 --||' LEDGER.ledger_id = ' || l_LEDGER_id
115 ||' FIFS.id_flex_num = '|| l_coa_id ||' '
116 ||' AND FIFS.id_flex_num = FSAV.id_flex_num '
117 ||' AND FIFS.application_id = 101 '
118 ||' AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME '
119 ||' AND FIFS.application_id = FSAV.application_id '
120 ||' AND FSAV.SEGMENT_ATTRIBUTE_TYPE = ''GL_ACCOUNT'' '
121 ||' AND FSAV.ATTRIBUTE_VALUE = ''Y'' '
122 ||' AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID '
123 ||' AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID '
124 --||' AND FFV.VALUE_CATEGORY IS NULL '
125 ||' ORDER BY FFV.FLEX_VALUE '
126 ;
127 ELSE
128 l_sql_str :=
129 'SELECT DISTINCT '
130 ||' FFV.FLEX_VALUE acc_number '
131 ||' ,DECODE(FFV.VALUE_CATEGORY,'''|| l_acc_level_context ||''','
132 ||' nvl(FFV.' || l_acc_level_position ||', ''''), '
133 ||' '''') acc_level '
134 ||' , ''0'' sub_flag '
135 ||' , ''/'' sub_item '
136 ||' ,DECODE(FFV.VALUE_CATEGORY,'''|| l_acc_bal_context ||''','
137 ||' nvl(FFV.' || l_acc_bal_position ||', ''''), '
138 ||' '''') acc_bal '
139 ||' FROM FND_ID_FLEX_SEGMENTS FIFS '
140 ||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV '
141 ||' ,FND_FLEX_VALUE_SETS FFVS '
142 ||' ,FND_FLEX_VALUES FFV '
143 --||' ,GL_LEDGERS LEDGER '
144 ||' WHERE '
145 --Get all correct row of FFV
146 --||' LEDGER.ledger_id = ' || l_LEDGER_id
147 ||' FIFS.id_flex_num = '|| l_coa_id ||' '
148 ||' AND FIFS.id_flex_num = FSAV.id_flex_num '
149 ||' AND FIFS.application_id = 101 '
150 ||' AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME '
151 ||' AND FIFS.application_id = FSAV.application_id '
152 ||' AND FSAV.SEGMENT_ATTRIBUTE_TYPE = ''GL_ACCOUNT'' '
153 ||' AND FSAV.ATTRIBUTE_VALUE = ''Y'' '
154 ||' AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID '
155 ||' AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID '
156 ||' ORDER BY FFV.FLEX_VALUE '
157 ;
158 END IF;
159
160
161 l_sql_str := 'CREATE OR REPLACE VIEW JA_CN_ACC_SUBS_V AS ' ||
162 l_sql_str;
163 EXECUTE IMMEDIATE l_sql_str;
164
165 End Get_Acc_Subs_View;
166
167 --==========================================================================
168 -- PROCEDURE NAME:
169 -- Add_COA Public
170 --
171 -- DESCRIPTION:
172 -- This procedure expots Natural Accounts, if there are invalid
173 -- Natural Accounts then output an exceptions's report.
174 --
175 -- PARAMETERS:
176 -- In: pn_coa_id NUMBER Chart of account ID
177 -- In: pn_ledger_id NUMBER ID of ledger
178 -- In: pn_le_id NUMBER ID of Legal Entity
179 -- In: P_XML_TEMPLATE_LANGUAGE VARCHAR2 template language of exception report
180 -- In: P_XML_TEMPLATE_TERRITORY VARCHAR2 template territory of exception report
181 -- In: P_XML_OUTPUT_FORMAT VARCHAR2 output format of exception report
182 --
183 -- DESIGN REFERENCES:
184 -- CNAO_V2_GL_TD.doc
185 --
186 -- CHANGE HISTORY:
187 -- 20-FEB-2010 Jason Liu created
188 --===========================================================================
189 PROCEDURE Add_COA
190 (pn_coa_id IN NUMBER
191 ,pn_ledger_id IN NUMBER
192 ,pn_le_id IN NUMBER
193 ,pv_xml_template_language IN VARCHAR2
194 ,pv_xml_template_territory IN VARCHAR2
195 ,pv_xml_output_format IN VARCHAR2
196 )
197 IS
198 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
199 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
200 l_proc_name VARCHAR2(100) :='Add_COA';
201
202 l_coa_id NUMBER := pn_coa_id;
203 l_ledger_id NUMBER := pn_ledger_id;
204 l_le_id NUMBER := pn_le_id;
205 JA_CN_INCOMPLETE_DFF_ASSIGN EXCEPTION;
206 JA_CN_INVALID_ACCOUNT_STRU EXCEPTION;
207 l_msg_incomplete_dff_assign VARCHAR2(2000);
208 l_msg_invalid_account_stru VARCHAR2(2000);
209 l_seperator VARCHAR2(1) := FND_GLOBAL.Local_Chr(9); --' ';
210
211 l_na_curr_req_id NUMBER; --Request id of current request
212 l_xml_layout BOOLEAN;
213 l_template_language VARCHAR2(10) := pv_xml_template_language;
214 l_template_territory VARCHAR2(10) := pv_xml_template_territory;
215 l_output_format VARCHAR2(10) := pv_xml_output_format;
216 l_na_req_id NUMBER; --Request id for concurrent program 'Generating Natural Account Export Exception Report'
217 l_na_req_phase fnd_lookup_values.meaning%TYPE;
218 l_na_req_status fnd_lookup_values.meaning%TYPE;
219 l_na_req_dev_phase VARCHAR2(30);
220 l_na_req_dev_status VARCHAR2(30);
221 l_na_req_message VARCHAR2(100);
222
223 l_dff VARCHAR2(6);
224 l_sql varchar2(10000);
225 l_account_structures_kfv VARCHAR2(100) := 'ja_cn_account_structures_kfv';
226 l_na_acc_str VARCHAR2(2000);
227 l_na_acc_str_2 VARCHAR2(2000);
228
229 l_ent_acc_type VARCHAR2(100);
230 l_delimiter_label FND_ID_FLEX_STRUCTURES.Concatenated_Segment_Delimiter%TYPE;
231 l_acc_segment VARCHAR2(100);
232 TYPE t_acc_level_segments IS TABLE OF NUMBER;
233 l_acc_segments t_acc_level_segments;
234 l_acc_seg_serial NUMBER;
235 TYPE t_level_seg_lens IS TABLE OF NUMBER;
236 l_acc_seg_lens t_level_seg_lens;
237
238 l_na_number FND_FLEX_VALUES.FLEX_VALUE%TYPE;
239 l_na_name FND_FLEX_VALUES_TL.description%TYPE;
240 l_na_parent VARCHAR2(1);
241 l_na_level VARCHAR2(100);
242 l_na_sub_flag VARCHAR2(1);
243 l_na_sub_item VARCHAR2(100);
244 l_acc_type_code VARCHAR2(1);
245 l_acc_bal_code VARCHAR2(100);
246 l_na_mea GL_STAT_ACCOUNT_UOM.UNIT_OF_MEASURE%TYPE;
247
248 l_project_meaning VARCHAR2(100);
249 l_thirdparty_meaning VARCHAR2(100);
250 l_supplier_meaning VARCHAR2(100);
251 l_customer_meaning VARCHAR2(100);
252 l_costcenter_meaning VARCHAR2(100);
253 l_personnel_meaning VARCHAR2(100);
254
255 l_na_type VARCHAR2(50);
256 l_na_bal VARCHAR2(10);
257
258 l_length NUMBER;
259 l_expected_length NUMBER;
260
261 l_exceptions_count NUMBER; --count of invalid account rows
262 l_row_count NUMBER; --count of correct account rows
263
264 lv_procedure_name VARCHAR2(40) := 'Add_COA';
265 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
266 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
267 NO_DATA EXCEPTION;
268 l_msg_no_data_found VARCHAR2(2000);
269
270 --Cursor to get DFF assignment status of Account Level, Project, Third Party,
271 -- Cost Center and Personnel, and Balance Side.
272 --Only a record of 'YYYYYY' expresses that all 6 DFFs have been set.
273 CURSOR c_dff IS
274 SELECT DECODE(nvl(DFF1.CONTEXT_CODE, ''), '', 'N',
275 DECODE(nvl(DFF1.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
276 || DECODE(nvl(DFF6.CONTEXT_CODE, ''), '', 'N',
277 DECODE(nvl(DFF6.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
278 dff_assign
279 FROM JA_CN_DFF_ASSIGNMENTS DFF1
280 ,JA_CN_DFF_ASSIGNMENTS DFF6
281 WHERE DFF1.DFF_TITLE_CODE = 'ACLE' -- Account Level
282 AND DFF6.DFF_TITLE_CODE = 'ACBS' -- Balance Side
283 -- Check whether the flexfields had been set for current COA_ID
284 AND DFF1.CHART_OF_ACCOUNTS_ID=l_coa_id
285 AND DFF6.CHART_OF_ACCOUNTS_ID=l_coa_id
286 ;
287
288 --Cursor to get natural account's Number, Name, Parent flag, Type Code;
289 --AND account level, subsidiary account flag and item of project,
290 -- third party, cost center and personnel, and balance side
291 CURSOR c_na_info IS
292 SELECT DISTINCT
293 --FFV.FLEX_VALUE acc_number -- replace with sub.acc_number
294 sub.acc_number
295 ,nvl(FFVT.description, '') acc_name
296 ,DECODE(FFV.summary_flag, 'Y', 'Y', 'N') acc_parent
297 ,SUBSTR(TO_CHAR(FFV.COMPILED_VALUE_ATTRIBUTES) --such as 'Y Y L'
298 ,5,1) acc_type_code
299 ,nvl(sub.acc_level, '') acc_level
300 ,nvl(sub.sub_flag, '0') sub_flag
301 ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
302 nvl(sub.sub_item, '/'), 'Project', l_project_meaning
303 ), 'Third Party', l_thirdparty_meaning
304 ), 'Supplier', l_supplier_meaning
305 ), 'Customer', l_customer_meaning
306 ), 'Cost Center', l_costcenter_meaning
307 ), 'Personnel', l_personnel_meaning
308 ) sub_item
309 ,nvl(sub.acc_bal, '') acc_bal
310 FROM JA_CN_ACC_SUBS_V sub
311 ,FND_ID_FLEX_SEGMENTS FIFS
312 ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
313 ,FND_FLEX_VALUE_SETS FFVS
314 ,FND_FLEX_VALUES FFV
315 ,FND_FLEX_VALUES_TL FFVT
316 ,GL_LEDGERS ledger
317 WHERE --Get all correct row of FFV
318 ledger.ledger_id = l_ledger_id --using variable l_sob_id
319 AND ledger.chart_of_accounts_id = FIFS.id_flex_num
320 AND FIFS.id_flex_num = FSAV.id_flex_num
321 AND FIFS.application_id = 101
322 AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
323 AND FIFS.application_id = FSAV.application_id
324 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
325 AND FSAV.ATTRIBUTE_VALUE = 'Y'
326 AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
327 AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
328 --AND nvl(FFV.ENABLED_FLAG, 'N') = 'Y' --Including disabled accounts
329 AND FFVT.FLEX_VALUE_ID = FFV.FLEX_VALUE_ID
330 AND nvl(FFVT.LANGUAGE, userenv('LANG')) = userenv('LANG')
331 --For account level, subsidiary account flag and item
332 AND sub.acc_number(+) = FFV.FLEX_VALUE
333 order by sub.acc_number
334 ;
335
336 BEGIN
337 --logging for debug
338 IF (ln_proc_level >= ln_dbg_level)
339 THEN
340 FND_LOG.STRING(ln_proc_level,
341 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
342 '.begin',
343 'Enter procedure');
344 -- logging the parameters
345 FND_LOG.STRING(ln_proc_level,
346 lv_procedure_name ||
347 '.parameters',
348 'pn_coa_id=' || pn_coa_id || ',' ||
349 'pn_ledger_id=' || pn_ledger_id || ',' ||
350 'pn_le_id=' || pn_le_id || ',' ||
351 'pv_xml_template_language=' || pv_xml_template_language || ',' ||
352 'pv_xml_template_territory=' || pv_xml_template_territory || ',' ||
353 'pv_xml_output_format=' || pv_xml_output_format);
354 END IF; --l_proc_level>=l_dbg_level
355 --1. Check whether any DFF assignment of Account Level, Project, Third Party,
356 -- Cost Center and Personnel, and Balance Side has been set or not.
357 --
358 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
359 '.parameters:' ||
360 'pn_coa_id=' || pn_coa_id || ',' ||
361 'pn_ledger_id=' || pn_ledger_id || ',' ||
362 'pn_le_id=' || pn_le_id || ',' ||
363 'pv_xml_template_language=' || pv_xml_template_language || ',' ||
364 'pv_xml_template_territory=' || pv_xml_template_territory || ',' ||
365 'pv_xml_output_format=' || pv_xml_output_format);
366 OPEN c_dff;
367 FETCH c_dff INTO l_dff;
368 IF c_dff%NOTFOUND OR
369 l_dff <> 'YY'
370 THEN
371 RAISE JA_CN_INCOMPLETE_DFF_ASSIGN;
372 END IF;
373 CLOSE c_dff;
374
375 l_sql :=
376 'SELECT '
377 ||' nvl(ACC_STR_V.concatenated_segments, '''') acc_str '
378 ||' FROM Ja_Cn_Sub_Acc_Sources_All SYS_PAR '
379 ||' ,' || l_account_structures_kfv || ' ACC_STR_V '
380 ||'WHERE ACC_STR_V.account_structure_id = SYS_PAR.ACCOUNTING_STRUCT_ID'
381 ||' AND SYS_PAR.CHART_OF_ACCOUNTS_ID = ' || l_coa_id --using parameter P_LE_ID
382 ;
383 EXECUTE IMMEDIATE l_sql into l_na_acc_str;
384
385 --Get delimiter label
386 BEGIN
387 SELECT fifs.concatenated_segment_delimiter separator
388 INTO l_delimiter_label
389 FROM FND_ID_FLEX_STRUCTURES fifs
390 ,ja_cn_account_structures_kfv jcask
391 ,ja_cn_sub_acc_sources_all jcsasa
392 WHERE fifs.id_flex_num = jcask.structure_id
393 AND jcask.account_structure_id = jcsasa.accounting_struct_id
394 AND jcsasa.chart_of_accounts_id = pn_coa_id;
395
396 EXCEPTION
397 WHEN NO_DATA_FOUND THEN
398 l_delimiter_label := '-';
399 WHEN OTHERS THEN
400 l_delimiter_label := '-';
401 END;
402
403 --Get account segments' length
404 l_na_acc_str_2 := l_na_acc_str || l_delimiter_label; --l_na_acc_str has at least 1 segment.
405 l_acc_seg_serial := 1;
406 l_acc_segments := t_acc_level_segments();
407 l_acc_segments.EXTEND(15);
408 l_acc_seg_lens := t_level_seg_lens();
409 l_acc_seg_lens.EXTEND(15);
410 WHILE l_na_acc_str_2 is not null LOOP
411 l_acc_segment := trim(substr(l_na_acc_str_2, 1,
412 instr(l_na_acc_str_2, l_delimiter_label, 1)-1
413 ));
414 l_na_acc_str_2 := substr(l_na_acc_str_2,
415 instr(l_na_acc_str_2, l_delimiter_label, 1)+1,
416 length(l_na_acc_str_2)
417 );
418
419 --Do not check the segment is natural number or not here.
420 l_acc_segments(l_acc_seg_serial) := TO_NUMBER(l_acc_segment);
421 IF l_acc_seg_serial = 1
422 THEN
423 l_acc_seg_lens(l_acc_seg_serial) := l_acc_segments(l_acc_seg_serial);
424 ELSE
425 l_acc_seg_lens(l_acc_seg_serial) := l_acc_seg_lens(l_acc_seg_serial -1 ) +
426 l_acc_segments(l_acc_seg_serial);
427 END IF;
428 l_acc_seg_serial := l_acc_seg_serial + 1;
429 END LOOP; --WHILE l_na_acc_str_2 is not null LOOP
430 -- dbms_output.put_line(l_acc_seg_lens.count); --l_acc_seg_lens.count=15!!!
431 l_acc_seg_serial := l_acc_seg_serial - 1; --count of segments of account structure
432
433 --3. Generate the account level and subsidiary flag and items
434 Get_Acc_Subs_View(P_LEDGER_ID => l_ledger_id
435 ,P_COA_ID => l_coa_id);
436
437 --4. Go through all the natural accounts with a natural number marked in
438 -- "Level" field and lists all invalid accounts in invalid account table
439 -- JA_CN_COA_NA_EXCEPTION.
440 l_na_curr_req_id := FND_GLOBAL.CONC_REQUEST_ID; --id of current request
441
442 OPEN c_na_info;
443 LOOP -- Loop for all natural accounts
444 FETCH c_na_info INTO l_na_number
445 ,l_na_name
446 ,l_na_parent
447 ,l_acc_type_code
448 ,l_na_level
449 ,l_na_sub_flag
450 ,l_na_sub_item
451 ,l_acc_bal_code
452 ;
453 EXIT WHEN c_na_info%NOTFOUND;
454
455 --Only consider accounts with level, and the level should be a natural number and <16
456 IF JA_CN_UTILITY.Check_Account_Level(l_na_level)/*l_na_level is not null AND Is_Natural_Number(l_na_level) = 1 and l_na_level < 16*/
457 THEN
458 l_length := LENGTH(TO_CHAR(l_na_number)); --length of l_na_number
459
460 IF l_acc_seg_serial >= l_na_level THEN --l_na_acc_str has l_na_level segments
461 --l_expected_length is sum first l_na_level segements of l_na_acc_str
462 l_expected_length := l_acc_seg_lens(l_na_level);
463 ELSE
464 l_expected_length := -1;
465 END IF;
466
467 IF l_length <> l_expected_length
468 THEN
469 --Insert a row of account number, level, length, expected length,
470 -- account structure, and current request id into invalid account
471 -- table JA_CN_COA_NA_EXCEPTIONS
472 INSERT INTO JA_CN_COA_NA_EXCEPTIONS
473 ( ACCOUNT_SEGMENT
474 ,ACCOUNT_LEVEL
475 ,VALUE_LENGTH
476 ,EXPECTED_LENGTH
477 ,ACCOUNT_STRUCTURE
478 ,NA_REQUEST_ID
479 ,CREATED_BY
480 ,CREATION_DATE
481 ,LAST_UPDATED_BY
482 ,LAST_UPDATE_DATE
483 ,LAST_UPDATE_LOGIN
484 )
485 VALUES( l_na_number
486 ,l_na_level
487 ,l_length
488 ,l_expected_length
489 ,l_na_acc_str
490 ,l_na_curr_req_id
491 ,fnd_global.user_id
492 ,SYSDATE
493 ,fnd_global.user_id
494 ,SYSDATE
495 ,fnd_global.LOGIN_ID
496 );
497 END IF; --Value length
498 END IF; --Account Level should not null and be a natural number and <16
499 END LOOP;
500 CLOSE c_na_info;
501
502 -- 3. Checks if the invalid account table JA_CN_COA_NA_EXCEPTIONS has any row.
503 -- If YES then records error in output and submits a request to generate
504 -- exception report;
505 -- ELSE goes on to collect all natural accounts. <NOT all lowest level ones>
506 SELECT count(*)
507 INTO l_exceptions_count
508 FROM JA_CN_COA_NA_EXCEPTIONS
509 WHERE NA_REQUEST_ID = l_na_curr_req_id
510 ;
511
512 IF l_exceptions_count > 0 --JA_CN_COA_NA_EXCEPTIONS has row
513 THEN
514 l_xml_layout := FND_REQUEST.ADD_LAYOUT( template_appl_name => 'JA'
515 ,template_code => 'JACNNAER'
516 ,template_language => l_template_language --'zh' ('en')
517 ,template_territory => l_template_territory--'00' ('US')
518 ,output_format => l_output_format --'RTF'('PDF')
519 );
520
521 l_na_req_id := FND_REQUEST.Submit_Request( application=> 'JA'
522 ,program => 'JACNNAER'
523 ,argument1 => to_number(l_na_curr_req_id)
524 );
525 COMMIT;
526
527 IF l_na_req_id <> 0
528 THEN
529 IF FND_CONCURRENT.Wait_For_Request( request_id => l_na_req_id
530 ,interval => 5
531 ,max_wait => 0
532 ,phase => l_na_req_phase
533 ,status => l_na_req_status
534 ,dev_phase => l_na_req_dev_phase
535 ,dev_status => l_na_req_dev_status
536 ,message => l_na_req_message
537 )
538 THEN
539 IF l_na_req_phase = 'Completed'
540 THEN
541 null;
542 END IF; --l_na_req_phase = 'Completed'
543 END IF; -- FND_CONCURRENT.Wait_For_Request ...
544 END IF; --l_na_req_id<>0
545
546 DELETE
547 FROM JA_CN_COA_NA_EXCEPTIONS
548 WHERE NA_REQUEST_ID = l_na_curr_req_id;
549 COMMIT;
550
551 --Report that there have invalid accounts
552 RAISE JA_CN_INVALID_ACCOUNT_STRU;
553
554 ELSE
555 l_row_count := 0;
556
557 l_ent_acc_type := 'ACCOUNT_TYPE';
558
559 OPEN c_na_info;
560 LOOP -- Loop for all natural accounts
561 FETCH c_na_info INTO l_na_number
562 ,l_na_name
563 ,l_na_parent
564 ,l_acc_type_code
565 ,l_na_level
566 ,l_na_sub_flag
567 ,l_na_sub_item
568 ,l_acc_bal_code
569 ;
570 EXIT WHEN c_na_info%NOTFOUND;
571
572 --Only consider accounts with level, and the level should be a natural number and <16
573 IF JA_CN_UTILITY.Check_Account_Level(l_na_level)/*l_na_level is not null AND Is_Natural_Number(l_na_level) = 1 and l_na_level < 16*/
574 THEN
575 /*IF l_na_parent = 'N' THEN --Only export lowest level (not parent) accounts*/
576 l_row_count := l_row_count + 1; --This account will be outputed
577
578 --Get rid of '/' at the last position of subsidiary item
579 l_na_sub_item := substr(l_na_sub_item, 1, length(l_na_sub_item)-1);
580
581 --Get UOM
582 BEGIN
583 SELECT DISTINCT
584 nvl(UOM.UNIT_OF_MEASURE, '') acc_uom
585 INTO l_na_mea
586 FROM GL_LEDGERS LEDGER
587 ,GL_STAT_ACCOUNT_UOM UOM
588 WHERE LEDGER.ledger_id = l_ledger_id --using variable l_sob_id
589 AND UOM.CHART_OF_ACCOUNTS_ID = LEDGER.CHART_OF_ACCOUNTS_ID
590 AND UOM.ACCOUNT_SEGMENT_VALUE = l_na_number --using variable l_na_number
591 ;
592 EXCEPTION
593 WHEN NO_DATA_FOUND THEN
594 l_na_mea := '';
595 END;
596
597 --Get Account type and Balance side
598 IF l_acc_type_code is not null
599 THEN
600 BEGIN
601 SELECT nvl(FLV.meaning,'') acc_type
602 ,FLV1.meaning acc_bal_side
603 INTO l_na_type
604 ,l_na_bal
605 FROM FND_LOOKUP_VALUES FLV
606 ,FND_LOOKUP_VALUES FLV1
607 WHERE --Get meaning of account type
608 FLV.lookup_code = l_acc_type_code --using variable l_acc_type_code
609 AND FLV.lookup_type = l_ent_acc_type --'ACCOUNT_TYPE'
610 and FLV.LANGUAGE = userenv('LANG')
611 --The following 3 conditions should be remained
612 AND ( nvl('', FLV.territory_code) = FLV.territory_code
613 or FLV.territory_code is null )
614 AND FLV.VIEW_APPLICATION_ID = 0
615 AND FLV.SECURITY_GROUP_ID = 0
616 --Get meaning of balance side
617 AND FLV1.lookup_code = DECODE(
618 l_acc_type_code, --using variable l_acc_type_code
619 'A', DECODE(l_acc_bal_code, 'C', 'C', 'D'), --using variable l_acc_bal_code
620 'E', DECODE(l_acc_bal_code, 'C', 'C', 'D'),
621 'L', DECODE(l_acc_bal_code, 'D', 'D', 'C'),
622 'O', DECODE(l_acc_bal_code, 'D', 'D', 'C'),
623 'R', DECODE(l_acc_bal_code, 'D', 'D', 'C')
624 )
625 AND FLV1.lookup_type = 'JA_CN_DEBIT_CREDIT'--'DEBIT_CREDIT'
626 AND FLV1.LANGUAGE = userenv('LANG');
627
628 EXCEPTION
629 WHEN NO_DATA_FOUND THEN
630 l_na_type := '';
631 l_na_bal := '';
632 END;
633 ELSE
634 l_na_type := '';
635 l_na_bal := '';
636 END IF; --l_acc_type_code is null or not
637
638 -- add to XML document
639 Ja_Cn_Utility.Add_Sub_Root_Node('CHART_OF_ACCOUNT',
640 Ja_Cn_Utility.GV_TAG_TYPE_START);
641
642
643 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER',
644 l_na_number);
645 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NAME',
646 l_na_name);
647 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_LEVEL',
648 l_na_level,
649 Ja_Cn_Utility.GV_TYPE_NUMBER);
650 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_TYPE',
651 l_na_type);
652 Ja_Cn_Utility.Add_Child_Node('BALANCE_SIDE',
653 l_na_bal);
654
655 Ja_Cn_Utility.Add_Sub_Root_Node('CHART_OF_ACCOUNT',
656 Ja_Cn_Utility.GV_TAG_TYPE_END);
657 END IF; --Account Level should not null and be a natural number and <16
658 END LOOP;
659 CLOSE c_na_info;
660
661 IF l_row_count = 0 --No account been outputed
662 THEN
663 Ja_Cn_Utility.Print_No_Data_Found_For_Log('CHART_OF_ACCOUNT');
664
665 Ja_Cn_Utility.Add_Sub_Root_Node('CHART_OF_ACCOUNT',
666 Ja_Cn_Utility.GV_TAG_TYPE_START);
667
668
669 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER',
670 NULL);
671 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NAME',
672 NULL);
673 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_LEVEL',
674 NULL,
675 Ja_Cn_Utility.GV_TYPE_NUMBER);
676 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_TYPE',
677 NULL);
678 Ja_Cn_Utility.Add_Child_Node('BALANCE_SIDE',
679 NULL);
680
681 Ja_Cn_Utility.Add_Sub_Root_Node('CHART_OF_ACCOUNT',
682 Ja_Cn_Utility.GV_TAG_TYPE_END);
683 END IF;
684
685 END IF; --The invalid account table has row or not
686
687 --logging for debug
688 IF (ln_proc_level >= ln_dbg_level)
689 THEN
690 FND_LOG.STRING(ln_proc_level,
691 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
692 'Exit procedure');
693 END IF; -- (ln_proc_level>=ln_dbg_level)
694
695 EXCEPTION
696 WHEN JA_CN_INCOMPLETE_DFF_ASSIGN
697 THEN
698 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
699 ,NAME => 'JA_CN_INCOMPLETE_DFF_ASSIGN'
700 );
701 l_msg_incomplete_dff_assign := FND_MESSAGE.Get;
702
703 FND_FILE.put_line(FND_FILE.output, l_msg_incomplete_dff_assign);
704 IF (l_proc_level >= l_dbg_level)
705 THEN
706 FND_LOG.String( l_proc_level
707 ,GV_MODULE_PREFIX||'.'||l_proc_name||'.JA_CN_INCOMPLETE_DFF_ASSIGN '
708 ,l_msg_incomplete_dff_assign);
709 END IF;
710 RAISE;
711
712 WHEN JA_CN_INVALID_ACCOUNT_STRU
713 THEN
714 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
715 ,NAME => 'JA_CN_INVALID_ACCOUNT_STRU'
716 );
717 FND_MESSAGE.SET_TOKEN('REQUEST_ID', TO_CHAR(l_na_req_id));
718 l_msg_invalid_account_stru := FND_MESSAGE.Get;
719
720 FND_FILE.put_line(FND_FILE.output, l_msg_invalid_account_stru);
721 IF (l_proc_level >= l_dbg_level)
722 THEN
723 FND_LOG.String( l_proc_level
724 ,GV_MODULE_PREFIX||'.'||l_proc_name||'.JA_CN_INVALID_ACCOUNT_STRU '
725 ,l_msg_invalid_account_stru);
726 END IF;
727 RAISE;
728
729 WHEN OTHERS
730 THEN
731 IF (l_proc_level >= l_dbg_level)
732 THEN
733 FND_LOG.String( l_proc_level
734 ,GV_MODULE_PREFIX||'.'||l_proc_name||'.Other_Exception '
735 ,SQLCODE||':'||SQLERRM);
736 END IF; --(l_proc_level >= l_dbg_level)
737 RAISE;
738 END Add_COA;
739
740 END JA_CN_COA_EXPORT_PKG;
741
742