[Home] [Help]
PACKAGE BODY: APPS.JA_CN_AB_EXP_PKG
Source
1 PACKAGE BODY JA_CN_AB_EXP_PKG AS
2 --$Header: JACNABEB.pls 120.0.12000000.1 2007/08/13 14:09:04 qzhao noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================
8 --| FILENAME
9 --| JACNABES.pls
10 --|
11 --| DESCRIPTION
12 --|
13 --| This package is to provide share procedures for CNAO programs
14 --|
15 --| PROCEDURE LIST
16 --|
17 --| PROCEDURE run_export
18 --|
19 --|
20 --| HISTORY
21 --| 01-May-2006 shujuan Yan Created
22 --+======================================================================*/
23
24 --==========================================================================
25 -- PROCEDURE NAME:
26 --
27 -- run_export Public
28 --
29 -- DESCRIPTION:
30 --
31 -- This procedure is used to export the account balances.
32 --
33 --
34 -- PARAMETERS:
35 -- Out: errbuf Mandatory parameter for PL/SQL concurrent programs
36 -- Out: retcode Mandatory parameter for PL/SQL concurrent programs
37 -- In p_coa_id Chart of Accounts Id
38 -- In p_ledger_id Ledger Id
39 -- In: p_legal_entity Legal entity ID
40 -- In: p_start_period start period name
41 -- In: P_end_period end period name
42 -- In: P_XML_TEMPLATE_LANGUAGE template language of exception report
43 -- In: P_XML_TEMPLATE_TERRITORY template territory of exception report
44 -- In: P_XML_OUTPUT_FORMAT output format of exception report
45 --
46 -- DESIGN REFERENCES:
47 --
48 --
49 -- CHANGE HISTORY:
50 --
51 -- 07-May-2007 Shujuan Yan Created
52 --
53 --===========================================================================
54 PROCEDURE Run_Export(errbuf OUT NOCOPY VARCHAR2
55 ,retcode OUT NOCOPY VARCHAR2
56 ,p_coa_id IN NUMBER
57 ,p_ledger_id IN NUMBER
58 ,p_legal_entity IN NUMBER
59 ,p_start_period IN VARCHAR2
60 ,p_end_period IN VARCHAR2
61 ,P_XML_TEMPLATE_LANGUAGE IN VARCHAR2
62 ,P_XML_TEMPLATE_TERRITORY IN VARCHAR2
63 ,P_XML_OUTPUT_FORMAT IN VARCHAR2) AS
64
65 --variables start here
66 l_module_name CONSTANT VARCHAR2(300) := 'JA_CN_AB_EXP_PKG.Run_Export';
67 l_runtime_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
68 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
69 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
70 l_exception_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
71 l_message VARCHAR2(300);
72 l_flag VARCHAR2(15);
73 l_number_a NUMBER;
74 l_number_b NUMBER;
75
76 -- l_set_of_books_id ja_cn_system_parameters_all.legal_entity_id%TYPE;
77 l_functional_currency fnd_currencies_vl.NAME%TYPE;
78 l_functional_currency_code fnd_currencies_vl.CURRENCY_CODE%TYPE;
79 --l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
80 l_flex_value_set_id fnd_id_flex_segments.flex_value_set_id%TYPE;
81 l_ja_cn_dff_assignments_row ja_cn_dff_assignments%ROWTYPE;
82 l_context_code ja_cn_dff_assignments.context_code%TYPE;
83 l_attribute_column4cost_center ja_cn_dff_assignments.attribute_column%TYPE;
84 l_attribute_column4third_party ja_cn_dff_assignments.attribute_column%TYPE;
85 l_attribute_column4personnel ja_cn_dff_assignments.attribute_column%TYPE;
86 l_attribute_column4project ja_cn_dff_assignments.attribute_column%TYPE;
87 l_attribute_column4is_foreign ja_cn_dff_assignments.attribute_column%TYPE;
88 l_attribute_column4balanceside ja_cn_dff_assignments.attribute_column%TYPE;
89 l_attribute_column4account_lev ja_cn_dff_assignments.attribute_column%TYPE;
90 l_account_segment ja_cn_journal_lines.account_segment%TYPE;
91 l_ja_cn_subsidiary_gbl_tmp_row ja_cn_subsidiary_gt%ROWTYPE;
92 l_account_balances_gbl_tmp_row ja_cn_account_balances_gt%ROWTYPE;
93 l_sql_stmt VARCHAR2(30000) := '';
94
95 l_sql_stmt4insert_j_line VARCHAR2(30000) := 'INSERT INTO ja_cn_account_balances_gt
96 (account_segment,
97 currency_code,
98 func_begin_balance,
99 orig_begin_balance,
100 func_period_net_dr,
101 func_period_net_cr,
102 orig_period_net_dr,
103 orig_period_net_cr,
104 func_end_balance,
105 orig_end_balance,
106 period_mon,
107 PERIOD_NAME,
108 START_DATE,
109 has_third_party,
110 has_cost_center,
111 has_personnel,
112 has_project,
113 account_type,
114 is_foreign,
115 balance_side,
116 account_level
117 @COLUMN_CLAUSE
118 )
119 (SELECT ' ||
120 prefix_a ||
121 '.account_segment, ' ||
122 prefix_b || '.name, ' ||
123 prefix_a ||
124 '.func_begin_balance,
125 ' ||
126 prefix_a ||
127 '.orig_begin_balance,
128 ' ||
129 prefix_a ||
130 '.func_period_net_dr,
131 ' ||
132 prefix_a ||
133 '.func_period_net_cr,
134 ' ||
135 prefix_a ||
136 '.orig_period_net_dr,
137 ' ||
138 prefix_a ||
139 '.orig_period_net_cr,
140 ' ||
141 prefix_a ||
142 '.func_end_balance,
143 ' ||
144 prefix_a ||
145 '.orig_end_balance,
146 ' ||
147 prefix_a ||
148 '.period_mon,
149 ' ||
150 prefix_a ||
151 '.PERIOD_NAME,:1,
152 s.has_third_party,
153 s.has_cost_center,
154 s.has_personnel,
155 s.has_project,
156 ' ||
157 prefix_a ||
158 '.account_type,s.is_foreign,s.balance_side,s.account_level
159 @PREFIX_COLUMN_CLAUSE ' || '
160 FROM (SELECT account_segment,
161 @CURRENCY_CLAUSE1
162 @SUM_CLAUSE
163 period_mon,
164 account_type,
165 PERIOD_NAME
166 @COLUMN_CLAUSE
167 FROM ja_cn_account_balances_v
168 WHERE period_name = :2
169 AND account_segment = :3
170 AND ledger_id = :4
171 AND company_segment IN
172 (SELECT bal_seg_value
173 FROM ja_cn_ledger_le_bsv_gt
174 WHERE legal_entity_id = :5)
175 GROUP BY account_segment,@CURRENCY_CLAUSE2 period_mon,account_type,PERIOD_NAME @COLUMN_CLAUSE) ' ||
176 prefix_a ||
177 ' LEFT JOIN ja_cn_subsidiary_gt s ON ' ||
178 prefix_a ||
179 '.account_segment =s.account_segment_value left join fnd_currencies_vl ' ||
180 prefix_b || ' ON ' || prefix_a ||
181 '.currency_code=' || prefix_b ||
182 '.currency_code)';
183
184 l_column_clauses ja_cn_je_exp_pkg.assoc_array_varchar1000_type;
185 l_prefix_column_clauses ja_cn_je_exp_pkg.assoc_array_varchar1000_type;
186 l_column_clause VARCHAR2(500);
187
188 l_prefix_column_clause VARCHAR2(500);
189
190 l_na_curr_req_id NUMBER;
191 l_na_req_id NUMBER;
192
193 l_na_req_phase fnd_lookup_values.meaning%TYPE;
194 l_na_req_status fnd_lookup_values.meaning%TYPE;
195 l_na_req_dev_phase VARCHAR2(30);
196 l_na_req_dev_status VARCHAR2(30);
197 l_na_req_message VARCHAR2(100);
198
199 l_xml_layout BOOLEAN;
200 l_template_language VARCHAR2(10) := P_XML_TEMPLATE_LANGUAGE;
201 l_template_territory VARCHAR2(10) := P_XML_TEMPLATE_TERRITORY;
202 l_output_format VARCHAR2(10) := P_XML_OUTPUT_FORMAT;
203
204 l_start_date DATE;
205 l_end_date DATE;
206 l_current_period GL_PERIOD_STATUSES.Period_Name%TYPE;
207 l_current_start_date DATE;
208 --exceptions start here
209 --sob_unfetchable EXCEPTION;
210
211 --cursors start here
212 CURSOR c_in_ja_cn_dff_assignments IS
213 SELECT *
214 FROM ja_cn_dff_assignments
215 WHERE chart_of_accounts_id = p_coa_id
216 AND (dff_title_code = 'SACC'
217 OR dff_title_code = 'SATP'
218 OR dff_title_code = 'SAEE'
219 OR dff_title_code = 'SAPA'
220 OR dff_title_code = 'FCRA'
221 OR dff_title_code = 'ACBS'
222 OR dff_title_code = 'ACLE');
223
224 CURSOR c_in_ja_cn_account_balances IS
225 SELECT DISTINCT account_segment
226 FROM ja_cn_account_balances_v
227 WHERE company_segment IN
228 (SELECT bal_seg_value--segment_value
229 FROM ja_cn_ledger_le_bsv_gt--ja_cn_legal_companies_all
230 WHERE legal_entity_id = p_legal_entity
231 AND chart_of_accounts_id = p_coa_id)
232 AND period_name IN
233 (SELECT period_name
234 FROM GL_PERIOD_STATUSES
235 WHERE ledger_id = p_ledger_id--set_of_books_id = l_set_of_books_id
236 AND application_id = 101
237 AND ((start_date BETWEEN l_start_date AND l_end_date) AND
238 (end_date BETWEEN l_start_date AND l_end_date)))
239 AND ledger_id = p_ledger_id;--set_of_books_id = l_set_of_books_id;
240
241 CURSOR c_in_ja_cn_subsidiary_gbl_tmp IS
242 SELECT * FROM ja_cn_subsidiary_gt;
243
244 CURSOR c_in_account_balances_gbl_tmp IS
245 SELECT *
246 FROM ja_cn_account_balances_gt
247 ORDER BY start_date
248 ,ACCOUNT_SEGMENT
249 ,PROJECT_NUMBER
250 ,THIRD_PARTY_NUMBER
251 ,COST_CENTER
252 ,PERSONNEL_NUMBER;
253
254 CURSOR c_in_closed_periods IS
255 SELECT period_name
256 FROM GL_PERIOD_STATUSES
260 (end_date BETWEEN l_start_date AND l_end_date))
257 WHERE ledger_id = p_ledger_id--set_of_books_id = l_set_of_books_id
258 AND application_id = 101
259 AND ((start_date BETWEEN l_start_date AND l_end_date) AND
261 ORDER BY start_date;
262
263 BEGIN
264 l_na_curr_req_id := FND_GLOBAL.CONC_REQUEST_ID;
265 --log the parameters
266 IF (l_procedure_level >= l_runtime_level) THEN
267
268 FND_LOG.STRING(l_procedure_level,
269 l_module_name,
270 'Start to run ' || l_module_name ||
271 'with parameter: p_coa_id=' ||
272 nvl(to_char(p_coa_id), 'null') || ' p_ledger_id=' ||
273 nvl(to_char(p_ledger_id), 'null') || ' p_legal_entity=' ||
274 nvl(to_char(p_legal_entity), 'null') || ' p_start_period=' ||
275 nvl(to_char(p_start_period), 'null') || ' p_end_period=' ||
276 nvl(to_char(p_end_period), 'null'));
277
278 END IF; --l_procedure_level >= l_runtime_level
279
280 --call JA_CN_UTILITY.Check_Profile, if it doesn't return true, exit
281 IF JA_CN_UTILITY.Check_Profile() <> TRUE THEN
282 IF (l_exception_level >= l_runtime_level) THEN
283 FND_LOG.STRING(l_exception_level,
284 l_module_name,
285 'Check profile failed!');
286 END IF; --l_exception_level >= l_runtime_level
287 retcode := 1;
288 RETURN;
289 END IF; --JA_CN_UTILITY.Check_Profile() != TRUE
290 l_flag := Ja_Cn_Utility.Populate_Ledger_Le_Bsv_Gt(p_Ledger_Id,p_Legal_Entity);
291 --call JA_CN_UTILITY.Check_Accounting_Period_Range, if it doesn't return true, exit
292 IF ja_cn_utility.Check_Accounting_Period_Range(p_start_period,
293 p_end_period,
294 p_legal_entity,
295 p_ledger_id) <> TRUE THEN
296 FND_MESSAGE.set_name('JA', 'JA_CN_PERIOD_OPEN');
297 FND_MESSAGE.SET_TOKEN('PERIOD_FROM', p_start_period, TRUE);
298 FND_MESSAGE.SET_TOKEN('PERIOD_TO', p_end_period, TRUE);
299 l_message := FND_MESSAGE.get();
300 FND_FILE.put_line(FND_FILE.LOG, l_message);
301
302 IF (l_exception_level >= l_runtime_level) THEN
303 FND_LOG.STRING(l_exception_level,
304 l_module_name,
305 'Check account period range failed!');
306 END IF;
307 retcode := 1;
308 errbuf := l_message;
309 RETURN;
310 END IF;
311
312 /* --fetch set of books id and chart of account id
313 JA_CN_UTILITY.Get_SOB_And_COA(p_legal_entity_id => p_legal_entity,
314 x_sob_id => l_set_of_books_id,
315 x_coa_id => l_chart_of_accounts_id,
316 x_flag => l_flag);
317
318 IF l_flag = -1 THEN
319 IF (l_exception_level >= l_runtime_level) THEN
320 FND_LOG.STRING(l_exception_level,
321 l_module_name,
322 'Get SOB or COA failed!');
323 END IF;
324 retcode := 1;
325 RETURN;
326 END IF;
327
328 --log the SOB
329 IF (l_statement_level >= l_runtime_level) THEN
330 FND_LOG.STRING(l_statement_level,
331 l_module_name,
332 'Fetched: l_set_of_books_id=' ||
333 nvl(to_char(l_set_of_books_id), 'null'));
334 END IF;
335
336 --log chat of account
337 IF (l_statement_level >= l_runtime_level) THEN
338 FND_LOG.STRING(l_statement_level,
339 l_module_name,
340 'Fetched: l_chart_of_accounts_id=' ||
341 nvl(to_char(l_chart_of_accounts_id), 'null'));
342 END IF;
343 */
344 --fetch start data and end date
345 SELECT start_date
346 INTO l_start_date
347 FROM GL_PERIOD_STATUSES
348 WHERE ledger_id = p_ledger_id--set_of_books_id = l_set_of_books_id
349 AND application_id = 101
350 AND period_name = p_start_period;
351
352 SELECT end_date
353 INTO l_end_date
354 FROM GL_PERIOD_STATUSES
355 WHERE ledger_id = p_ledger_id--set_of_books_id = l_set_of_books_id
356 AND application_id = 101
357 AND period_name = p_end_period;
358
359 --log start data and end date
360 IF (l_statement_level >= l_runtime_level) THEN
361 FND_LOG.STRING(l_statement_level,
362 l_module_name,
363 'Fetched: start date=' ||
364 nvl(to_char(l_start_date), 'null') || ' end date=' ||
365 nvl(to_char(l_end_date), 'null'));
366 END IF;
367
368 --fetch flex_value_set_id
369 SELECT s.flex_value_set_id
370 INTO l_flex_value_set_id
371 FROM fnd_id_flex_segments s
372 WHERE s.application_id = 101
373 AND s.id_flex_num = p_coa_id--l_chart_of_accounts_id
374 AND s.id_flex_code = 'GL#'
375 AND s.application_column_name =
376 (SELECT application_column_name
377 FROM fnd_segment_attribute_values
378 WHERE application_id = 101
379 AND segment_attribute_type = 'GL_ACCOUNT'
380 AND attribute_value = 'Y'
381 AND id_flex_num = p_coa_id
382 AND id_flex_code = 'GL#');--l_chart_of_accounts_id);
383
384 --log
385 IF (l_statement_level >= l_runtime_level) THEN
386 FND_LOG.STRING(l_statement_level,
387 l_module_name,
388 'Fetched: l_flex_value_set_id=' ||
392 --fetch context code, attribute column for cost center, third party, personnel and project
389 nvl(to_char(l_flex_value_set_id), 'null'));
390 END IF; --l_statement_level >= l_runtime_level
391
393 OPEN c_in_ja_cn_dff_assignments;
394 LOOP
395 FETCH c_in_ja_cn_dff_assignments
396 INTO l_ja_cn_dff_assignments_row;
397 EXIT WHEN c_in_ja_cn_dff_assignments%NOTFOUND;
398 IF l_ja_cn_dff_assignments_row.context_code IS NOT NULL THEN
399 l_context_code := l_ja_cn_dff_assignments_row.context_code;
400 END IF; --l_ja_cn_dff_assignments_row.context_code IS NOT NULL
401 CASE l_ja_cn_dff_assignments_row.dff_title_code
402 WHEN 'SACC' THEN
403 l_attribute_column4cost_center := l_ja_cn_dff_assignments_row.attribute_column;
404 WHEN 'SATP' THEN
405 l_attribute_column4third_party := l_ja_cn_dff_assignments_row.attribute_column;
406 WHEN 'SAEE' THEN
407 l_attribute_column4personnel := l_ja_cn_dff_assignments_row.attribute_column;
408 WHEN 'SAPA' THEN
409 l_attribute_column4project := l_ja_cn_dff_assignments_row.attribute_column;
410 WHEN 'FCRA' THEN
411 l_attribute_column4is_foreign := l_ja_cn_dff_assignments_row.attribute_column;
412 WHEN 'ACBS' THEN
413 l_attribute_column4balanceside := l_ja_cn_dff_assignments_row.attribute_column;
414 WHEN 'ACLE' THEN
415 l_attribute_column4account_lev := l_ja_cn_dff_assignments_row.attribute_column;
416 END CASE; END LOOP;
417 CLOSE c_in_ja_cn_dff_assignments;
418
419 --log
420 IF (l_statement_level >= l_runtime_level) THEN
421 FND_LOG.STRING(l_statement_level,
422 l_module_name,
423 'Fetched: l_context_code=' ||
424 nvl(to_char(l_context_code), 'null'));
425 FND_LOG.STRING(l_statement_level,
426 l_module_name,
427 'Fetched: l_attribute_column4cost_center=' ||
428 nvl(to_char(l_attribute_column4cost_center), 'null'));
429 FND_LOG.STRING(l_statement_level,
430 l_module_name,
431 'Fetched: l_attribute_column4third_party=' ||
432 nvl(to_char(l_attribute_column4third_party), 'null'));
433
434 FND_LOG.STRING(l_statement_level,
435 l_module_name,
436 'Fetched: l_attribute_column4personnel=' ||
437 nvl(to_char(l_attribute_column4personnel), 'null'));
438
439 FND_LOG.STRING(l_statement_level,
440 l_module_name,
441 'Fetched: l_attribute_column4project=' ||
442 nvl(to_char(l_attribute_column4project), 'null'));
443
444 FND_LOG.STRING(l_statement_level,
445 l_module_name,
446 'Fetched: l_attribute_column4is_foreign=' ||
447 nvl(to_char(l_attribute_column4is_foreign), 'null'));
448
449 FND_LOG.STRING(l_statement_level,
450 l_module_name,
451 'Fetched: l_attribute_column4balanceside=' ||
452 nvl(to_char(l_attribute_column4balanceside), 'null'));
453
454 FND_LOG.STRING(l_statement_level,
455 l_module_name,
456 'Fetched: l_attribute_column4account_lev=' ||
457 nvl(to_char(l_attribute_column4account_lev), 'null'));
458
459 END IF; --l_statement_level >= l_runtime_level
460
461 --fetch the account subsidiary info and save to temp table
462 OPEN c_in_ja_cn_account_balances;
463
464 --log
465 IF (l_statement_level >= l_runtime_level) THEN
466 FND_LOG.STRING(l_statement_level,
467 l_module_name,
468 'Fetched account segments start below:');
469
470 END IF; --l_statement_level >= l_runtime_level
471 l_sql_stmt := 'insert into JA_CN_SUBSIDIARY_GT
472 (
473 ACCOUNT_SEGMENT_VALUE,
474 HAS_THIRD_PARTY,
475 HAS_COST_CENTER,
476 HAS_PERSONNEL,
477 HAS_PROJECT,
478 IS_FOREIGN,
479 BALANCE_SIDE,
480 account_level
481 )
482 select flex_value,' ||
483 nvl(to_char(l_attribute_column4third_party), 'null') || ',' ||
484 nvl(to_char(l_attribute_column4cost_center), 'null') || ',' ||
485 nvl(to_char(l_attribute_column4personnel), 'null') || ',' ||
486 nvl(to_char(l_attribute_column4project), 'null') || ',' ||
487 nvl(to_char(l_attribute_column4is_foreign), 'null') || ',' ||
488 nvl(to_char(l_attribute_column4balanceside), 'null') || ',' ||
489 nvl(to_char(l_attribute_column4account_lev), 'null') || '
490 from FND_FLEX_VALUES
491 where
492 flex_value_set_id=:1 and
493 flex_value=:2';
494 --log
495 /*IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
496 fnd_log.STRING(fnd_log.level_statement, l_module_name, l_sql_stmt);
497
498 END IF;*/
499
500 LOOP
501 FETCH c_in_ja_cn_account_balances
502 INTO l_account_segment;
503 EXIT WHEN c_in_ja_cn_account_balances%NOTFOUND;
504 --log
505 IF (l_statement_level >= l_runtime_level) THEN
506 FND_LOG.STRING(l_statement_level, l_module_name, l_account_segment);
507 END IF; --l_statement_level >= l_runtime_level
508 EXECUTE IMMEDIATE l_sql_stmt
509 USING l_flex_value_set_id, l_account_segment;
510 END LOOP;
511
515 l_message := FND_MESSAGE.get();
512 --no data found with the requested parameters
513 IF (c_in_ja_cn_account_balances%ROWCOUNT = 0) THEN
514 FND_MESSAGE.set_name('JA', 'JA_CN_NO_DATA_FOUND');
516 FND_FILE.put_line(FND_FILE.output, l_message);
517 IF (l_exception_level >= l_runtime_level) THEN
518 FND_LOG.STRING(l_exception_level, l_module_name, l_message);
519 END IF;
520 retcode := 1;
521 errbuf := l_message;
522 RETURN;
523 END IF; --c_in_ja_cn_account_balances%ROWCOUNT = 0
524 CLOSE c_in_ja_cn_account_balances;
525
526 --fetch functional currency name
527 SELECT fnd_currencies_vl.NAME
528 INTO l_functional_currency
529 FROM fnd_currencies_vl
530 WHERE currency_code =
531 (SELECT currency_code
532 FROM gl_ledgers--gl_sets_of_books
533 WHERE ledger_id = p_ledger_id);--set_of_books_id = l_set_of_books_id);
534
535 --fetch functional currency code
536 SELECT currency_code
537 INTO l_functional_currency_code
538 FROM gl_ledgers--gl_sets_of_books
539 WHERE ledger_id = p_ledger_id;--set_of_books_id = l_set_of_books_id;
540
541 --log currency name and currency code
542 IF (l_statement_level >= l_runtime_level) THEN
543 FND_LOG.STRING(l_statement_level,
544 l_module_name,
545 'Fetched: l_functional_currency=' ||
546 nvl(to_char(l_functional_currency), 'null'));
547 END IF;
548 --FND_FILE.PUT_LINE(FND_FILE.LOG, l_functional_currency);
549
550 --combine the journal lines and save them to the JA_CN_JOURNAL_LINES_GBL_TMP
551 OPEN c_in_closed_periods;
552 LOOP
553 FETCH c_in_closed_periods
554 INTO l_current_period;
555 EXIT WHEN c_in_closed_periods%NOTFOUND;
556
557 --fetch start date of current period
558 SELECT start_date
559 INTO l_current_start_date
560 FROM GL_PERIOD_STATUSES
561 WHERE ledger_id = p_ledger_id--set_of_books_id = l_set_of_books_id
562 AND application_id = 101
563 AND period_name = l_current_period;
564
565 --log current period and it's start date
566 IF (l_statement_level >= l_runtime_level) THEN
567 FND_LOG.STRING(l_statement_level,
568 l_module_name,
569 'about to generate journal lines info for period:' ||
570 l_current_period || ' start date:' ||
571 l_current_start_date);
572
573 END IF;
574
575 --FND_FILE.PUT_LINE(FND_FILE.LOG, l_current_start_date);
576
577 OPEN c_in_ja_cn_subsidiary_gbl_tmp;
578 LOOP
579 FETCH c_in_ja_cn_subsidiary_gbl_tmp
580 INTO l_ja_cn_subsidiary_gbl_tmp_row;
581 EXIT WHEN c_in_ja_cn_subsidiary_gbl_tmp%NOTFOUND;
582 --log
583 IF (l_statement_level >= l_runtime_level) THEN
584 FND_LOG.STRING(l_statement_level,
585 l_module_name,
586 'about to generate group clause for account:' ||
587 l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value);
588
589 END IF; --l_statement_level >= l_runtime_level
590
591 JA_CN_JE_EXP_PKG.Gen_Clauses(l_column_clauses,
592 l_prefix_column_clauses,
593 l_ja_cn_subsidiary_gbl_tmp_row.has_cost_center,
594 l_ja_cn_subsidiary_gbl_tmp_row.has_third_party,
595 l_ja_cn_subsidiary_gbl_tmp_row.has_personnel,
596 l_ja_cn_subsidiary_gbl_tmp_row.has_project,
597 l_column_clause,
598 l_prefix_column_clause);
599
600 l_sql_stmt := REPLACE(l_sql_stmt4insert_j_line,
601 '@COLUMN_CLAUSE',
602 l_column_clause);
603 l_sql_stmt := REPLACE(l_sql_stmt,
604 '@PREFIX_COLUMN_CLAUSE',
605 l_prefix_column_clause);
606
607 --foreign currency disabled, the account is in functional currency, sum all the functional balances,
608 --set foreign balances to zero, set functional currency to balance currency
609 IF l_ja_cn_subsidiary_gbl_tmp_row.is_foreign IS NULL OR
610 l_ja_cn_subsidiary_gbl_tmp_row.is_foreign =
611 l_functional_currency_code THEN
612 l_sql_stmt := REPLACE(l_sql_stmt,
613 '@CURRENCY_CLAUSE1',
614 '''' || l_functional_currency_code ||
615 ''' currency_code,');
616 l_sql_stmt := REPLACE(l_sql_stmt, '@CURRENCY_CLAUSE2', ' ');
617 l_sql_stmt := REPLACE(l_sql_stmt,
618 '@SUM_CLAUSE',
619 'SUM(FUNC_BEGIN_BALANCE) FUNC_BEGIN_BALANCE,
620 0 ORIG_BEGIN_BALANCE,
621 SUM(FUNC_PERIOD_NET_DR) FUNC_PERIOD_NET_DR,
622 SUM(FUNC_PERIOD_NET_CR) FUNC_PERIOD_NET_CR,
623 0 ORIG_PERIOD_NET_DR,
624 0 ORIG_PERIOD_NET_CR,
625 SUM(FUNC_END_BALANCE) FUNC_END_BALANCE,
626 0 ORIG_END_BALANCE,');
627 --fnd_file.PUT_LINE(fnd_file.LOG,l_ja_cn_subsidiary_gbl_tmp_row.ACCOUNT_SEGMENT_VALUE);
628 --foreign currency enabled
629 ELSE
630 l_sql_stmt := REPLACE(l_sql_stmt,
631 '@CURRENCY_CLAUSE1',
632 'currency_code,');
633 l_sql_stmt := REPLACE(l_sql_stmt,
637 '@SUM_CLAUSE',
634 '@CURRENCY_CLAUSE2',
635 'currency_code,');
636 l_sql_stmt := REPLACE(l_sql_stmt,
638 'SUM(func_begin_balance) func_begin_balance,
639 SUM(orig_begin_balance) orig_begin_balance,
640 SUM(func_period_net_dr) func_period_net_dr,
641 SUM(orig_period_net_dr) orig_period_net_dr,
642 SUM(func_period_net_cr) func_period_net_cr,
643 SUM(orig_period_net_cr) orig_period_net_cr,
644 SUM(func_end_balance) func_end_balance,
645 SUM(orig_end_balance) orig_end_balance,');
646 END IF;
647 /*IF l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value = '6001' THEN
648 FND_FILE.PUT_LINE(FND_FILE.LOG, l_sql_stmt);
649 END IF;*/
650
651 EXECUTE IMMEDIATE l_sql_stmt
652 USING l_current_start_date, l_current_period, l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value, p_ledger_id, p_legal_entity;
653 END LOOP;
654 CLOSE c_in_ja_cn_subsidiary_gbl_tmp;
655 END LOOP;
656
657 CLOSE c_in_closed_periods;
658
659 --check error accounts, for those accounts which are foreign currency A enabled but have balances which are
660 --not A and the begin and end balances are not zero.
661 /*OPEN c_in_account_balances_gbl_tmp;
662 LOOP
663 FETCH c_in_account_balances_gbl_tmp
664 INTO l_account_balances_gbl_tmp_row;
665 EXIT WHEN c_in_account_balances_gbl_tmp%NOTFOUND;
666 --is foreign account enabled
667 --fnd_file.PUT_LINE(fnd_file.LOG,'is f: '||l_account_balances_gbl_tmp_row.is_foreign||' f c code: '||l_functional_currency_code);
668 IF l_account_balances_gbl_tmp_row.is_foreign IS NOT NULL AND
669 l_account_balances_gbl_tmp_row.is_foreign <>
670 l_functional_currency_code THEN
671 SELECT COUNT(*)
672 INTO l_number_a
673 FROM ja_cn_account_balances_gt
674 WHERE account_segment = l_account_balances_gbl_tmp_row.account_segment
675 AND nvl(COST_CENTER, 'NULL') =
676 nvl(l_account_balances_gbl_tmp_row.COST_CENTER, 'NULL')
677 AND nvl(THIRD_PARTY_NUMBER, 'NULL') =
678 nvl(l_account_balances_gbl_tmp_row.THIRD_PARTY_NUMBER, 'NULL')
679 AND nvl(PERSONNEL_NUMBER, 'NULL') =
680 nvl(l_account_balances_gbl_tmp_row.PERSONNEL_NUMBER, 'NULL')
681 AND nvl(PROJECT_NUMBER, 'NULL') =
682 nvl(l_account_balances_gbl_tmp_row.PROJECT_NUMBER, 'NULL')
683 AND currency_code <>
684 (SELECT fnd_currencies_vl.NAME
685 FROM fnd_currencies_vl
686 WHERE currency_code = l_account_balances_gbl_tmp_row.is_foreign)
687 AND FUNC_BEGIN_BALANCE <> 0
688 AND FUNC_END_BALANCE <> 0;
689 --fnd_file.PUT_LINE(fnd_file.LOG,l_number_a);
690 IF l_number_a >= 1 THEN
691 --error balance
692 INSERT INTO JA_CN_ERROR_ACCOUNTS
693 (request_id
694 ,ACCOUNT_SEGMENT
695 ,CURRENCY_CODE
696 ,FUNC_BEGIN_BALANCE
697 ,ORIG_BEGIN_BALANCE
698 ,FUNC_PERIOD_NET_DR
699 ,FUNC_PERIOD_NET_CR
700 ,ORIG_PERIOD_NET_DR
701 ,ORIG_PERIOD_NET_CR
702 ,FUNC_END_BALANCE
703 ,ORIG_END_BALANCE
704 ,PERIOD_MON
705 ,COST_CENTER
706 ,THIRD_PARTY_NUMBER
707 ,PERSONNEL_NUMBER
708 ,PROJECT_NUMBER
709 ,HAS_THIRD_PARTY
710 ,HAS_COST_CENTER
711 ,HAS_PERSONNEL
712 ,HAS_PROJECT
713 ,ACCOUNT_TYPE
714 ,IS_FOREIGN
715 ,BALANCE_SIDE
716 ,ACCOUNT_LEVEL
717 ,PERIOD_NAME
718 ,START_DATE)
719 SELECT l_na_curr_req_id
720 ,l_account_balances_gbl_tmp_row.account_segment
721 ,CURRENCY_CODE
722 ,FUNC_BEGIN_BALANCE
723 ,ORIG_BEGIN_BALANCE
724 ,FUNC_PERIOD_NET_DR
725 ,FUNC_PERIOD_NET_CR
726 ,ORIG_PERIOD_NET_DR
727 ,ORIG_PERIOD_NET_CR
728 ,FUNC_END_BALANCE
729 ,ORIG_END_BALANCE
730 ,PERIOD_MON
731 ,COST_CENTER
732 ,THIRD_PARTY_NUMBER
733 ,PERSONNEL_NUMBER
734 ,PROJECT_NUMBER
735 ,HAS_THIRD_PARTY
736 ,HAS_COST_CENTER
737 ,HAS_PERSONNEL
738 ,HAS_PROJECT
739 ,ACCOUNT_TYPE
740 ,IS_FOREIGN
741 ,BALANCE_SIDE
742 ,ACCOUNT_LEVEL
743 ,period_name
744 ,start_date
745 FROM ja_cn_account_balances_gt
746 WHERE account_segment =
747 l_account_balances_gbl_tmp_row.account_segment
748 AND nvl(COST_CENTER, 'NULL') =
749 nvl(l_account_balances_gbl_tmp_row.COST_CENTER, 'NULL')
750 AND nvl(THIRD_PARTY_NUMBER, 'NULL') =
751 nvl(l_account_balances_gbl_tmp_row.THIRD_PARTY_NUMBER,
752 'NULL')
753 AND nvl(PERSONNEL_NUMBER, 'NULL') =
754 nvl(l_account_balances_gbl_tmp_row.PERSONNEL_NUMBER, 'NULL')
755 AND nvl(PROJECT_NUMBER, 'NULL') =
756 nvl(l_account_balances_gbl_tmp_row.PROJECT_NUMBER, 'NULL')
757 AND currency_code <>
761 l_account_balances_gbl_tmp_row.is_foreign)
758 (SELECT fnd_currencies_vl.NAME
759 FROM fnd_currencies_vl
760 WHERE currency_code =
762 AND FUNC_BEGIN_BALANCE <> 0
763 AND FUNC_END_BALANCE <> 0;
764
765 END IF;
766 END IF;
767 END LOOP;
768 CLOSE c_in_account_balances_gbl_tmp;*/
769
770 OPEN c_in_ja_cn_subsidiary_gbl_tmp;
771 LOOP
772 FETCH c_in_ja_cn_subsidiary_gbl_tmp
773 INTO l_ja_cn_subsidiary_gbl_tmp_row;
774 EXIT WHEN c_in_ja_cn_subsidiary_gbl_tmp%NOTFOUND;
775 --is foreign account enabled
776 --fnd_file.PUT_LINE(fnd_file.LOG,'is f: '||l_account_balances_gbl_tmp_row.is_foreign||' f c code: '||l_functional_currency_code);
777 IF l_ja_cn_subsidiary_gbl_tmp_row.is_foreign IS NOT NULL AND
778 l_ja_cn_subsidiary_gbl_tmp_row.is_foreign <>
779 l_functional_currency_code THEN
780 INSERT INTO JA_CN_ERROR_ACCOUNTS
781 (request_id
782 ,ACCOUNT_SEGMENT
783 ,CURRENCY_CODE
784 ,FUNC_BEGIN_BALANCE
785 ,ORIG_BEGIN_BALANCE
786 ,FUNC_PERIOD_NET_DR
787 ,FUNC_PERIOD_NET_CR
788 ,ORIG_PERIOD_NET_DR
789 ,ORIG_PERIOD_NET_CR
790 ,FUNC_END_BALANCE
791 ,ORIG_END_BALANCE
792 ,PERIOD_MON
793 ,COST_CENTER
794 ,THIRD_PARTY_NUMBER
795 ,PERSONNEL_NUMBER
796 ,PROJECT_NUMBER
797 ,HAS_THIRD_PARTY
798 ,HAS_COST_CENTER
799 ,HAS_PERSONNEL
800 ,HAS_PROJECT
801 ,ACCOUNT_TYPE
802 ,IS_FOREIGN
803 ,BALANCE_SIDE
804 ,ACCOUNT_LEVEL
805 ,PERIOD_NAME
806 ,START_DATE)
807 SELECT l_na_curr_req_id
808 ,l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value
809 ,CURRENCY_CODE
810 ,FUNC_BEGIN_BALANCE
811 ,ORIG_BEGIN_BALANCE
812 ,FUNC_PERIOD_NET_DR
813 ,FUNC_PERIOD_NET_CR
814 ,ORIG_PERIOD_NET_DR
815 ,ORIG_PERIOD_NET_CR
816 ,FUNC_END_BALANCE
817 ,ORIG_END_BALANCE
818 ,PERIOD_MON
819 ,COST_CENTER
820 ,THIRD_PARTY_NUMBER
821 ,PERSONNEL_NUMBER
822 ,PROJECT_NUMBER
823 ,HAS_THIRD_PARTY
824 ,HAS_COST_CENTER
825 ,HAS_PERSONNEL
826 ,HAS_PROJECT
827 ,ACCOUNT_TYPE
828 ,IS_FOREIGN
829 ,BALANCE_SIDE
830 ,ACCOUNT_LEVEL
831 ,period_name
832 ,start_date
833 FROM ja_cn_account_balances_gt
834 WHERE account_segment =
835 l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value
836 AND currency_code <>
837 (SELECT fnd_currencies_vl.NAME
838 FROM fnd_currencies_vl
839 WHERE currency_code =
840 l_ja_cn_subsidiary_gbl_tmp_row.is_foreign)
841 AND FUNC_BEGIN_BALANCE <> 0
842 AND FUNC_END_BALANCE <> 0;
843 END IF;
844 END LOOP;
845 CLOSE c_in_ja_cn_subsidiary_gbl_tmp;
846
847 --output error accounts if any
848 SELECT COUNT(*)
849 INTO l_number_b
850 FROM JA_CN_ERROR_ACCOUNTS
851 WHERE request_id = l_na_curr_req_id;
852 IF l_number_b > 0 THEN
853 l_xml_layout := FND_REQUEST.ADD_LAYOUT(template_appl_name => 'JA',
854 template_code => 'JACNABER',
855 template_language => l_template_language --'zh' ('en')
856 ,
857 template_territory => l_template_territory --'00' ('US')
858 ,
859 output_format => l_output_format --'RTF' ('PDF')
860 );
861 l_na_req_id := FND_REQUEST.Submit_Request(application => 'JA',
862 program => 'JACNABER',
863 argument1 => l_na_curr_req_id,
864 argument2 => p_start_period,
865 argument3 => p_end_period);
866 COMMIT;
867
868 --Waiting for the 'Generating Natural Account Export Exception Report' completed.
869 IF l_na_req_id <> 0 THEN
870 IF FND_CONCURRENT.Wait_For_Request(request_id => l_na_req_id,
871 INTERVAL => 5,
872 max_wait => 0,
873 phase => l_na_req_phase,
874 status => l_na_req_status,
875 dev_phase => l_na_req_dev_phase,
876 dev_status => l_na_req_dev_status,
877 message => l_na_req_message) THEN
878 IF l_na_req_phase = 'Completed' THEN
879 NULL;
880 END IF; --l_na_req_phase = 'Completed'
884 DELETE FROM JA_CN_ERROR_ACCOUNTS WHERE REQUEST_ID = l_na_curr_req_id;
881 END IF; -- FND_CONCURRENT.Wait_For_Request ...
882 END IF; --l_na_req_id<>0
883 --DELETE rows with l_na_curr_req_id in TABLE JA_CN_ERROR_ACCOUNTS;
885 retcode := 1;
886 errbuf := FND_MESSAGE.get;
887 RETURN;
888 END IF;
889
890 --output
891 OPEN c_in_account_balances_gbl_tmp;
892 LOOP
893 FETCH c_in_account_balances_gbl_tmp
894 INTO l_account_balances_gbl_tmp_row;
895 EXIT WHEN c_in_account_balances_gbl_tmp%NOTFOUND;
896 --if acount level is not out of range, export
897 IF JA_CN_UTILITY.Check_Account_Level(l_account_balances_gbl_tmp_row.account_level) THEN
898 --modify the balance amount according to balance side
899 IF l_account_balances_gbl_tmp_row.account_type = 'A' OR
900 l_account_balances_gbl_tmp_row.account_type = 'E' THEN
901 l_message := 'D';
902 ELSE
903 l_message := 'C';
904 END IF;
905 IF l_account_balances_gbl_tmp_row.balance_side IS NOT NULL AND
906 l_message <> l_account_balances_gbl_tmp_row.balance_side THEN
907 l_account_balances_gbl_tmp_row.func_begin_balance := l_account_balances_gbl_tmp_row.func_begin_balance * -1;
908 l_account_balances_gbl_tmp_row.orig_begin_balance := l_account_balances_gbl_tmp_row.orig_begin_balance * -1;
909 l_account_balances_gbl_tmp_row.func_end_balance := l_account_balances_gbl_tmp_row.func_end_balance * -1;
910 l_account_balances_gbl_tmp_row.orig_end_balance := l_account_balances_gbl_tmp_row.orig_end_balance * -1;
911 END IF;
912 --if is functional currency, set original amounts to zero
913 /*IF l_account_balances_gbl_tmp_row.CURRENCY_CODE = l_functional_currency THEN
914 l_account_balances_gbl_tmp_row.orig_begin_balance := 0;
915 l_account_balances_gbl_tmp_row.orig_period_net_dr := 0;
916 l_account_balances_gbl_tmp_row.orig_period_net_cr := 0;
917 l_account_balances_gbl_tmp_row.orig_end_balance := 0;
918 END IF;*/
919
920 FND_FILE.put_line(FND_FILE.output,
921 '"' || l_account_balances_gbl_tmp_row.account_segment --account number
922 || '"' || FND_GLOBAL.Local_Chr(9) || '"' ||
923 l_account_balances_gbl_tmp_row.currency_code --currency
924 || '"' || FND_GLOBAL.Local_Chr(9) || '"' ||
925 JA_CN_JE_EXP_PKG.Get_Subsidiary_Desc(p_cost_center => l_account_balances_gbl_tmp_row.cost_center,
926 p_third_party_number => l_account_balances_gbl_tmp_row.third_party_number,
927 p_personnel_number => l_account_balances_gbl_tmp_row.personnel_number,
928 p_project_number => l_account_balances_gbl_tmp_row.project_number,
929 p_has_cost_center => l_account_balances_gbl_tmp_row.has_cost_center,
930 p_has_third_party => l_account_balances_gbl_tmp_row.has_third_party,
931 p_has_personnel => l_account_balances_gbl_tmp_row.has_personnel,
932 p_has_project => l_account_balances_gbl_tmp_row.has_project) --subsidiary account group
933 || '"' || FND_GLOBAL.Local_Chr(9) || '' ||
934 TRIM(to_char(nvl(l_account_balances_gbl_tmp_row.func_begin_balance,
935 0),
936 '99999999999999999990.99')) --opening balance
937 || '' || FND_GLOBAL.Local_Chr(9) || '' || '0.00' --opening quantity
938 || '' || FND_GLOBAL.Local_Chr(9) || '' ||
939 TRIM(to_char(nvl(l_account_balances_gbl_tmp_row.orig_begin_balance,
940 0),
941 '99999999999999999990.99')) --opening foreign currency balance
942 || '' || FND_GLOBAL.Local_Chr(9) || '' ||
943 TRIM(to_char(nvl(l_account_balances_gbl_tmp_row.func_period_net_dr,
944 0),
945 '999999999999999990.99')) --period debit amount
946 || '' || FND_GLOBAL.Local_Chr(9) || '' || '0.00' --period debit quantity
947 || '' || FND_GLOBAL.Local_Chr(9) || '' ||
948 TRIM(to_char(nvl(l_account_balances_gbl_tmp_row.orig_period_net_dr,
949 0),
950 '999999999999999990.99')) --period foreign currency debit amount
951 || '' || FND_GLOBAL.Local_Chr(9) || '' ||
952 TRIM(to_char(nvl(l_account_balances_gbl_tmp_row.func_period_net_cr,
953 0),
954 '999999999999999990.99')) --period credit amount
955 || '' || FND_GLOBAL.Local_Chr(9) || '' || '0.00' --preiod credit quantity
956 || '' || FND_GLOBAL.Local_Chr(9) || '' ||
957 TRIM(to_char(nvl(l_account_balances_gbl_tmp_row.orig_period_net_cr,
958 0),
959 '999999999999999990.99')) --period foreign currency credit amount
960 || '' || FND_GLOBAL.Local_Chr(9) || '' ||
961 TRIM(to_char(nvl(l_account_balances_gbl_tmp_row.func_end_balance,
962 0),
963 '99999999999999999990.99')) --closing balance
964 || '' || FND_GLOBAL.Local_Chr(9) || '' || '0.00' --closing quantity
965 || '' || FND_GLOBAL.Local_Chr(9) || '' ||
966 TRIM(to_char(nvl(l_account_balances_gbl_tmp_row.orig_end_balance,
967 0),
968 '99999999999999999990.99')) --closing foreign currency balance
969 || '' || FND_GLOBAL.Local_Chr(9) || '"' ||
970 TRIM(to_char(l_account_balances_gbl_tmp_row.period_mon,
971 '09')) --accounting month
972 || '"');
973 END IF; --if JA_CN_UTILITY.Check_Account_Level(l_account_balances_gbl_tmp_row.balance_side)
974 END LOOP;
975 CLOSE c_in_account_balances_gbl_tmp;
976
977 --log
978 IF (l_procedure_level >= l_runtime_level) THEN
979
980 FND_LOG.STRING(l_procedure_level,
981 l_module_name,
982 'Stop running ' || l_module_name);
983
984 END IF;
985 END Run_Export;
986
987 END JA_CN_AB_EXP_PKG;