[Home] [Help]
PACKAGE BODY: APPS.JA_CN_JE_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_JE_EXPORT_PKG AS
2 --$Header: JACNJOEB.pls 120.7.12020000.2 2013/03/29 09:51:26 chongwan ship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================|
8 --| FILENAME |
9 --| JACNJOEB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| |
13 --| This package contains the following PL/SQL tables/procedures |
14 --| to export Journal which already post in the Itemized Journal table|
15 --| |
16 --| PROCEDURE LIST |
17 --| Add_Journal |
18 --| |
19 --| HISTORY |
20 --| 18-Mar-2010 Wei Huang Created |
21 --| 13-APr-2010 Wei Huang Updated Procedure Add_Journal |
22 --| 19-May-2010 Wei Huang Updated procedure Add_journal |
23 --| 03-Jun-2010 Wei Huang Updated procedure Add_journal |
24 --| |
25 --| 07/14/2010 Chaoqun Wu Fixed bug 9872212, exchange rate type |
26 --| should be popluated from SLA table |
27 --| 01/05/2011 Jianchao Chi Updated for the new solution to change |
28 --| the logic of Voucher Number and Legal |
29 --| Entity |
30 --+======================================================================*/
31 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_JE_EXPORT_PKG';
32
33 --==========================================================================
34 -- PROCEDURE NAME:
35 --
36 -- Add_Journal Public
37 --
38 -- DESCRIPTION:
39 --
40 -- This procedure is to export Journal which already post in the
41 -- CNAO Itemized Journal table.
42 --
43 -- PARAMETERS:
44 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
45 -- pn_ledger_id Ledger ID
46 -- pv_accounting_year Accounting Year
47 -- pn_coa_id Chart of Accounts ID
48 -- pv_period_from Period From
49 -- pv_period_to Period To
50 --
51 -- Out:
52 --
53 -- DESIGN REFERENCES:
54 --
55 --
56 -- CHANGE HISTORY:
57 -- 18-Mar-2010 Wei Huang Created
58 -- 13-APr-2010 Wei Huang Updated Procedure Add_Journal, Clear the
59 -- JA_CN_SUBSIDIARY_GT temporary table before
60 -- insert data to it.
61 -- 19-May-2010 Wei Huang Updated procedure Add_journal, change the
62 -- exporting of Bill Date from eight spaces to
63 -- the default of fixed date '00000000'.
64 -- 03-Jun-2010 Wei Huang Updated the currency_conversion_rate logic to
65 -- NVL(ROUND('||prefix_a ||'.currency_conversion_rate,4),1),'
66 -- 14-Jul-2010 Chaoqun Wu Fixed bug 9872212
67 -- 05-Jan-2011 Jianchao Chi Updated for the new solution to change
68 -- the logic of Voucher Number and Legal Entity
69 -- 29-Mar-2013 Jar Wang for bug 16182326
70 --===========================================================================
71 PROCEDURE Add_Journal
72 (pn_legal_entity_id IN NUMBER
73 ,pn_ledger_id IN NUMBER
74 ,pv_accounting_year IN VARCHAR2
75 ,pn_coa_id IN NUMBER
76 ,pv_period_from IN VARCHAR2
77 ,pv_period_to IN VARCHAR2
78 )
79 IS
80 lv_procedure_name VARCHAR2(40) := 'Add_Journal';
81 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
82 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
83 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
84 ln_exception_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
85 NO_DATA EXCEPTION;
86
87 lv_message VARCHAR2(300);
88 ln_flex_value_set_id fnd_id_flex_segments.flex_value_set_id%TYPE;
89 l_account_segment ja_cn_journal_lines.account_segment%TYPE;
90 l_ja_cn_subsidiary_gbl_tmp_row ja_cn_subsidiary_gt%ROWTYPE;
91 lv_sql_stmt VARCHAR2(30000) := '';
92 l_journal_number ja_cn_journal_lines_gt.journal_number%TYPE := '';
93 ln_journal_line_number NUMBER := 0;
94 l_functional_currency fnd_currencies_vl.NAME%TYPE;
95
96 ld_start_date DATE;
97 ld_end_date DATE;
98 l_current_period GL_PERIOD_STATUSES.Period_Name%TYPE;
99 ld_current_start_date DATE;
100 ln_current_period_num NUMBER;
101 ln_row_count NUMBER := 0;
102 lv_je_category VARCHAR2(200);
103 lv_sql_stmt4insert_j_line CONSTANT VARCHAR2(30000) := 'INSERT INTO ja_cn_journal_lines_gt
104 (journal_number,
105 je_category,
106 description,
107 default_effective_date,
108 je_line_num,
109 account_segment_value,
110 accounted_dr,
111 accounted_cr,
112 entered_dr,
113 entered_cr,
114 currency_code,
115 currency_conversion_rate,
116 journal_preparer,
117 JOURNAL_APPROVER,
118 JOURNAL_POSTER,
119 PERIOD_NAME,
120 START_DATE,
121 SETTLEMENT_METHOD_NUMBER,
122 CURRENCY_CONVERSION_TYPE,
123 PERIOD_NUM,
124 JOURNAL_SOURCE,
125 segment1,segment2,segment3,segment4,segment5,
126 segment6,segment7,segment8,segment9,segment10,
127 segment11,segment12,segment13,segment14,segment15,
128 segment16,segment17,segment18,segment19,segment20,
129 segment21,segment22,segment23,segment24,segment25,
130 segment26,segment27,segment28,segment29,segment30
131 )
132 SELECT ' ||
133 prefix_a ||
134 '.journal_number,' ||
135 prefix_a ||
136 '.je_category,' ||
137 prefix_a ||
138 '.description,' ||
139 prefix_a ||
140 '.default_effective_date,' ||
141 prefix_a ||
142 '.je_line_num,' ||
143 prefix_a ||
144 '.account_segment,' ||
145 prefix_a ||
146 '.accounted_dr,' ||
147 prefix_a ||
148 '.accounted_cr,' ||
149 prefix_a ||
150 '.entered_dr,' ||
151 prefix_a ||
152 '.entered_cr,' ||
153 prefix_a || '.currency_code,' ||
154 'NVL(ROUND(' ||
155 prefix_a ||
156 '.currency_conversion_rate,4),1),' ||
157 prefix_a ||
158 '.JOURNAL_CREATOR,' ||
159 prefix_a ||
160 '.JOURNAL_APPROVER,' ||
161 prefix_a ||
162 '.JOURNAL_POSTER,' ||
163 prefix_a ||
164 '.PERIOD_NAME, :1,' ||
165 prefix_a ||
166 '.SETTLEMENT_METHOD_NUMBER,' ||
167 prefix_a ||
168 '.CURRENCY_CONVERSION_TYPE, :2,' || --Updated by Chaoqun for fixing bug 9872212
169 'jh.je_source,' ||
170 'segment1,segment2,segment3,segment4,segment5,
171 segment6,segment7,segment8,segment9,segment10,
172 segment11,segment12,segment13,segment14,segment15,
173 segment16,segment17,segment18,segment19,segment20,
174 segment21,segment22,segment23,segment24,segment25,
175 segment26,segment27,segment28,segment29,segment30'||
176 --Comment by Jianchao Chi for new solution to change the logic
177 --of Legal Entity and Voucher Number, 05-JAN-2011
178 /*FROM (SELECT je_header_id,
179 journal_number,
180 je_category,
181 description,
182 default_effective_date,
183 je_line_num,
184 account_segment,
185 SUM(accounted_dr) accounted_dr,
186 SUM(accounted_cr) accounted_cr,
187 SUM(entered_dr) entered_dr,
188 SUM(entered_cr) entered_cr,
189 currency_code,
190 currency_conversion_rate,
191 JOURNAL_CREATOR,
192 JOURNAL_APPROVER,
193 JOURNAL_POSTER,
194 PERIOD_NAME,
195 SETTLEMENT_METHOD_NUMBER,
196 CURRENCY_CONVERSION_TYPE,
197 segment1,segment2,segment3,segment4,segment5,
198 segment6,segment7,segment8,segment9,segment10,
199 segment11,segment12,segment13,segment14,segment15,
200 segment16,segment17,segment18,segment19,segment20,
201 segment21,segment22,segment23,segment24,segment25,
202 segment26,segment27,segment28,segment29,segment30
203 FROM ja_cn_journal_lines
204 WHERE period_name = :3
205 AND account_segment = :4
206 @AND_CONDITION
207 AND ledger_id = :5
208 AND company_segment IN
209 (SELECT bal_seg_value
210 FROM ja_cn_ledger_le_bsv_gt
211 WHERE legal_entity_id = :6)
212 GROUP BY je_header_id,
213 voucher_number,
214 account_segment,
215 je_category,
216 description,
217 default_effective_date,
218 je_line_num,
219 currency_code,
220 currency_conversion_rate,
221 JOURNAL_CREATOR,
222 JOURNAL_APPROVER,
223 JOURNAL_POSTER,
224 period_name,
225 SETTLEMENT_METHOD_NUMBER,
226 CURRENCY_CONVERSION_TYPE,*/
227
228 --Update by Jianchao Chi, for cnaov2 upgrade 05-JAN-2011
229 --Add ja_cn_voucher_number table, voucher number and legal entity
230 --are fetched from ja_cn_voucher_number
231 ' FROM (SELECT jcjl.je_header_id,
232 jcjl.voucher_number journal_number,
233 jcjl.je_category,
234 jcjl.description,
235 jcjl.default_effective_date,
236 jcjl.je_line_num,
237 jcjl.account_segment,
238 SUM(jcjl.accounted_dr) accounted_dr,
239 SUM(jcjl.accounted_cr) accounted_cr,
240 SUM(jcjl.entered_dr) entered_dr,
241 SUM(jcjl.entered_cr) entered_cr,
242 jcjl.currency_code,
243 jcjl.currency_conversion_rate,
244 jcjl.JOURNAL_CREATOR,
245 jcjl.JOURNAL_APPROVER,
246 jcjl.JOURNAL_POSTER,
247 jcjl.PERIOD_NAME,
248 jcjl.SETTLEMENT_METHOD_NUMBER,
249 jcjl.CURRENCY_CONVERSION_TYPE,
250 segment1,segment2,segment3,segment4,segment5,
251 segment6,segment7,segment8,segment9,segment10,
252 segment11,segment12,segment13,segment14,segment15,
253 segment16,segment17,segment18,segment19,segment20,
254 segment21,segment22,segment23,segment24,segment25,
255 segment26,segment27,segment28,segment29,segment30
256 FROM (SELECT DISTINCT jc.*, jcvn.voucher_number
257 FROM ja_cn_voucher_number jcvn, ja_cn_journal_lines jc
258 WHERE jcvn.je_header_id = jc.je_header_id
259 AND jcvn.je_line_number = jc.je_line_num) jcjl
260 WHERE jcjl.period_name = :3
261 AND jcjl.account_segment = :4
262 @AND_CONDITION
263 AND jcjl.ledger_id = :5
264 AND jcjl.company_segment IN
265 (SELECT bal_seg_value
266 FROM ja_cn_ledger_le_bsv_gt
267 WHERE legal_entity_id = :6)
268 GROUP BY jcjl.je_header_id,
269 jcjl.voucher_number,
270 jcjl.account_segment,
271 jcjl.je_category,
272 jcjl.description,
273 jcjl.default_effective_date,
274 jcjl.je_line_num,
275 jcjl.currency_code,
276 jcjl.currency_conversion_rate,
277 jcjl.JOURNAL_CREATOR,
278 jcjl.JOURNAL_APPROVER,
279 jcjl.JOURNAL_POSTER,
280 jcjl.period_name,
281 jcjl.SETTLEMENT_METHOD_NUMBER,
282 jcjl.CURRENCY_CONVERSION_TYPE,
283 segment1,segment2,segment3,segment4,segment5,
284 segment6,segment7,segment8,segment9,segment10,
285 segment11,segment12,segment13,segment14,segment15,
286 segment16,segment17,segment18,segment19,segment20,
287 segment21,segment22,segment23,segment24,segment25,
288 segment26,segment27,segment28,segment29,segment30
289 ) ' ||
290 prefix_a ||
291 ' left join JA_CN_SUBSIDIARY_GT s on ' ||
292 prefix_a ||
293 '.account_segment=s.ACCOUNT_SEGMENT_VALUE' ||
294 ' LEFT JOIN GL_JE_HEADERS jh ON ' ||
295 prefix_a ||
296 '.je_header_id=jh.je_header_id ';
297
298 --cursors start here
299 CURSOR c_in_ja_cn_journal_lines
300 IS
301 SELECT DISTINCT account_segment
302 FROM ja_cn_journal_lines
303 WHERE company_segment IN
304 (SELECT bal_seg_value
305 FROM ja_cn_ledger_le_bsv_gt
306 WHERE legal_entity_id = pn_legal_entity_id
307 AND chart_of_accounts_id = pn_coa_id)
308 AND period_name IN
309 (SELECT period_name
310 FROM GL_PERIOD_STATUSES
311 WHERE ledger_id = pn_ledger_id
312 AND application_id = 101
313 AND ((start_date BETWEEN ld_start_date AND ld_end_date) AND
314 (end_date BETWEEN ld_start_date AND ld_end_date)))
315 AND ledger_id = pn_ledger_id;
316
317 CURSOR c_in_ja_cn_subsidiary_gbl_tmp
318 IS
319 SELECT * FROM ja_cn_subsidiary_gt;
320
321 CURSOR c_in_closed_periods
322 IS
323 SELECT period_name
324 FROM GL_PERIOD_STATUSES
325 WHERE ledger_id = pn_ledger_id
326 AND application_id = 101
327 AND ((start_date BETWEEN ld_start_date AND ld_end_date) AND
328 (end_date BETWEEN ld_start_date AND ld_end_date))
329 ORDER BY start_date;
330
331 CURSOR c_in_journal_lines_gbl_tmp
332 IS
333 SELECT *
334 FROM ja_cn_journal_lines_gt
335 ORDER BY start_date, to_number(journal_number), to_number(je_line_num);
336
337 BEGIN
338 --log the parameters
339 IF (ln_proc_level >= ln_dbg_level) 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_legal_entity_id=' || pn_legal_entity_id || ',' ||
349 'pn_ledger_id=' || pn_ledger_id || ',' ||
350 'pv_accounting_year=' || pv_accounting_year || ',' ||
351 'pn_coa_id=' || pn_coa_id || ',' ||
352 'pv_period_from=' || pv_period_from || ',' ||
353 'pv_period_to=' || pv_period_to);
354 END IF;
355
356 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
357 '.parameters:' ||
358 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
359 'pn_ledger_id=' || pn_ledger_id || ',' ||
360 'pv_accounting_year=' || pv_accounting_year || ',' ||
361 'pn_coa_id=' || pn_coa_id || ',' ||
362 'pv_period_from=' || pv_period_from || ',' ||
363 'pv_period_to=' || pv_period_to);
364
365 FND_FILE.put_line(FND_FILE.log, lv_sql_stmt4insert_j_line);
366 --Populate balancing segment values to table ja_cn_ledger_le_bsv_gt
367 DELETE FROM JA_CN_LEDGER_LE_BSV_GT;
368 COMMIT;
369 IF Ja_Cn_Utility.Populate_Ledger_Le_Bsv_Gt(pn_Ledger_Id,pn_legal_entity_id) <> 'S'
370 THEN
371 RETURN;
372 END IF;
373
374 --Fetch start data and end date
375 IF pv_period_from IS NOT NULL THEN
376 SELECT start_date
377 INTO ld_start_date
378 FROM GL_PERIOD_STATUSES
379 WHERE ledger_id = pn_ledger_id
380 AND application_id = 101
381 AND period_name = pv_period_from
382 AND TO_CHAR(period_year) = pv_accounting_year;
383 ELSE
384 ld_start_date := to_date(pv_accounting_year||'0101','YYYYMMDD');
385 END IF; -- pv_period_from IS NOT NULL
386
387 IF pv_period_to IS NOT NULL THEN
388 SELECT end_date
389 INTO ld_end_date
390 FROM GL_PERIOD_STATUSES
391 WHERE ledger_id = pn_ledger_id
392 AND application_id = 101
393 AND period_name = pv_period_to
394 AND TO_CHAR(period_year) = pv_accounting_year;
395 ELSE
396 ld_end_date := to_date(pv_accounting_year||'1231','YYYYMMDD');
397 END IF; -- pv_period_to IS NOT NULL
398
399 --logging the variables
400 IF (ln_statement_level >= ln_dbg_level)
401 THEN
402 FND_LOG.STRING(ln_statement_level,
403 lv_procedure_name,
404 'ld_start_date=' || ld_start_date || ',' ||
405 'ld_end_date=' || ld_end_date);
406 END IF; --(ln_statement_level >= ln_dbg_level)
407 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
408 '.variable:' ||
409 'ld_start_date=' || ld_start_date || ',' ||
410 'ld_end_date=' || ld_end_date);
411
412 --Fetch flex_value_set_id
413 SELECT s.flex_value_set_id
414 INTO ln_flex_value_set_id
415 FROM fnd_id_flex_segments s
416 WHERE s.application_id = 101
417 AND s.id_flex_num = pn_coa_id--l_chart_of_accounts_id
418 AND s.id_flex_code = 'GL#'
419 AND s.application_column_name =
420 (SELECT application_column_name
421 FROM fnd_segment_attribute_values
422 WHERE application_id = 101
423 AND segment_attribute_type = 'GL_ACCOUNT'
424 AND attribute_value = 'Y'
425 AND id_flex_num = pn_coa_id
426 AND id_flex_code = 'GL#');--l_chart_of_accounts_id);
427
428 --logging the variables
429 IF (ln_statement_level >= ln_dbg_level)
430 THEN
431 FND_LOG.STRING(ln_statement_level,
432 lv_procedure_name,
433 'ln_flex_value_set_id=' || ln_flex_value_set_id);
434 END IF; --(ln_statement_level >= ln_dbg_level)
435 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
436 '.variable:' ||
437 'ln_flex_value_set_id=' || ln_flex_value_set_id);
438
439 --Clear the JA_CN_SUBSIDIARY_GT temporary table as the Accounting
440 --Balance already inserted data
441 DELETE FROM JA_CN_SUBSIDIARY_GT;
442 COMMIT;
443
444 --fetch the account and save it temp table
445 OPEN c_in_ja_cn_journal_lines;
446 --log
447 IF (ln_statement_level >= ln_dbg_level) THEN
448 FND_LOG.STRING(ln_statement_level,
449 lv_procedure_name,
450 'Fetched account segments start below:');
451
452 END IF; --ln_statement_level >= ln_dbg_level
453 --the context code is not necessary to be a condition to query out the signs for subsidiary
454 lv_sql_stmt := 'insert into JA_CN_SUBSIDIARY_GT (ACCOUNT_SEGMENT_VALUE) select flex_value' ||
455 ' from FND_FLEX_VALUES where flex_value_set_id=:1 and flex_value=:2';
456 LOOP
457 FETCH c_in_ja_cn_journal_lines
458 INTO l_account_segment;
459 EXIT WHEN c_in_ja_cn_journal_lines%NOTFOUND;
460
461 EXECUTE IMMEDIATE lv_sql_stmt
462 USING ln_flex_value_set_id, l_account_segment;
463
464 END LOOP;
465
466 --no data found with the requested parameters
467 IF (c_in_ja_cn_journal_lines%ROWCOUNT = 0) THEN
468 FND_MESSAGE.set_name('JA', 'JA_CN_NO_DATA_FOUND');
469 lv_message := FND_MESSAGE.get();
470 FND_FILE.put_line(FND_FILE.LOG, lv_message);
471 IF (ln_exception_level >= ln_dbg_level) THEN
472 FND_LOG.STRING(ln_exception_level, lv_procedure_name, lv_message);
473 END IF;
474 ln_row_count := 0;
475 END IF;
476 CLOSE c_in_ja_cn_journal_lines;
477
478 --combine the journal lines and save them to the JA_CN_JOURNAL_LINES_GT
479 OPEN c_in_closed_periods;
480 LOOP
481 FETCH c_in_closed_periods
482 INTO l_current_period;
483 EXIT WHEN c_in_closed_periods%NOTFOUND;
484
485 --fetch start date of current period
486 SELECT start_date,period_num
487 INTO ld_current_start_date, ln_current_period_num
488 FROM GL_PERIOD_STATUSES
489 WHERE ledger_id = pn_ledger_id
490 AND application_id = 101
491 AND period_name = l_current_period;
492
493 --log current period and it's start date
494 IF (ln_statement_level >= ln_dbg_level) THEN
495 FND_LOG.STRING(ln_statement_level,
496 lv_procedure_name,
497 'about to generate journal lines info for period:' ||
498 l_current_period || ' start date:' ||
499 ld_current_start_date);
500 END IF;
501
502 OPEN c_in_ja_cn_subsidiary_gbl_tmp;
503 LOOP
504 FETCH c_in_ja_cn_subsidiary_gbl_tmp
505 INTO l_ja_cn_subsidiary_gbl_tmp_row;
506 EXIT WHEN c_in_ja_cn_subsidiary_gbl_tmp%NOTFOUND;
507 --log
508 IF (ln_statement_level >= ln_dbg_level) THEN
509 FND_LOG.STRING(ln_statement_level,
510 lv_procedure_name,
511 'about to generate group clause for account:' ||
512 l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value);
513 END IF;
514
515 lv_sql_stmt := lv_sql_stmt4insert_j_line;
516
517 EXECUTE IMMEDIATE REPLACE(lv_sql_stmt,
518 '@AND_CONDITION',
519 'AND accounted_dr IS NOT NULL')
520 USING ld_current_start_date, ln_current_period_num, l_current_period, l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value, pn_ledger_id, pn_legal_entity_id;
521
522 EXECUTE IMMEDIATE REPLACE(lv_sql_stmt,
523 '@AND_CONDITION',
524 'AND accounted_cr IS NOT NULL')
525 USING ld_current_start_date, ln_current_period_num, l_current_period, l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value, pn_ledger_id, pn_legal_entity_id;
526
527 END LOOP;
528 CLOSE c_in_ja_cn_subsidiary_gbl_tmp;
529
530 END LOOP;
531
532 CLOSE c_in_closed_periods;
533
534 --fetch functional currency
535 SELECT fnd_currencies_vl.NAME
536 INTO l_functional_currency
537 FROM fnd_currencies_vl
538 WHERE currency_code =
539 (SELECT currency_code
540 FROM gl_ledgers
541 WHERE ledger_id = pn_ledger_id );
542
543 --logging the variables
544 IF (ln_statement_level >= ln_dbg_level)
545 THEN
546 FND_LOG.STRING(ln_statement_level,
547 lv_procedure_name,
548 'l_functional_currency=' || l_functional_currency);
549 END IF; --(ln_statement_level >= ln_dbg_level)
550 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
551 '.variable:' ||
552 'l_functional_currency=' || l_functional_currency);
553
554
555 --output
556 FOR v_row IN c_in_journal_lines_gbl_tmp
557 LOOP
558 ln_row_count := ln_row_count + 1;
559
560 IF l_journal_number = v_row.journal_number THEN
561 ln_journal_line_number := ln_journal_line_number + 1;
562 ELSE
563 l_journal_number := v_row.journal_number;
564 ln_journal_line_number := 1;
565 END IF;
566
567 IF l_functional_currency = v_row.currency_code THEN
568 v_row.entered_dr := 0;
569 v_row.entered_cr := 0;
570 END IF;
571
572 Ja_Cn_Utility.Add_Sub_Root_Node('JOURNAL',
573 Ja_Cn_Utility.GV_TAG_TYPE_START);
574 Ja_Cn_Utility.Add_Fixed_Child_Node('GL_JOURNAL_CREATION_DATE',
575 TO_CHAR(v_row.default_effective_date,'YYYYMMDD'),
576 8);
577 Ja_Cn_Utility.Add_Fixed_Child_Node('ACCOUNTING_YEAR',
578 pv_accounting_year,
579 4);
580 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER',
581 v_row.period_num);
582
583 select user_je_category_name into lv_je_category from GL_JE_CATEGORIES where JE_CATEGORY_NAME=v_row.je_category;
584 --Ja_Cn_Utility.Add_Child_Node('JOURNAL_CATEGORY_NUMBER', v_row.je_category);
585 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CATEGORY_NUMBER',lv_je_category); --added by jarwang for bug 16182326
586
587 Ja_Cn_Utility.Add_Child_Node('JOURNAL_NUMBER',
588 l_journal_number);
589 Ja_Cn_Utility.Add_Child_Node('JOURNAL_LINE_NUMBER',
590 ln_journal_line_number);
591 Ja_Cn_Utility.Add_Child_Node('JOURNAL_DESCRIPTION',
592 v_row.description);
593 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER',
594 v_row.account_segment_value);
595 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO1',
596 Ja_Cn_Utility.Process_Subsidiary_Item(1,v_row.Segment1));
597 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO2',
598 Ja_Cn_Utility.Process_Subsidiary_Item(2,v_row.Segment2));
599 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO3',
600 Ja_Cn_Utility.Process_Subsidiary_Item(3,v_row.Segment3));
601 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO4',
602 Ja_Cn_Utility.Process_Subsidiary_Item(4,v_row.Segment4));
603 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO5',
604 Ja_Cn_Utility.Process_Subsidiary_Item(5,v_row.Segment5));
605 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO6',
606 Ja_Cn_Utility.Process_Subsidiary_Item(6,v_row.Segment6));
607 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO7',
608 Ja_Cn_Utility.Process_Subsidiary_Item(7,v_row.Segment7));
609 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO8',
610 Ja_Cn_Utility.Process_Subsidiary_Item(8,v_row.Segment8));
611 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO9',
612 Ja_Cn_Utility.Process_Subsidiary_Item(9,v_row.Segment9));
613 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO10',
614 Ja_Cn_Utility.Process_Subsidiary_Item(10,v_row.Segment10));
615 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO11',
616 Ja_Cn_Utility.Process_Subsidiary_Item(11,v_row.Segment11));
617 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO12',
618 Ja_Cn_Utility.Process_Subsidiary_Item(12,v_row.Segment12));
619 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO13',
620 Ja_Cn_Utility.Process_Subsidiary_Item(13,v_row.Segment13));
621 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO14',
622 Ja_Cn_Utility.Process_Subsidiary_Item(14,v_row.Segment14));
623 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO15',
624 Ja_Cn_Utility.Process_Subsidiary_Item(15,v_row.Segment15));
625 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO16',
626 Ja_Cn_Utility.Process_Subsidiary_Item(16,v_row.Segment16));
627 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO17',
628 Ja_Cn_Utility.Process_Subsidiary_Item(17,v_row.Segment17));
629 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO18',
630 Ja_Cn_Utility.Process_Subsidiary_Item(18,v_row.Segment18));
631 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO19',
632 Ja_Cn_Utility.Process_Subsidiary_Item(19,v_row.Segment19));
633 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO20',
634 Ja_Cn_Utility.Process_Subsidiary_Item(20,v_row.Segment20));
635 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO21',
636 Ja_Cn_Utility.Process_Subsidiary_Item(21,v_row.Segment21));
637 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO22',
638 Ja_Cn_Utility.Process_Subsidiary_Item(22,v_row.Segment22));
639 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO23',
640 Ja_Cn_Utility.Process_Subsidiary_Item(23,v_row.Segment23));
641 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO24',
642 Ja_Cn_Utility.Process_Subsidiary_Item(24,v_row.Segment24));
643 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO25',
644 Ja_Cn_Utility.Process_Subsidiary_Item(25,v_row.Segment25));
645 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO26',
646 Ja_Cn_Utility.Process_Subsidiary_Item(26,v_row.Segment26));
647 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO27',
648 Ja_Cn_Utility.Process_Subsidiary_Item(27,v_row.Segment27));
649 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO28',
650 Ja_Cn_Utility.Process_Subsidiary_Item(28,v_row.Segment28));
651 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO29',
652 Ja_Cn_Utility.Process_Subsidiary_Item(29,v_row.Segment29));
653 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO30',
654 Ja_Cn_Utility.Process_Subsidiary_Item(30,v_row.Segment30));
655 Ja_Cn_Utility.Add_Child_Node('CURRENCY_CODE',
656 v_row.currency_code);
657 Ja_Cn_Utility.Add_Child_Node('UNIT_OF_MEASURE',
658 NULL);
659 Ja_Cn_Utility.Add_Child_Node('DEBIT_ENTERED_AMOUNT',
660 v_row.entered_dr,
661 Ja_Cn_Utility.GV_TYPE_NUMBER);
662 Ja_Cn_Utility.Add_Child_Node('DEBIT_FUNCTIONAL_AMOUNT',
663 v_row.accounted_dr,
664 Ja_Cn_Utility.GV_TYPE_NUMBER);
665 Ja_Cn_Utility.Add_Child_Node('CREDIT_ENTERED_AMOUNT',
666 v_row.entered_cr,
667 Ja_Cn_Utility.GV_TYPE_NUMBER);
668 Ja_Cn_Utility.Add_Child_Node('CREDIT_FUNCTIONAL_AMOUNT',
669 v_row.accounted_cr,
670 Ja_Cn_Utility.GV_TYPE_NUMBER);
671 Ja_Cn_Utility.Add_Child_Node('EXCHANGE_RATE_TYPE_NUMBER',
672 v_row.currency_conversion_type);
673 Ja_Cn_Utility.Add_Child_Node('EXCHANGE_RATE',
674 v_row.currency_conversion_rate,
675 Ja_Cn_Utility.GV_TYPE_NUMBER);
676 Ja_Cn_Utility.Add_Child_Node('JOURNAL_HEADER_FLEX_VALUE',
677 NULL);
678 Ja_Cn_Utility.Add_Child_Node('JOURNAL_LINE_FLEX_VALUE',
679 NULL);
680 Ja_Cn_Utility.Add_Child_Node('SETTLEMENT_METHOD_NUMBER',
681 v_row.settlement_method_number);
682 Ja_Cn_Utility.Add_Child_Node('BILL_TYPE',
683 NULL);
684 Ja_Cn_Utility.Add_Child_Node('BILL_NUMBER',
685 NULL);
686 Ja_Cn_Utility.Add_Fixed_Child_Node('BILL_DATE',
687 NULL,
688 8);
689 Ja_Cn_Utility.Add_Child_Node('ATTACHMENT_QUANTITY',
690 '0');
691 Ja_Cn_Utility.Add_Child_Node('CREATOR',
692 v_row.journal_preparer);
693 Ja_Cn_Utility.Add_Child_Node('REVIEWER',
694 v_row.journal_approver);
695 Ja_Cn_Utility.Add_Child_Node('POSTER',
696 v_row.journal_poster);
697 Ja_Cn_Utility.Add_Fixed_Child_Node('POSTED',
698 '1',
699 1);
700 Ja_Cn_Utility.Add_Fixed_Child_Node('CANCELLED',
701 '0',
702 1);
703 Ja_Cn_Utility.Add_Child_Node('JOURNAL_SOURCE',
704 v_row.journal_source);
705 Ja_Cn_Utility.Add_Sub_Root_Node('JOURNAL',
706 Ja_Cn_Utility.GV_TAG_TYPE_END);
707 END LOOP;
708
709 -- To judge if the Cursor fetchs data. If the row count is 0 and the
710 -- minOccurs=0 specified in xml schema, then need to handle it and raise exception
711 IF (ln_row_count = 0)
712 THEN
713 Ja_Cn_Utility.Print_No_Data_Found_For_Log('JOURNAL');
714
715 Ja_Cn_Utility.Add_Sub_Root_Node('JOURNAL',
716 Ja_Cn_Utility.GV_TAG_TYPE_START);
717 Ja_Cn_Utility.Add_Fixed_Child_Node('GL_JOURNAL_CREATION_DATE',
718 NULL,
719 8);
720 Ja_Cn_Utility.Add_Fixed_Child_Node('ACCOUNTING_YEAR',
721 NULL,
722 4);
723 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER',
724 NULL);
725 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CATEGORY_NUMBER',
726 NULL);
727 Ja_Cn_Utility.Add_Child_Node('JOURNAL_NUMBER',
728 NULL);
729 Ja_Cn_Utility.Add_Child_Node('JOURNAL_LINE_NUMBER',
730 NULL);
731 Ja_Cn_Utility.Add_Child_Node('JOURNAL_DESCRIPTION',
732 NULL);
733 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER',
734 NULL);
735 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO1',
736 NULL);
737 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO2',
738 NULL);
739 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO3',
740 NULL);
741 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO4',
742 NULL);
743 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO5',
744 NULL);
745 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO6',
746 NULL);
747 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO7',
748 NULL);
749 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO8',
750 NULL);
751 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO9',
752 NULL);
753 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO10',
754 NULL);
755 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO11',
756 NULL);
757 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO12',
758 NULL);
759 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO13',
760 NULL);
761 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO14',
762 NULL);
763 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO15',
764 NULL);
765 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO16',
766 NULL);
767 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO17',
768 NULL);
769 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO18',
770 NULL);
771 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO19',
772 NULL);
773 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO20',
774 NULL);
775 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO21',
776 NULL);
777 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO22',
778 NULL);
779 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO23',
780 NULL);
781 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO24',
782 NULL);
783 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO25',
784 NULL);
785 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO26',
786 NULL);
787 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO27',
788 NULL);
789 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO28',
790 NULL);
791 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO29',
792 NULL);
793 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO30',
794 NULL);
795 Ja_Cn_Utility.Add_Child_Node('CURRENCY_CODE',
796 NULL);
797 Ja_Cn_Utility.Add_Child_Node('UNIT_OF_MEASURE',
798 NULL);
799 Ja_Cn_Utility.Add_Child_Node('DEBIT_ENTERED_AMOUNT',
800 NULL,
801 Ja_Cn_Utility.GV_TYPE_NUMBER);
802 Ja_Cn_Utility.Add_Child_Node('DEBIT_FUNCTIONAL_AMOUNT',
803 NULL,
804 Ja_Cn_Utility.GV_TYPE_NUMBER);
805 Ja_Cn_Utility.Add_Child_Node('CREDIT_ENTERED_AMOUNT',
806 NULL,
807 Ja_Cn_Utility.GV_TYPE_NUMBER);
808 Ja_Cn_Utility.Add_Child_Node('CREDIT_FUNCTIONAL_AMOUNT',
809 NULL,
810 Ja_Cn_Utility.GV_TYPE_NUMBER);
811 Ja_Cn_Utility.Add_Child_Node('EXCHANGE_RATE_TYPE_NUMBER',
812 NULL);
813 Ja_Cn_Utility.Add_Child_Node('EXCHANGE_RATE',
814 NULL,
815 Ja_Cn_Utility.GV_TYPE_NUMBER);
816 Ja_Cn_Utility.Add_Child_Node('JOURNAL_HEADER_FLEX_VALUE',
817 NULL);
818 Ja_Cn_Utility.Add_Child_Node('JOURNAL_LINE_FLEX_VALUE',
819 NULL);
820 Ja_Cn_Utility.Add_Child_Node('SETTLEMENT_METHOD_NUMBER',
821 NULL);
822 Ja_Cn_Utility.Add_Child_Node('BILL_TYPE',
823 NULL);
824 Ja_Cn_Utility.Add_Child_Node('BILL_NUMBER',
825 NULL);
826 Ja_Cn_Utility.Add_Fixed_Child_Node('BILL_DATE',
827 NULL,
828 8);
829 Ja_Cn_Utility.Add_Child_Node('ATTACHMENT_QUANTITY',
830 NULL);
831 Ja_Cn_Utility.Add_Child_Node('CREATOR',
832 NULL);
833 Ja_Cn_Utility.Add_Child_Node('REVIEWER',
834 NULL);
835 Ja_Cn_Utility.Add_Child_Node('POSTER',
836 NULL);
837 Ja_Cn_Utility.Add_Fixed_Child_Node('POSTED',
838 NULL,
839 1);
840 Ja_Cn_Utility.Add_Fixed_Child_Node('CANCELLED',
841 NULL,
842 1);
843 Ja_Cn_Utility.Add_Child_Node('JOURNAL_SOURCE',
844 NULL);
845 Ja_Cn_Utility.Add_Sub_Root_Node('JOURNAL',
846 Ja_Cn_Utility.GV_TAG_TYPE_END);
847 END IF;
848
849 --logging for debug
850 IF (ln_proc_level >= ln_dbg_level)
851 THEN
852 FND_LOG.STRING(ln_proc_level,
853 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
854 'Exit procedure');
855 END IF; -- (ln_proc_level>=ln_dbg_level
856
857 EXCEPTION
858 WHEN OTHERS THEN
859 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
860 THEN
861 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
862 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
863 '.Other_Exception ',
864 SQLCODE || SQLERRM);
865 END IF;
866 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
867 RAISE;
868
869 END Add_Journal;
870
871 END JA_CN_JE_EXPORT_PKG;
872