[Home] [Help]
PACKAGE BODY: APPS.JA_CN_JE_EXP_PKG
Source
1 PACKAGE BODY JA_CN_JE_EXP_PKG AS
2 --$Header: JACNJEEB.pls 120.0.12000000.1 2007/08/13 14:09:43 qzhao noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================
8 --| FILENAME
9 --| JACNJEES.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 --| PROCEDURE gen_clauses
19 --| FUNCTION get_subsidiary_desc
20 --|
21 --|
22 --| HISTORY
23 --| 07-May-2006 Shujuan Yan Created
24 --|
25 --+======================================================================*/
26
27 --==========================================================================
28 -- PROCEDURE NAME:
29 --
30 -- run_export Public
31 --
32 -- DESCRIPTION:
33 --
34 -- This procedure is used to export the journal entries.
35 --
36 --
37 -- PARAMETERS:
38 -- Out: errbuf Mandatory parameter for PL/SQL concurrent programs
39 -- Out: retcode Mandatory parameter for PL/SQL concurrent programs
40 -- In p_coa_id Chart of Accounts Id
41 -- In p_ledger_id Ledger Id
42 -- In: p_legal_entity Legal entity ID
43 -- In: p_start_period start period name
44 -- In: P_end_period end period name
45 --
46 -- DESIGN REFERENCES:
47 --
48 --
49 -- CHANGE HISTORY:
50 --
51 -- 07-May-2006 Shujuan Yan Created
52 --
53 --===========================================================================
54
55 PROCEDURE Run_Export(errbuf OUT NOCOPY VARCHAR2
56 ,retcode OUT NOCOPY VARCHAR2
57 ,p_coa_id IN NUMBER
58 ,p_ledger_id IN NUMBER
59 ,p_legal_entity_id IN NUMBER
60 ,p_start_period IN VARCHAR2
61 ,p_end_period IN VARCHAR2) IS
62
63 --variables start here
64 l_runtime_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
65 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
66 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
67 l_exception_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
68 l_flag VARCHAR2(15);
69
70 l_module_name CONSTANT VARCHAR2(300) := 'JA_CN_JE_EXP_PKG.Run_Export';
71 l_message VARCHAR2(300);
72 --l_set_of_books_id ja_cn_system_parameters_all.legal_entity_id%TYPE;
73 --l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
74 l_flex_value_set_id fnd_id_flex_segments.flex_value_set_id%TYPE;
75 l_ja_cn_dff_assignments_row ja_cn_dff_assignments%ROWTYPE;
76 l_context_code ja_cn_dff_assignments.context_code%TYPE;
77 l_attribute_column4cost_center ja_cn_dff_assignments.attribute_column%TYPE;
78 l_attribute_column4third_party ja_cn_dff_assignments.attribute_column%TYPE;
79 l_attribute_column4personnel ja_cn_dff_assignments.attribute_column%TYPE;
80 l_attribute_column4project ja_cn_dff_assignments.attribute_column%TYPE;
81 l_account_segment ja_cn_journal_lines.account_segment%TYPE;
82 l_ja_cn_subsidiary_gbl_tmp_row ja_cn_subsidiary_gt%ROWTYPE;
83 l_journal_lines_gbl_tmp_row ja_cn_journal_lines_gt%ROWTYPE;
84 l_sql_stmt VARCHAR2(30000) := '';
85 l_journal_number ja_cn_journal_lines_gt.journal_number%TYPE := '';
86 l_journal_line_number NUMBER := 0;
87 l_functional_currency fnd_currencies_vl.NAME%TYPE;
88
89 l_start_date DATE;
90 l_end_date DATE;
91 l_current_period GL_PERIOD_STATUSES.Period_Name%TYPE;
92 l_current_start_date DATE;
93
94 l_sql_stmt4insert_j_line CONSTANT VARCHAR2(30000) := 'INSERT INTO ja_cn_journal_lines_gt
95 (journal_number,
96 je_category,
97 description,
98 default_effective_date,
99 je_line_num,
100 account_segment_value,
101 accounted_dr,
102 accounted_cr,
103 entered_dr,
104 entered_cr,
105 currency_code,
106 currency_conversion_rate,
107 journal_preparer,
108 JOURNAL_APPROVER,
109 JOURNAL_POSTER,
110 PERIOD_NAME,
111 START_DATE,
112 has_third_party,
113 has_cost_center,
114 has_personnel,
115 has_project
116 @COLUMN_CLAUSE)
117
118 SELECT ' ||
119 prefix_a ||
120 '.journal_number,' ||
121 prefix_b ||
122 '.USER_JE_CATEGORY_NAME,' ||
123 prefix_a ||
124 '.description,' ||
125 prefix_a ||
126 '.default_effective_date,' ||
127 prefix_a ||
128 '.je_line_num,' ||
129 prefix_a ||
130 '.account_segment,' ||
131 prefix_a ||
132 '.accounted_dr,' ||
133 prefix_a ||
134 '.accounted_cr,' ||
135 prefix_a ||
136 '.entered_dr,' ||
137 prefix_a ||
138 '.entered_cr,' ||
139 prefix_c || '.name,' ||
140 prefix_a ||
141 '.currency_conversion_rate,' ||
142 prefix_a ||
143 '.JOURNAL_CREATOR,' ||
144 prefix_a ||
145 '.JOURNAL_APPROVER,' ||
146 prefix_a ||
147 '.JOURNAL_POSTER,' ||
148 prefix_a ||
149 '.PERIOD_NAME, :1,
150 s.has_third_party,
151 s.has_cost_center,
152 s.has_personnel,
153 s.has_project
154 @PREFIX_COLUMN_CLAUSE
155 FROM (SELECT journal_number,
156 je_category,
157 description,
158 default_effective_date,
159 je_line_num,
160 account_segment,
161 SUM(accounted_dr) accounted_dr,
162 SUM(accounted_cr) accounted_cr,
163 SUM(entered_dr) entered_dr,
164 SUM(entered_cr) entered_cr,
165 currency_code,
166 currency_conversion_rate,
167 JOURNAL_CREATOR,
168 JOURNAL_APPROVER,
169 JOURNAL_POSTER,
170 PERIOD_NAME
171 @COLUMN_CLAUSE
172 FROM ja_cn_journal_lines
173 WHERE period_name = :2
174 AND account_segment = :3
175 @AND_CONDITION
176 AND ledger_id = :4
177 AND company_segment IN
178 (SELECT bal_seg_value
179 FROM ja_cn_ledger_le_bsv_gt
180 WHERE legal_entity_id = :5)
181 GROUP BY journal_number,
182 account_segment,
183 je_category,
184 description,
185 default_effective_date,
186 je_line_num,
187 currency_code,
188 currency_conversion_rate,
189 JOURNAL_CREATOR,
190 JOURNAL_APPROVER,
191 JOURNAL_POSTER,
192 period_name
193 @COLUMN_CLAUSE
194 ) ' ||
195 prefix_a ||
196 ' left join JA_CN_SUBSIDIARY_GT s on ' ||
197 prefix_a ||
198 '.account_segment=s.ACCOUNT_SEGMENT_VALUE' ||
199 ' LEFT JOIN gl_je_categories_vl ' ||
200 prefix_b || ' ON ' ||
201 prefix_a ||
202 '.je_category=' ||
203 prefix_b ||
204 '.JE_CATEGORY_NAME' ||
205 ' LEFT JOIN fnd_currencies_vl ' ||
206 prefix_c || ' ON ' ||
207 prefix_a ||
208 '.currency_code=' ||
209 prefix_c ||
210 '.currency_code ';
211
212 l_column_clauses assoc_array_varchar1000_type;
213 l_prefix_column_clauses assoc_array_varchar1000_type;
214 l_column_clause VARCHAR2(500);
215 l_prefix_column_clause VARCHAR2(500);
216
217 --exceptions start here
218 --sob_unfetchable EXCEPTION;
219
220 --cursors start here
221 CURSOR c_in_ja_cn_dff_assignments IS
222 SELECT *
223 FROM ja_cn_dff_assignments
224 WHERE chart_of_accounts_id = p_coa_id
225 AND (dff_title_code = 'SACC'
226 OR dff_title_code = 'SATP'
227 OR dff_title_code = 'SAEE'
228 OR dff_title_code = 'SAPA');
229
230 CURSOR c_in_ja_cn_journal_lines IS
231 SELECT DISTINCT account_segment
232 FROM ja_cn_journal_lines
233 WHERE company_segment IN
234 (SELECT bal_seg_value--segment_value
235 FROM ja_cn_ledger_le_bsv_gt--ja_cn_legal_companies_all
236 WHERE legal_entity_id = p_legal_entity_id
237 AND chart_of_accounts_id = p_coa_id)
238 AND period_name IN
239 (SELECT period_name
240 FROM GL_PERIOD_STATUSES
241 WHERE ledger_id = p_ledger_id
242 AND application_id = 101
243 AND ((start_date BETWEEN l_start_date AND l_end_date) AND
244 (end_date BETWEEN l_start_date AND l_end_date)))
245 AND ledger_id = p_ledger_id;--set_of_books_id = l_set_of_books_id;
246
247 CURSOR c_in_ja_cn_subsidiary_gbl_tmp IS
248 SELECT * FROM ja_cn_subsidiary_gt;
249
250 CURSOR c_in_journal_lines_gbl_tmp IS
251 SELECT *
252 FROM ja_cn_journal_lines_gt
253 ORDER BY start_date, to_number(journal_number), to_number(je_line_num);
254
255 CURSOR c_in_closed_periods IS
256 SELECT period_name
257 FROM GL_PERIOD_STATUSES
258 WHERE ledger_id = p_ledger_id--set_of_books_id = l_set_of_books_id
259 AND application_id = 101
260 AND ((start_date BETWEEN l_start_date AND l_end_date) AND
261 (end_date BETWEEN l_start_date AND l_end_date))
262 ORDER BY start_date;
263
264 BEGIN
265
266 --log the parameters
267 IF (l_procedure_level >= l_runtime_level) THEN
268
269 FND_LOG.STRING(l_procedure_level,
270 l_module_name,
271 'Start to run ' || l_module_name ||
272 'with parameter: p_coa_id=' ||
273 nvl(to_char(p_coa_id), 'null') || ' p_ledger_id=' ||
274 nvl(to_char(p_ledger_id), 'null') || ' p_legal_entity_id=' ||
275 nvl(to_char(p_legal_entity_id), 'null') || ' p_start_period=' ||
276 nvl(to_char(p_start_period), 'null') || ' p_end_period=' ||
277 nvl(to_char(p_end_period), 'null'));
278
279 END IF;
280
281 --call JA_CN_UTILITY.Check_Profile, if it doesn't return true, exit
282 IF JA_CN_UTILITY.Check_Profile() <> TRUE THEN
283 IF (l_exception_level >= l_runtime_level) THEN
284 FND_LOG.STRING(l_exception_level,
285 l_module_name,
286 'Check profile failed!');
287 END IF;
288 retcode := 1;
289 RETURN;
290 END IF;
291
292 l_flag := Ja_Cn_Utility.Populate_Ledger_Le_Bsv_Gt(p_Ledger_Id,p_Legal_Entity_Id);
293
294 --call JA_CN_UTILITY.Check_Accounting_Period_Range, if it doesn't return true, exit
295 IF ja_cn_utility.Check_Accounting_Period_Range(p_start_period,
296 p_end_period,
297 p_legal_entity_id,
298 p_ledger_id) <> TRUE THEN
299 FND_MESSAGE.set_name('JA', 'JA_CN_PERIOD_OPEN');
300 FND_MESSAGE.SET_TOKEN('PERIOD_FROM', p_start_period, TRUE);
301 FND_MESSAGE.SET_TOKEN('PERIOD_TO', p_end_period, TRUE);
302 l_message := FND_MESSAGE.get();
303 FND_FILE.put_line(FND_FILE.LOG, l_message);
304
305 IF (l_exception_level >= l_runtime_level) THEN
306 FND_LOG.STRING(l_exception_level,
307 l_module_name,
308 'Check account period range failed!');
309 END IF;
310 retcode := 1;
311 errbuf := l_message;
312 RETURN;
313 END IF;
314 /*
315 --fetch set of books id and chart of account id
316 JA_CN_UTILITY.Get_SOB_And_COA(p_legal_entity_id => p_legal_entity,
317 x_sob_id => l_set_of_books_id,
318 x_coa_id => l_chart_of_accounts_id,
319 x_flag => l_flag);
320
321 IF l_flag = -1 THEN
322 IF (l_exception_level >= l_runtime_level) THEN
323 FND_LOG.STRING(l_exception_level,
324 l_module_name,
325 'Get SOB or COA failed!');
326 END IF;
327 retcode := 1;
328
329 RETURN;
330 END IF;
331
332 --log the SOB
333 IF (l_statement_level >= l_runtime_level) THEN
334 FND_LOG.STRING(l_statement_level,
335 l_module_name,
336 'Fetched: l_set_of_books_id=' ||
337 nvl(to_char(l_set_of_books_id), 'null'));
338 END IF;
339 --log chat of account
340 IF (l_statement_level >= l_runtime_level) THEN
341 FND_LOG.STRING(l_statement_level,
342 l_module_name,
343 'Fetched: l_chart_of_accounts_id=' ||
344 nvl(to_char(l_chart_of_accounts_id), 'null'));
345 END IF;
346 */
347
348 --fetch start data and end date
349 SELECT start_date
350 INTO l_start_date
351 FROM GL_PERIOD_STATUSES
352 WHERE ledger_id = p_ledger_id--set_of_books_id = l_set_of_books_id
353 AND application_id = 101
354 AND period_name = p_start_period;
355
356 SELECT end_date
357 INTO l_end_date
358 FROM GL_PERIOD_STATUSES
359 WHERE ledger_id = p_ledger_id --set_of_books_id = l_set_of_books_id
360 AND application_id = 101
361 AND period_name = p_end_period;
362
363 --log start data and end date
364 IF (l_statement_level >= l_runtime_level) THEN
365 FND_LOG.STRING(l_statement_level,
366 l_module_name,
367 'Fetched: start date=' ||
368 nvl(to_char(l_start_date), 'null') || ' end date=' ||
369 nvl(to_char(l_end_date), 'null'));
370 END IF;
371
372 --fetch flex_value_set_id
373 SELECT s.flex_value_set_id
374 INTO l_flex_value_set_id
375 FROM fnd_id_flex_segments s
376 WHERE s.application_id = 101
377 AND s.id_flex_num = p_coa_id--l_chart_of_accounts_id
378 AND s.id_flex_code = 'GL#'
379 AND s.application_column_name =
380 (SELECT application_column_name
381 FROM fnd_segment_attribute_values
382 WHERE application_id = 101
383 AND segment_attribute_type = 'GL_ACCOUNT'
384 AND attribute_value = 'Y'
385 AND id_flex_num = p_coa_id
386 AND id_flex_code = 'GL#');--l_chart_of_accounts_id);
387 --log
388 IF (l_statement_level >= l_runtime_level) THEN
389 FND_LOG.STRING(l_statement_level,
390 l_module_name,
391 'Fetched: l_flex_value_set_id=' ||
392 nvl(to_char(l_flex_value_set_id), 'null'));
393 END IF;
394
395 --fetch context code, attribute column for cost center, third party, personnel and project
396 OPEN c_in_ja_cn_dff_assignments;
397 LOOP
398 FETCH c_in_ja_cn_dff_assignments
399 INTO l_ja_cn_dff_assignments_row;
400 EXIT WHEN c_in_ja_cn_dff_assignments%NOTFOUND;
401 IF l_ja_cn_dff_assignments_row.context_code IS NOT NULL THEN
402 l_context_code := l_ja_cn_dff_assignments_row.context_code;
403 END IF;
404 CASE l_ja_cn_dff_assignments_row.dff_title_code
405 WHEN 'SACC' THEN
406 l_attribute_column4cost_center := l_ja_cn_dff_assignments_row.attribute_column;
407 WHEN 'SATP' THEN
408 l_attribute_column4third_party := l_ja_cn_dff_assignments_row.attribute_column;
409 WHEN 'SAEE' THEN
410 l_attribute_column4personnel := l_ja_cn_dff_assignments_row.attribute_column;
411 WHEN 'SAPA' THEN
412 l_attribute_column4project := l_ja_cn_dff_assignments_row.attribute_column;
413 END CASE; END LOOP;
414 CLOSE c_in_ja_cn_dff_assignments;
415 --log
416 IF (l_statement_level >= l_runtime_level) THEN
417 FND_LOG.STRING(l_statement_level,
418 l_module_name,
419 'Fetched: l_context_code=' ||
420 nvl(to_char(l_context_code), 'null'));
421 FND_LOG.STRING(l_statement_level,
422 l_module_name,
423 'Fetched: l_attribute_column4cost_center=' ||
424 nvl(to_char(l_attribute_column4cost_center), 'null'));
425 FND_LOG.STRING(l_statement_level,
426 l_module_name,
427 'Fetched: l_attribute_column4third_party=' ||
428 nvl(to_char(l_attribute_column4third_party), 'null'));
429
430 FND_LOG.STRING(l_statement_level,
431 l_module_name,
432 'Fetched: l_attribute_column4personnel=' ||
433 nvl(to_char(l_attribute_column4personnel), 'null'));
434
435 FND_LOG.STRING(l_statement_level,
436 l_module_name,
437 'Fetched: l_attribute_column4project=' ||
438 nvl(to_char(l_attribute_column4project), 'null'));
439
440 END IF;
441
442 --fetch the account subsidiary info and save to temp table
443 OPEN c_in_ja_cn_journal_lines;
444 --log
445 IF (l_statement_level >= l_runtime_level) THEN
446 FND_LOG.STRING(l_statement_level,
447 l_module_name,
448 'Fetched account segments start below:');
449
450 END IF;
451 --the context code is not necessary to be a condition to query out the signs for subsidiary
452 l_sql_stmt := 'insert into JA_CN_SUBSIDIARY_GT (ACCOUNT_SEGMENT_VALUE, HAS_THIRD_PARTY,HAS_COST_CENTER,HAS_PERSONNEL,HAS_PROJECT) select flex_value,' ||
453 nvl(to_char(l_attribute_column4third_party), 'null') || ',' ||
454 nvl(to_char(l_attribute_column4cost_center), 'null') || ',' ||
455 nvl(to_char(l_attribute_column4personnel), 'null') || ',' ||
456 nvl(to_char(l_attribute_column4project), 'null') ||
457 ' from FND_FLEX_VALUES where flex_value_set_id=:1 and flex_value=:2';
458
459 LOOP
460 FETCH c_in_ja_cn_journal_lines
461 INTO l_account_segment;
462 EXIT WHEN c_in_ja_cn_journal_lines%NOTFOUND;
463 --log
464 IF (l_statement_level >= l_runtime_level) THEN
465 FND_LOG.STRING(l_statement_level,
466 l_module_name,
467 'about to insert subsidiary info of account: ' ||
468 l_account_segment || ' to temp table');
469
470 END IF;
471
472 EXECUTE IMMEDIATE l_sql_stmt
473 USING l_flex_value_set_id, l_account_segment;
474
475 END LOOP;
476
477 --no data found with the requested parameters
478 IF (c_in_ja_cn_journal_lines%ROWCOUNT = 0) THEN
479 FND_MESSAGE.set_name('JA', 'JA_CN_NO_DATA_FOUND');
480 l_message := FND_MESSAGE.get();
481 FND_FILE.put_line(FND_FILE.LOG, l_message);
482 IF (l_exception_level >= l_runtime_level) THEN
483 FND_LOG.STRING(l_exception_level, l_module_name, l_message);
484 END IF;
485 retcode := 1;
486 errbuf := l_message;
487 RETURN;
488
489 END IF;
490 CLOSE c_in_ja_cn_journal_lines;
491
492 --combine the journal lines and save them to the JA_CN_JOURNAL_LINES_GT
493 OPEN c_in_closed_periods;
494 LOOP
495 FETCH c_in_closed_periods
496 INTO l_current_period;
497 EXIT WHEN c_in_closed_periods%NOTFOUND;
498
499 --fetch start date of current period
500 SELECT start_date
501 INTO l_current_start_date
502 FROM GL_PERIOD_STATUSES
503 WHERE ledger_id = p_ledger_id--set_of_books_id = l_set_of_books_id
504 AND application_id = 101
505 AND period_name = l_current_period;
506
507 --log current period and it's start date
508 IF (l_statement_level >= l_runtime_level) THEN
509 FND_LOG.STRING(l_statement_level,
510 l_module_name,
511 'about to generate journal lines info for period:' ||
512 l_current_period || ' start date:' ||
513 l_current_start_date);
514
515 END IF;
516
517 --FND_FILE.PUT_LINE(FND_FILE.LOG, l_current_start_date);
518
519 OPEN c_in_ja_cn_subsidiary_gbl_tmp;
520 LOOP
521 FETCH c_in_ja_cn_subsidiary_gbl_tmp
522 INTO l_ja_cn_subsidiary_gbl_tmp_row;
523 EXIT WHEN c_in_ja_cn_subsidiary_gbl_tmp%NOTFOUND;
524 --log
525 IF (l_statement_level >= l_runtime_level) THEN
526 FND_LOG.STRING(l_statement_level,
527 l_module_name,
528 'about to generate group clause for account:' ||
529 l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value);
530
531 END IF;
532
533 Gen_Clauses(l_column_clauses,
534 l_prefix_column_clauses,
535 l_ja_cn_subsidiary_gbl_tmp_row.has_cost_center,
536 l_ja_cn_subsidiary_gbl_tmp_row.has_third_party,
537 l_ja_cn_subsidiary_gbl_tmp_row.has_personnel,
538 l_ja_cn_subsidiary_gbl_tmp_row.has_project,
539 l_column_clause,
540 l_prefix_column_clause);
541
542 --dbms_output.put_line(l_column_clause);
543 --dbms_output.put_line(l_prefix_column_clause);
544 --dbms_output.put_line(l_where_clause);
545 l_sql_stmt := REPLACE(l_sql_stmt4insert_j_line,
546 '@COLUMN_CLAUSE',
547 l_column_clause);
548 l_sql_stmt := REPLACE(l_sql_stmt,
549 '@PREFIX_COLUMN_CLAUSE',
550 l_prefix_column_clause);
551 /*IF (l_exception_level >= l_runtime_level)
552 THEN
553 fnd_log.STRING(fnd_log.level_statement, l_module_name, l_sql_stmt);
554 END IF;*/
555 FND_FILE.PUT_LINE(FND_FILE.LOG, l_sql_stmt);
556 EXECUTE IMMEDIATE REPLACE(l_sql_stmt,
557 '@AND_CONDITION',
558 'AND accounted_dr IS NOT NULL')
559 USING l_current_start_date, l_current_period, l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value, p_ledger_id, p_legal_entity_id;
560
561 EXECUTE IMMEDIATE REPLACE(l_sql_stmt,
562 '@AND_CONDITION',
563 'AND accounted_cr IS NOT NULL')
564 USING l_current_start_date, l_current_period, l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value, p_ledger_id, p_legal_entity_id;
565
566 END LOOP;
567 CLOSE c_in_ja_cn_subsidiary_gbl_tmp;
568
569 END LOOP;
570
571 CLOSE c_in_closed_periods;
572
573 --fetch functional currency
574 SELECT fnd_currencies_vl.NAME
575 INTO l_functional_currency
576 FROM fnd_currencies_vl
577 WHERE currency_code =
578 (SELECT currency_code
579 FROM gl_ledgers--gl_sets_of_books
580 WHERE ledger_id = p_ledger_id );--set_of_books_id = l_set_of_books_id);
581 --log
582 IF (l_statement_level >= l_runtime_level) THEN
583 FND_LOG.STRING(l_statement_level,
584 l_module_name,
585 'Fetched: l_functional_currency=' ||
586 nvl(to_char(l_functional_currency), 'null'));
587 END IF;
588
589 --output
590 OPEN c_in_journal_lines_gbl_tmp;
591 LOOP
592 FETCH c_in_journal_lines_gbl_tmp
593 INTO l_journal_lines_gbl_tmp_row;
594 EXIT WHEN c_in_journal_lines_gbl_tmp%NOTFOUND;
595 IF l_journal_number = l_journal_lines_gbl_tmp_row.journal_number THEN
596 l_journal_line_number := l_journal_line_number + 1;
597 ELSE
598 l_journal_number := l_journal_lines_gbl_tmp_row.journal_number;
599 l_journal_line_number := 1;
600 END IF;
601
602 IF l_functional_currency = l_journal_lines_gbl_tmp_row.currency_code THEN
603 l_journal_lines_gbl_tmp_row.entered_dr := 0;
604 l_journal_lines_gbl_tmp_row.entered_cr := 0;
605 END IF;
606
607 FND_FILE.put_line(FND_FILE.output,
608 '"' || to_char(l_journal_lines_gbl_tmp_row.default_effective_date,
609 'YYYYMMDD') --journal date
610 || '"' || FND_GLOBAL.Local_Chr(9) || '"' ||
611 l_journal_lines_gbl_tmp_row.je_category --category
612 || '"' || FND_GLOBAL.Local_Chr(9) || '"' ||
613 l_journal_number --journal number
614 || '"' || FND_GLOBAL.Local_Chr(9) || '' ||
615 l_journal_line_number --journal line number
616 || '' || FND_GLOBAL.Local_Chr(9) || '"' ||
617 l_journal_lines_gbl_tmp_row.description --description
618 || '"' || FND_GLOBAL.Local_Chr(9) || '"' ||
619 l_journal_lines_gbl_tmp_row.account_segment_value --account
620 || '"' || FND_GLOBAL.Local_Chr(9) || '' ||
621 TRIM(to_char(nvl(l_journal_lines_gbl_tmp_row.accounted_dr,
622 0),
623 '999999999999999990.99')) --debit
624 || '' || FND_GLOBAL.Local_Chr(9) || '' ||
625 TRIM(to_char(nvl(l_journal_lines_gbl_tmp_row.accounted_cr,
626 0),
627 '999999999999999990.99')) --credit
628 || '' || FND_GLOBAL.Local_Chr(9) || '"' ||
629 l_journal_lines_gbl_tmp_row.currency_code --currency
630 || '"' || FND_GLOBAL.Local_Chr(9) || '' ||
631 TRIM(to_char(nvl(l_journal_lines_gbl_tmp_row.entered_dr,
632 0),
633 '999999999999999990.99')) --debit abount in foreign currency
634 || '' || FND_GLOBAL.Local_Chr(9) || '' ||
635 TRIM(to_char(nvl(l_journal_lines_gbl_tmp_row.entered_cr,
636 0),
637 '999999999999999990.99')) --credit abount in foreign currency
638 || '' || FND_GLOBAL.Local_Chr(9) || '' ||
639 TRIM(to_char(nvl(l_journal_lines_gbl_tmp_row.currency_conversion_rate,
640 0),
641 '9999999999990.999999')) --exchange rate
642 || '' || FND_GLOBAL.Local_Chr(9) || '' || '0.0000' --quantity
643 || '' || FND_GLOBAL.Local_Chr(9) || '' || '0.0000' --unit price
644 || '' || FND_GLOBAL.Local_Chr(9) || '"' ||
645 Get_Subsidiary_Desc(p_cost_center => l_journal_lines_gbl_tmp_row.cost_center,
646 p_third_party_number => l_journal_lines_gbl_tmp_row.third_party_number,
647 p_personnel_number => l_journal_lines_gbl_tmp_row.personnel_number,
648 p_project_number => l_journal_lines_gbl_tmp_row.project_number,
649 p_has_cost_center => l_journal_lines_gbl_tmp_row.has_cost_center,
650 p_has_third_party => l_journal_lines_gbl_tmp_row.has_third_party,
651 p_has_personnel => l_journal_lines_gbl_tmp_row.has_personnel,
652 p_has_project => l_journal_lines_gbl_tmp_row.has_project) --subsidiary account group
653 || '"' || FND_GLOBAL.Local_Chr(9) || '"' || '' --settlement method
654 || '"' || FND_GLOBAL.Local_Chr(9) || '"' || '' --bill type
655 || '"' || FND_GLOBAL.Local_Chr(9) || '"' || '' --bill number
656 || '"' || FND_GLOBAL.Local_Chr(9) || '"' || '' --bill date
657 || '"' || FND_GLOBAL.Local_Chr(9) || '' || '0' --attachment quantity
658 || '' || FND_GLOBAL.Local_Chr(9) || '"' ||
659 l_journal_lines_gbl_tmp_row.journal_preparer --preparer
660 || '"' || FND_GLOBAL.Local_Chr(9) || '"' ||
661 l_journal_lines_gbl_tmp_row.JOURNAL_APPROVER -- journal approver
662 || '"' || FND_GLOBAL.Local_Chr(9) || '"' ||
663 l_journal_lines_gbl_tmp_row.journal_poster --bookkeeper
664 || '"' || FND_GLOBAL.Local_Chr(9) || '"' || '' --cashier
665 || '"' || FND_GLOBAL.Local_Chr(9) || '"' || '1' --closing flag
666 || '"' || FND_GLOBAL.Local_Chr(9) || '"' || '0' --cancel flag
667 || '"');
668
669 END LOOP;
670 CLOSE c_in_journal_lines_gbl_tmp;
671 IF (l_procedure_level >= l_runtime_level) THEN
672
673 FND_LOG.STRING(l_procedure_level,
674 l_module_name,
675 'Stop running ' || l_module_name);
676
677 END IF;
678 END Run_Export;
679
680 --==========================================================================
681 -- PROCEDURE NAME:
682 --
683 -- gen_clauses Public
684 --
685 -- DESCRIPTION:
686 --
687 -- This procedure is used to generate the column names with or withouot
688 -- prefix in order to complete the SQL statements which are used to query
689 -- journal entries.
690 --
691 -- PARAMETERS:
692 -- In Out: p_column_clauses Collection stores generated column clauses
693 -- In Out: p_prefix_column_clauses Collection stores generated prefix column clauses
694 -- In: p_has_cost_center 'Y' or not indicates whether it is cost center subsidiary
695 -- In: p_has_third_party 'C or S' or not indicates whether it is third party subsidiary
696 -- In: p_has_personnel 'Y' or not indicates whether it is personnel subsidiary
697 -- In: p_has_project 'Y' or not indicates whether it is project subsidiary
698 -- Out: p_return_column_clause Return value of generated column clause
699 -- Out: p_return_prefix_column_clause Return value of generated prefix column clause
700 --
701 --
702 -- DESIGN REFERENCES:
703 --
704 --
705 -- CHANGE HISTORY:
706 --
707 -- 01-May-2007 Shujuan Yan Created
708 --
709 --===========================================================================
710
711 PROCEDURE Gen_Clauses(p_column_clauses IN OUT NOCOPY assoc_array_varchar1000_type
712 ,p_prefix_column_clauses IN OUT NOCOPY assoc_array_varchar1000_type
713 ,p_has_cost_center VARCHAR2
714 ,p_has_third_party VARCHAR2
715 ,p_has_personnel VARCHAR2
716 ,p_has_project VARCHAR2
717 ,p_return_column_clause OUT NOCOPY VARCHAR2
718 ,p_return_prefix_column_clause OUT NOCOPY VARCHAR2) IS
719
720 l_runtime_level NUMBER := FND_LOG.G_Current_Runtime_Level;
721 l_procedure_level NUMBER := FND_LOG.Level_Procedure;
722 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
723 l_module_name CONSTANT VARCHAR2(300) := 'JA_CN_JE_EXP_PKG.Gen_Clauses';
724
725 key PLS_INTEGER := 0;
726 column_clause VARCHAR2(500) := '';
727 prefix_column_clause VARCHAR2(500) := '';
728 BEGIN
729 IF (l_procedure_level >= l_runtime_level) THEN
730 FND_LOG.STRING(l_procedure_level,
731 l_module_name,
732 'Start to generate clauses by ' || l_module_name ||
733 ' in sequence of [CC][TP][PL][PT] with: ' ||
734 nvl(p_has_cost_center, 'null') ||
735 nvl(p_has_third_party, 'null') ||
736 nvl(p_has_personnel, 'null') || nvl(p_has_project, 'null'));
737
738 END IF;
739
740 IF (p_has_cost_center = 'Y') THEN
741 key := key + 8;
742 END IF;
743 IF (p_has_third_party = 'C' OR p_has_third_party = 'S') THEN
744 key := key + 4;
745 END IF;
746 IF (p_has_personnel = 'Y') THEN
747 key := key + 2;
748 END IF;
749 IF (p_has_project = 'Y') THEN
750 key := key + 1;
751 END IF;
752 IF (l_statement_level >= l_runtime_level) THEN
753 FND_LOG.STRING(l_statement_level,
754 l_module_name,
755 'Generated key is: ' || key);
756 END IF;
757
758 IF p_column_clauses.EXISTS(key) = FALSE THEN
759 IF (l_statement_level >= l_runtime_level) THEN
760 FND_LOG.STRING(l_statement_level,
761 l_module_name,
762 'There is no clauses pre-generated with key: ' || key ||
763 ' so, generate it right now!');
764 END IF;
765
766 IF (p_has_cost_center = 'Y') THEN
767 column_clause := column_clause || ', COST_CENTER ';
768 prefix_column_clause := prefix_column_clause || ', ' || prefix_a ||
769 '.COST_CENTER ';
770
771 END IF;
772 IF (p_has_third_party = 'C' OR p_has_third_party = 'S') THEN
773 column_clause := column_clause || ', THIRD_PARTY_NUMBER ';
774 prefix_column_clause := prefix_column_clause || ', ' || prefix_a ||
775 '.THIRD_PARTY_NUMBER ';
776
777 END IF;
778 IF (p_has_personnel = 'Y') THEN
779 column_clause := column_clause || ', PERSONNEL_NUMBER ';
780 prefix_column_clause := prefix_column_clause || ', ' || prefix_a ||
781 '.PERSONNEL_NUMBER ';
782
783 END IF;
784 IF (p_has_project = 'Y') THEN
785 column_clause := column_clause || ', PROJECT_NUMBER ';
786 prefix_column_clause := prefix_column_clause || ', ' || prefix_a ||
787 '.PROJECT_NUMBER ';
788
789 END IF;
790 p_column_clauses(key) := column_clause;
791 p_prefix_column_clauses(key) := prefix_column_clause;
792
793 END IF;
794
795 p_return_prefix_column_clause := p_prefix_column_clauses(key);
796 p_return_column_clause := p_column_clauses(key);
797 IF (l_procedure_level >= l_runtime_level) THEN
798
799 FND_LOG.STRING(l_procedure_level,
800 l_module_name,
801 'Stop running ' || l_module_name);
802
803 END IF;
804 END Gen_Clauses;
805
806 --==========================================================================
807 -- PROCEDURE NAME:
808 --
809 -- get_subsidiary_desc Public
810 --
811 -- DESCRIPTION:
812 --
813 -- This procedure is used to generate the subsidiary description.
814 --
815 --
816 -- PARAMETERS:
817 -- In: p_cost_center Cost center segment
818 -- In: p_third_party_number Number of third party
819 -- In: p_personnel_number Personnel number
820 -- In: p_project_number Number of project
821 -- In: p_has_cost_center 'Y' or not indicates whether it is cost center subsidiary.
822 -- In: p_has_third_party 'C or S' or not indicates whether it is third party subsidiary.
823 -- In: p_has_personnel 'Y' or not indicates whether it is personnel subsidiary.
824 -- In: p_has_project 'Y' or not indicates whether it is project subsidiary.
825 --
826 --
827 -- DESIGN REFERENCES:
828 --
829 --
830 -- CHANGE HISTORY:
831 --
832 -- 07-May-2007 Shujuan Yan Created
833 --
834 --===========================================================================
835 FUNCTION Get_Subsidiary_Desc(p_cost_center VARCHAR2
836 ,p_third_party_number VARCHAR2
837 ,p_personnel_number VARCHAR2
838 ,p_project_number VARCHAR2
839 ,p_has_cost_center VARCHAR2
840 ,p_has_third_party VARCHAR2
841 ,p_has_personnel VARCHAR2
842 ,p_has_project VARCHAR2) RETURN VARCHAR2 IS
843
844 l_subsidiary_desc VARCHAR2(500) := '';
845 BEGIN
846
847 IF (p_has_project = 'Y') THEN
848 l_subsidiary_desc := l_subsidiary_desc || p_project_number || '/';
849 END IF;
850 IF (p_has_third_party = 'C' OR p_has_third_party = 'S') THEN
851 l_subsidiary_desc := l_subsidiary_desc || p_third_party_number || '/';
852 END IF;
853 IF (p_has_cost_center = 'Y') THEN
854 l_subsidiary_desc := l_subsidiary_desc || p_cost_center || '/';
855 END IF;
856 IF (p_has_personnel = 'Y') THEN
857 l_subsidiary_desc := l_subsidiary_desc || p_personnel_number || '/';
858 END IF;
859
860 IF (l_subsidiary_desc IS NOT NULL) THEN
861
862 l_subsidiary_desc := substr(l_subsidiary_desc,
863 1,
864 length(l_subsidiary_desc) - 1);
865 END IF;
866 RETURN l_subsidiary_desc;
867 END Get_Subsidiary_Desc;
868
869 END JA_CN_JE_EXP_PKG;