[Home] [Help]
PACKAGE BODY: APPS.JA_CN_APAB_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_APAB_EXPORT_PKG AS
2 --$Header: JACNPBEB.pls 120.3 2010/05/20 07:03:13 weihuang noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================|
8 --| FILENAME |
9 --| JACNPBEB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| |
13 --| This package contains the following PL/SQL tables/procedures |
14 --| to export Accounting Period Amount and Balance which already post |
15 --| in the CNAO Itemized balance table. |
16 --| |
17 --| PROCEDURE LIST |
18 --| Add_Account_Amount_Balance |
19 --| |
20 --| HISTORY |
21 --| 04-Mar-2010 Wei Huang Created |
22 --| 19-May-2010 Wei Huang Updated procedure Add_Account_Amount_Balance |
23 --| |
24 --+======================================================================*/
25 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_APAB_EXPORT_PKG';
26
27 --==========================================================================
28 -- PROCEDURE NAME:
29 --
30 -- Add_Account_Amount_Balance Public
31 --
32 -- DESCRIPTION:
33 --
34 -- This procedure is to export Accounting Period Amount and Balance
35 -- which already post in the CNAO Itemized balance table.
36 --
37 -- PARAMETERS:
38 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
39 -- pn_ledger_id Ledger ID
40 -- pv_accounting_year Accounting Year
41 -- pn_coa_id Chart of Accounts ID
42 -- pv_period_from Period From
43 -- pv_period_to Period To
44 --
45 -- Out:
46 --
47 -- DESIGN REFERENCES:
48 --
49 --
50 -- CHANGE HISTORY:
51 -- 04-Mar-2010 Wei Huang Created
52 -- 19-May-2010 Wei Huang Updated the logic of handling NO_DATA Exception
53 --
54 --===========================================================================
55 PROCEDURE Add_Account_Amount_Balance
56 (pn_legal_entity_id IN NUMBER
57 ,pn_ledger_id IN NUMBER
58 ,pv_accounting_year IN VARCHAR2
59 ,pn_coa_id IN NUMBER
60 ,pv_period_from IN VARCHAR2
61 ,pv_period_to IN VARCHAR2
62 )
63 IS
64 lv_procedure_name VARCHAR2(40) := 'Add_Account_Amount_Balance';
65 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
66 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
67 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
68 ln_exception_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
69 NO_DATA EXCEPTION;
70
71 lv_debit FND_LOOKUP_VALUES.MEANING%TYPE;
72 lv_credit FND_LOOKUP_VALUES.MEANING%TYPE;
73 lv_dr_cr_begin FND_LOOKUP_VALUES.MEANING%TYPE;
74 lv_dr_cr_end FND_LOOKUP_VALUES.MEANING%TYPE;
75 lv_message VARCHAR2(300);
76
77 l_functional_currency fnd_currencies_vl.NAME%TYPE;
78 l_functional_currency_code fnd_currencies_vl.CURRENCY_CODE%TYPE;
79 ln_flex_value_set_id fnd_id_flex_segments.flex_value_set_id%TYPE;
80 l_ja_cn_dff_assignments_row ja_cn_dff_assignments%ROWTYPE;
81 l_context_code ja_cn_dff_assignments.context_code%TYPE;
82 l_attribute_column4balanceside ja_cn_dff_assignments.attribute_column%TYPE;
83 l_attribute_column4account_lev ja_cn_dff_assignments.attribute_column%TYPE;
84 l_account_segment ja_cn_journal_lines.account_segment%TYPE;
85 l_ja_cn_subsidiary_gbl_tmp_row ja_cn_subsidiary_gt%ROWTYPE;
86 ln_row_count NUMBER := 0;
87
88 lv_sql_stmt VARCHAR2(30000) := '';
89 lv_sql_stmt4insert_j_line VARCHAR2(30000) := 'INSERT INTO ja_cn_account_balances_gt
90 (account_segment,
91 currency_code,
92 func_begin_balance,
93 orig_begin_balance,
94 func_period_net_dr,
95 func_period_net_cr,
96 orig_period_net_dr,
97 orig_period_net_cr,
98 func_end_balance,
99 orig_end_balance,
100 period_mon,
101 PERIOD_NAME,
102 START_DATE,
103 account_type,
104 is_foreign,
105 balance_side,
106 account_level,
107 segment1,segment2,segment3,segment4,segment5,
108 segment6,segment7,segment8,segment9,segment10,
109 segment11,segment12,segment13,segment14,segment15,
110 segment16,segment17,segment18,segment19,segment20,
111 segment21,segment22,segment23,segment24,segment25,
112 segment26,segment27,segment28,segment29,segment30
113 )
114 (SELECT ' ||
115 prefix_a ||
116 '.account_segment, ' ||
117 prefix_a || '.currency_code, ' ||
118 prefix_a ||
119 '.func_begin_balance,
120 ' ||
121 prefix_a ||
122 '.orig_begin_balance,
123 ' ||
124 prefix_a ||
125 '.func_period_net_dr,
126 ' ||
127 prefix_a ||
128 '.func_period_net_cr,
129 ' ||
130 prefix_a ||
131 '.orig_period_net_dr,
132 ' ||
133 prefix_a ||
134 '.orig_period_net_cr,
135 ' ||
136 prefix_a ||
137 '.func_end_balance,
138 ' ||
139 prefix_a ||
140 '.orig_end_balance,
141 ' ||
142 prefix_a ||
143 '.period_mon,
144 ' ||
145 prefix_a ||
146 '.PERIOD_NAME,:1,
147 ' ||
148 prefix_a ||
149 '.account_type,s.is_foreign,s.balance_side,s.account_level,
150 ' ||
151 'segment1,segment2,segment3,segment4,segment5,
152 segment6,segment7,segment8,segment9,segment10,
153 segment11,segment12,segment13,segment14,segment15,
154 segment16,segment17,segment18,segment19,segment20,
155 segment21,segment22,segment23,segment24,segment25,
156 segment26,segment27,segment28,segment29,segment30'
157 || '
158 FROM (SELECT account_segment,
159 currency_code,
160 SUM(func_begin_balance) func_begin_balance,
161 SUM(orig_begin_balance) orig_begin_balance,
162 SUM(func_period_net_dr) func_period_net_dr,
163 SUM(orig_period_net_dr) orig_period_net_dr,
164 SUM(func_period_net_cr) func_period_net_cr,
165 SUM(orig_period_net_cr) orig_period_net_cr,
166 SUM(func_end_balance) func_end_balance,
167 SUM(orig_end_balance) orig_end_balance,
168 period_mon,
169 account_type,
170 PERIOD_NAME,
171 segment1,segment2,segment3,segment4,segment5,
172 segment6,segment7,segment8,segment9,segment10,
173 segment11,segment12,segment13,segment14,segment15,
174 segment16,segment17,segment18,segment19,segment20,
175 segment21,segment22,segment23,segment24,segment25,
176 segment26,segment27,segment28,segment29,segment30
177 FROM ja_cn_account_balances_v
178 WHERE period_name = :2
179 AND account_segment = :3
180 AND ledger_id = :4
181 AND company_segment IN
182 (SELECT bal_seg_value
183 FROM ja_cn_ledger_le_bsv_gt
184 WHERE legal_entity_id = :5)
185 GROUP BY account_segment,currency_code,period_mon,account_type,PERIOD_NAME,
186 segment1,segment2,segment3,segment4,segment5,
187 segment6,segment7,segment8,segment9,segment10,
188 segment11,segment12,segment13,segment14,segment15,
189 segment16,segment17,segment18,segment19,segment20,
190 segment21,segment22,segment23,segment24,segment25,
191 segment26,segment27,segment28,segment29,segment30) ' ||
192 prefix_a ||
193 ' LEFT JOIN ja_cn_subsidiary_gt s ON ' ||
194 prefix_a ||
195 '.account_segment =s.account_segment_value)';
196
197 ld_start_date DATE;
198 ld_end_date DATE;
199 l_current_period GL_PERIOD_STATUSES.Period_Name%TYPE;
200 ld_current_start_date DATE;
201
202 --cursors start here
203 CURSOR c_in_ja_cn_dff_assignments
204 IS
205 SELECT *
206 FROM ja_cn_dff_assignments
207 WHERE chart_of_accounts_id = pn_coa_id
208 AND (dff_title_code = 'ACBS' -- Balance Side
209 OR dff_title_code = 'ACLE' -- Account Level
210 );
211
212 CURSOR c_in_ja_cn_account_balances
213 IS
214 SELECT DISTINCT account_segment
215 FROM ja_cn_account_balances_v
216 WHERE company_segment IN
217 (SELECT bal_seg_value--segment_value
218 FROM ja_cn_ledger_le_bsv_gt
219 WHERE legal_entity_id = pn_legal_entity_id
220 AND chart_of_accounts_id = pn_coa_id)
221 AND period_name IN
222 (SELECT period_name
223 FROM GL_PERIOD_STATUSES
224 WHERE ledger_id = pn_ledger_id
225 AND application_id = 101
226 AND ((start_date BETWEEN ld_start_date AND ld_end_date) AND
227 (end_date BETWEEN ld_start_date AND ld_end_date)))
228 AND ledger_id = pn_ledger_id;
229
230 CURSOR c_in_ja_cn_subsidiary_gbl_tmp
231 IS
232 SELECT * FROM ja_cn_subsidiary_gt;
233
234 CURSOR c_in_closed_periods
235 IS
236 SELECT period_name
237 FROM GL_PERIOD_STATUSES
238 WHERE ledger_id = pn_ledger_id
239 AND application_id = 101
240 AND ((start_date BETWEEN ld_start_date AND ld_end_date) AND
241 (end_date BETWEEN ld_start_date AND ld_end_date))
242 ORDER BY start_date;
243
244 CURSOR amount_balance_gt_cur
245 IS
246 SELECT *
247 FROM ja_cn_account_balances_gt
248 ORDER BY start_date,ACCOUNT_SEGMENT;
249
250 BEGIN
251 --logging for debug
252 IF (ln_proc_level >= ln_dbg_level)
253 THEN
254 FND_LOG.STRING(ln_proc_level,
255 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
256 '.begin',
257 'Enter procedure');
258 -- logging the parameters
259 FND_LOG.STRING(ln_proc_level,
260 lv_procedure_name ||
261 '.parameters',
262 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
263 'pn_ledger_id=' || pn_ledger_id || ',' ||
264 'pv_accounting_year=' || pv_accounting_year || ',' ||
265 'pn_coa_id=' || pn_coa_id || ',' ||
266 'pv_period_from=' || pv_period_from || ',' ||
267 'pv_period_to=' || pv_period_to);
268 END IF; --ln_proc_level >= ln_dbg_level
269
270 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
271 '.parameters:' ||
272 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
273 'pn_ledger_id=' || pn_ledger_id || ',' ||
274 'pv_accounting_year=' || pv_accounting_year || ',' ||
275 'pn_coa_id=' || pn_coa_id || ',' ||
276 'pv_period_from=' || pv_period_from || ',' ||
277 'pv_period_to=' || pv_period_to);
278
279 --Populate balancing segment values to table ja_cn_ledger_le_bsv_gt
280 DELETE FROM JA_CN_LEDGER_LE_BSV_GT;
281 COMMIT;
282 IF Ja_Cn_Utility.Populate_Ledger_Le_Bsv_Gt(pn_Ledger_Id,pn_legal_entity_id) <> 'S'
283 THEN
284 RETURN;
285 END IF;
286
287 --Fetch start data and end date
288 IF pv_period_from IS NOT NULL THEN
289 SELECT start_date
290 INTO ld_start_date
291 FROM GL_PERIOD_STATUSES
292 WHERE ledger_id = pn_ledger_id
293 AND application_id = 101
294 AND period_name = pv_period_from
295 AND TO_CHAR(period_year) = pv_accounting_year;
296 ELSE
297 ld_start_date := to_date(pv_accounting_year||'0101','YYYYMMDD');
298 END IF; --pv_period_from IS NOT NULL
299
300 IF pv_period_to IS NOT NULL THEN
301 SELECT end_date
302 INTO ld_end_date
303 FROM GL_PERIOD_STATUSES
304 WHERE ledger_id = pn_ledger_id
305 AND application_id = 101
306 AND period_name = pv_period_to
307 AND TO_CHAR(period_year) = pv_accounting_year;
308 ELSE
309 ld_end_date := to_date(pv_accounting_year||'1231','YYYYMMDD');
310 END IF; --pv_period_to IS NOT NULL
311
312 --logging the variables
313 IF (ln_statement_level >= ln_dbg_level)
314 THEN
315 FND_LOG.STRING(ln_statement_level,
316 lv_procedure_name,
317 'ld_start_date=' || ld_start_date || ',' ||
318 'ld_end_date=' || ld_end_date);
319 END IF; --(ln_statement_level >= ln_dbg_level)
320 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
321 '.variable:' ||
322 'ld_start_date=' || ld_start_date || ',' ||
323 'ld_end_date=' || ld_end_date);
324
325 --Get the Chinese characters of Debit and Credit
326 SELECT MEANING
327 INTO lv_debit
328 FROM FND_LOOKUP_VALUES
329 WHERE LOOKUP_TYPE = 'JA_CN_DEBIT_CREDIT'
330 AND LOOKUP_CODE = 'D'
331 AND LANGUAGE = USERENV('LANG');
332
333 SELECT MEANING
334 INTO lv_credit
335 FROM FND_LOOKUP_VALUES
336 WHERE LOOKUP_TYPE = 'JA_CN_DEBIT_CREDIT'
337 AND LOOKUP_CODE = 'C'
338 AND LANGUAGE = USERENV('LANG');
339
340 --Fetch flex_value_set_id of CN_GL_ACCOUNT
341 SELECT s.flex_value_set_id
342 INTO ln_flex_value_set_id
343 FROM fnd_id_flex_segments s
344 WHERE s.application_id = 101
345 AND s.id_flex_num = pn_coa_id
346 AND s.id_flex_code = 'GL#'
347 AND s.application_column_name =
348 (SELECT application_column_name
349 FROM fnd_segment_attribute_values
350 WHERE application_id = 101
351 AND segment_attribute_type = 'GL_ACCOUNT'
352 AND attribute_value = 'Y'
353 AND id_flex_num = pn_coa_id
354 AND id_flex_code = 'GL#');
355
356 --logging the variables
357 IF (ln_statement_level >= ln_dbg_level)
358 THEN
359 FND_LOG.STRING(ln_statement_level,
360 lv_procedure_name,
361 'ln_flex_value_set_id=' || ln_flex_value_set_id);
362 END IF; --(ln_statement_level >= ln_dbg_level)
363 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
364 '.variable:' ||
365 'ln_flex_value_set_id=' || ln_flex_value_set_id);
366
367 --Fetch which attribute column for stroing balance side and account level
368 OPEN c_in_ja_cn_dff_assignments;
369 LOOP
370 FETCH c_in_ja_cn_dff_assignments
371 INTO l_ja_cn_dff_assignments_row;
372 EXIT WHEN c_in_ja_cn_dff_assignments%NOTFOUND;
373 IF l_ja_cn_dff_assignments_row.context_code IS NOT NULL THEN
374 l_context_code := l_ja_cn_dff_assignments_row.context_code;
375 END IF; --l_ja_cn_dff_assignments_row.context_code IS NOT NULL
376 CASE l_ja_cn_dff_assignments_row.dff_title_code
377 WHEN 'ACBS' THEN
378 l_attribute_column4balanceside := l_ja_cn_dff_assignments_row.attribute_column;
379 WHEN 'ACLE' THEN
380 l_attribute_column4account_lev := l_ja_cn_dff_assignments_row.attribute_column;
381 END CASE; END LOOP;
382 CLOSE c_in_ja_cn_dff_assignments;
383
384 --logging the variables
385 IF (ln_statement_level >= ln_dbg_level)
386 THEN
387 FND_LOG.STRING(ln_statement_level,
388 lv_procedure_name,
389 'l_attribute_column4balanceside=' || l_attribute_column4balanceside || ',' ||
390 'l_attribute_column4account_lev=' || l_attribute_column4account_lev);
391 END IF; --(ln_statement_level >= ln_dbg_level)
392 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
393 '.variable:' ||
394 'l_attribute_column4balanceside=' || l_attribute_column4balanceside || ',' ||
395 'l_attribute_column4account_lev=' || l_attribute_column4account_lev);
396
397 --Clear the JA_CN_SUBSIDIARY_GT temporary table as the Accounting
398 --Balance already inserted data
399 DELETE FROM JA_CN_SUBSIDIARY_GT;
400 COMMIT;
401
402 --Fetch the account subsidiary info for each account and save JA_CN_SUBSIDIARY_GT
403 OPEN c_in_ja_cn_account_balances;
404 --log
405 IF (ln_statement_level >= ln_dbg_level) THEN
406 FND_LOG.STRING(ln_statement_level,
407 GV_MODULE_PREFIX,
408 'Fetched account segments start below:');
409 END IF; --ln_statement_level >= ln_runtime_level
410 lv_sql_stmt := 'insert into JA_CN_SUBSIDIARY_GT
411 (
412 ACCOUNT_SEGMENT_VALUE,
413 BALANCE_SIDE,
414 account_level
415 )
416 select flex_value,' ||
417 nvl(TO_CHAR(l_attribute_column4balanceside), 'null') || ',' ||
418 nvl(TO_CHAR(l_attribute_column4account_lev), 'null') || '
419 from FND_FLEX_VALUES
420 where
421 flex_value_set_id=:1 and
422 flex_value=:2';
423 LOOP
424 FETCH c_in_ja_cn_account_balances
425 INTO l_account_segment;
426 EXIT WHEN c_in_ja_cn_account_balances%NOTFOUND;
427
428 --log
429 IF (ln_statement_level >= ln_dbg_level) THEN
430 FND_LOG.STRING(ln_statement_level, GV_MODULE_PREFIX, l_account_segment);
431 END IF; --ln_statement_level >= ln_runtime_level
432 EXECUTE IMMEDIATE lv_sql_stmt
433 USING ln_flex_value_set_id, l_account_segment;
434 END LOOP;
435
436 --No data found with the requested parameters
437 IF (c_in_ja_cn_account_balances%ROWCOUNT = 0) THEN
438 FND_MESSAGE.set_name('JA', 'JA_CN_NO_DATA_FOUND');
439 lv_message := FND_MESSAGE.get();
440 FND_FILE.put_line(FND_FILE.LOG, lv_message);
441 IF (ln_exception_level >= ln_dbg_level) THEN
442 FND_LOG.STRING(ln_exception_level, GV_MODULE_PREFIX, lv_message);
443 END IF;
444 ln_row_count := 0;
445 END IF; --c_in_ja_cn_account_balances%ROWCOUNT = 0
446 CLOSE c_in_ja_cn_account_balances;
447
448 --Fetch functional currency name
449 SELECT fnd_currencies_vl.NAME
450 INTO l_functional_currency
451 FROM fnd_currencies_vl
452 WHERE currency_code =
453 (SELECT currency_code
454 FROM gl_ledgers
455 WHERE ledger_id = pn_ledger_id);
456
457 --Fetch functional currency code
458 SELECT currency_code
459 INTO l_functional_currency_code
460 FROM gl_ledgers
461 WHERE ledger_id = pn_ledger_id;
462
463 --logging the variables
464 IF (ln_statement_level >= ln_dbg_level)
465 THEN
466 FND_LOG.STRING(ln_statement_level,
467 lv_procedure_name,
468 'l_functional_currency=' || l_functional_currency );
469 END IF; --(ln_statement_level >= ln_dbg_level)
470 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
471 '.variable:' ||
472 'l_functional_currency=' || l_functional_currency );
473
474 --Combine the journal lines and save them to the JA_CN_JOURNAL_LINES_GBL_TMP
475 OPEN c_in_closed_periods;
476 LOOP
477 FETCH c_in_closed_periods
478 INTO l_current_period;
479 EXIT WHEN c_in_closed_periods%NOTFOUND;
480
481 --Fetch start date of current period
482 SELECT start_date
483 INTO ld_current_start_date
484 FROM GL_PERIOD_STATUSES
485 WHERE ledger_id = pn_ledger_id
486 AND application_id = 101
487 AND period_name = l_current_period;
488
489 --log current period and it's start date
490 IF (ln_statement_level >= ln_dbg_level) THEN
491 FND_LOG.STRING(ln_statement_level,
492 GV_MODULE_PREFIX,
493 'about to generate journal lines info for period:' ||
494 l_current_period || ' start date:' ||
495 ld_current_start_date);
496
497 END IF;
498
499 --Insert data to global temp table ja_cn_account_balances_gt
500 OPEN c_in_ja_cn_subsidiary_gbl_tmp;
501 LOOP
502 FETCH c_in_ja_cn_subsidiary_gbl_tmp
503 INTO l_ja_cn_subsidiary_gbl_tmp_row;
504 EXIT WHEN c_in_ja_cn_subsidiary_gbl_tmp%NOTFOUND;
505 --log
506 IF (ln_statement_level >= ln_dbg_level) THEN
507 FND_LOG.STRING(ln_statement_level,
508 GV_MODULE_PREFIX,
509 'about to generate group clause for account:' ||
510 l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value);
511
512 END IF; --l_statement_level >= l_runtime_level
513
514 lv_sql_stmt := lv_sql_stmt4insert_j_line;
515
516 EXECUTE IMMEDIATE lv_sql_stmt
517 USING ld_current_start_date, l_current_period, l_ja_cn_subsidiary_gbl_tmp_row.account_segment_value, pn_ledger_id, pn_legal_entity_id;
518 END LOOP;
519 CLOSE c_in_ja_cn_subsidiary_gbl_tmp;
520 END LOOP;
521
522 CLOSE c_in_closed_periods;
523
524 --Open and loop the Cursor to add the data to XML file
525 FOR v_row IN amount_balance_gt_cur
526 LOOP
527 ln_row_count := ln_row_count + 1;
528
529 --if acount level is not out of range, export
530 IF JA_CN_UTILITY.Check_Account_Level(v_row.Account_Level) THEN
531 --modify the balance amount according to balance side
532 IF v_row.account_type = 'A' OR
533 v_row.account_type = 'E' THEN
534 lv_message := 'D';
535 ELSE
536 lv_message := 'C';
537 END IF;
538 IF v_row.balance_side IS NOT NULL AND
539 lv_message <> v_row.balance_side THEN
540 v_row.func_begin_balance := v_row.func_begin_balance * -1;
541 v_row.orig_begin_balance := v_row.orig_begin_balance * -1;
542 v_row.func_end_balance := v_row.func_end_balance * -1;
543 v_row.orig_end_balance := v_row.orig_end_balance * -1;
544
545 --balance side have the high priority
546 lv_message := v_row.balance_side;
547 END IF; --v_row.balance_side IS NOT NULL
548
549 --Judge DR or CR
550 IF lv_message = 'D' THEN
551 --Dr./Cr.(Beginning Balance)
552 IF v_row.func_begin_balance >= 0 OR
553 v_row.orig_begin_balance >= 0 THEN
554 lv_dr_cr_begin := lv_debit;
555 ELSE
556 lv_dr_cr_begin := lv_credit;
557 END IF;
558 --Dr./Cr.(Ending Balance)
559 IF v_row.func_end_balance >= 0 OR
560 v_row.orig_end_balance >= 0 THEN
561 lv_dr_cr_end := lv_debit;
562 ELSE
563 lv_dr_cr_end := lv_credit;
564 END IF;
565 ELSE
566 --Dr./Cr.(Beginning Balance)
567 IF v_row.func_begin_balance >= 0 OR
568 v_row.orig_begin_balance >= 0 THEN
569 lv_dr_cr_begin := lv_credit;
570 ELSE
571 lv_dr_cr_begin := lv_debit;
572 END IF;
573 --Dr./Cr.(Ending Balance)
574 IF v_row.func_end_balance >= 0 OR
575 v_row.orig_end_balance >= 0 THEN
576 lv_dr_cr_end := lv_credit;
577 ELSE
578 lv_dr_cr_end := lv_debit;
579 END IF;
580 END IF;
581
582 Ja_Cn_Utility.Add_Sub_Root_Node('ACC_PERIOD_AMOUNT_AND_BALANCE',
583 Ja_Cn_Utility.GV_TAG_TYPE_START);
584 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER',
585 v_row.Account_Segment);
586 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO1',
587 Ja_Cn_Utility.Process_Subsidiary_Item(1,v_row.Segment1));
588 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO2',
589 Ja_Cn_Utility.Process_Subsidiary_Item(2,v_row.Segment2));
590 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO3',
591 Ja_Cn_Utility.Process_Subsidiary_Item(3,v_row.Segment3));
592 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO4',
593 Ja_Cn_Utility.Process_Subsidiary_Item(4,v_row.Segment4));
594 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO5',
595 Ja_Cn_Utility.Process_Subsidiary_Item(5,v_row.Segment5));
596 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO6',
597 Ja_Cn_Utility.Process_Subsidiary_Item(6,v_row.Segment6));
598 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO7',
599 Ja_Cn_Utility.Process_Subsidiary_Item(7,v_row.Segment7));
600 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO8',
601 Ja_Cn_Utility.Process_Subsidiary_Item(8,v_row.Segment8));
602 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO9',
603 Ja_Cn_Utility.Process_Subsidiary_Item(9,v_row.Segment9));
604 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO10',
605 Ja_Cn_Utility.Process_Subsidiary_Item(10,v_row.Segment10));
606 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO11',
607 Ja_Cn_Utility.Process_Subsidiary_Item(11,v_row.Segment11));
608 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO12',
609 Ja_Cn_Utility.Process_Subsidiary_Item(12,v_row.Segment12));
610 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO13',
611 Ja_Cn_Utility.Process_Subsidiary_Item(13,v_row.Segment13));
612 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO14',
613 Ja_Cn_Utility.Process_Subsidiary_Item(14,v_row.Segment14));
614 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO15',
615 Ja_Cn_Utility.Process_Subsidiary_Item(15,v_row.Segment15));
616 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO16',
617 Ja_Cn_Utility.Process_Subsidiary_Item(16,v_row.Segment16));
618 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO17',
619 Ja_Cn_Utility.Process_Subsidiary_Item(17,v_row.Segment17));
620 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO18',
621 Ja_Cn_Utility.Process_Subsidiary_Item(18,v_row.Segment18));
622 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO19',
623 Ja_Cn_Utility.Process_Subsidiary_Item(19,v_row.Segment19));
624 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO20',
625 Ja_Cn_Utility.Process_Subsidiary_Item(20,v_row.Segment20));
626 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO21',
627 Ja_Cn_Utility.Process_Subsidiary_Item(21,v_row.Segment21));
628 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO22',
629 Ja_Cn_Utility.Process_Subsidiary_Item(22,v_row.Segment22));
630 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO23',
631 Ja_Cn_Utility.Process_Subsidiary_Item(23,v_row.Segment23));
632 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO24',
633 Ja_Cn_Utility.Process_Subsidiary_Item(24,v_row.Segment24));
634 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO25',
635 Ja_Cn_Utility.Process_Subsidiary_Item(25,v_row.Segment25));
636 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO26',
637 Ja_Cn_Utility.Process_Subsidiary_Item(26,v_row.Segment26));
638 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO27',
639 Ja_Cn_Utility.Process_Subsidiary_Item(27,v_row.Segment27));
640 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO28',
641 Ja_Cn_Utility.Process_Subsidiary_Item(28,v_row.Segment28));
642 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO29',
643 Ja_Cn_Utility.Process_Subsidiary_Item(29,v_row.Segment29));
644 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO30',
645 Ja_Cn_Utility.Process_Subsidiary_Item(30,v_row.Segment30));
646 Ja_Cn_Utility.Add_Child_Node('BEGIN_BALANCE_SIDE',
647 lv_dr_cr_begin);
648 Ja_Cn_Utility.Add_Child_Node('END_BALANCE_SIDE',
649 lv_dr_cr_end);
650 Ja_Cn_Utility.Add_Child_Node('CURRENCY_CODE',
651 v_row.currency_code);
652 Ja_Cn_Utility.Add_Child_Node('UNIT_OF_MEASURE',
653 NULL);
654 Ja_Cn_Utility.Add_Fixed_Child_Node('ACCOUNTING_YEAR',
655 pv_accounting_year,
656 4);
657 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER',
658 v_row.period_mon);
659 Ja_Cn_Utility.Add_Child_Node('BEGINNING_ENTERED_BALANCE',
660 v_row.Orig_Begin_Balance,
661 Ja_Cn_Utility.GV_TYPE_NUMBER);
662 Ja_Cn_Utility.Add_Child_Node('BEGINNING_FUNCTIONAL_BALANCE',
663 v_row.Func_Begin_Balance,
664 Ja_Cn_Utility.GV_TYPE_NUMBER);
665 Ja_Cn_Utility.Add_Child_Node('DEBIT_ENTERED_AMOUNT',
666 v_row.Orig_Period_Net_Dr,
667 Ja_Cn_Utility.GV_TYPE_NUMBER);
668 Ja_Cn_Utility.Add_Child_Node('DEBIT_FUNCTIONAL_AMOUNT',
669 v_row.Func_Period_Net_Dr,
670 Ja_Cn_Utility.GV_TYPE_NUMBER);
671 Ja_Cn_Utility.Add_Child_Node('CREDIT_ENTERED_AMOUNT',
672 v_row.Orig_Period_Net_Cr,
673 Ja_Cn_Utility.GV_TYPE_NUMBER);
674 Ja_Cn_Utility.Add_Child_Node('CREDIT_FUNCTIONAL_AMOUNT',
675 v_row.Func_Period_Net_Cr,
676 Ja_Cn_Utility.GV_TYPE_NUMBER);
677 Ja_Cn_Utility.Add_Child_Node('END_ENTERED_BALANCE',
678 v_row.Orig_End_Balance,
679 Ja_Cn_Utility.GV_TYPE_NUMBER);
680 Ja_Cn_Utility.Add_Child_Node('END_FUNCTIONAL_BALANCE',
681 v_row.Func_End_Balance,
682 Ja_Cn_Utility.GV_TYPE_NUMBER);
683 Ja_Cn_Utility.Add_Sub_Root_Node('ACC_PERIOD_AMOUNT_AND_BALANCE',
684 Ja_Cn_Utility.GV_TAG_TYPE_END);
685 END IF; --if JA_CN_UTILITY.Check_Account_Level(l_account_balances_gbl_tmp_row.balance_side)
686
687 END LOOP; --amount_balance_gt_cur
688
689 -- To judge if the Cursor fetchs data. If the row count is 0 and the
690 -- minOccurs=0 specified in xml schema, then need to handle it and raise exception
691 IF (ln_row_count = 0)
692 THEN
693 Ja_Cn_Utility.Print_No_Data_Found_For_Log('ACC_PERIOD_AMOUNT_AND_BALANCE');
694
695 Ja_Cn_Utility.Add_Sub_Root_Node('ACC_PERIOD_AMOUNT_AND_BALANCE',
696 Ja_Cn_Utility.GV_TAG_TYPE_START);
697 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER',
698 NULL);
699 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO1',
700 NULL);
701 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO2',
702 NULL);
703 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO3',
704 NULL);
705 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO4',
706 NULL);
707 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO5',
708 NULL);
709 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO6',
710 NULL);
711 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO7',
712 NULL);
713 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO8',
714 NULL);
715 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO9',
716 NULL);
717 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO10',
718 NULL);
719 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO11',
720 NULL);
721 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO12',
722 NULL);
723 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO13',
724 NULL);
725 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO14',
726 NULL);
727 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO15',
728 NULL);
729 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO16',
730 NULL);
731 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO17',
732 NULL);
733 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO18',
734 NULL);
735 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO19',
736 NULL);
737 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO20',
738 NULL);
739 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO21',
740 NULL);
741 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO22',
742 NULL);
743 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO23',
744 NULL);
745 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO24',
746 NULL);
747 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO25',
748 NULL);
749 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO26',
750 NULL);
751 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO27',
752 NULL);
753 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO28',
754 NULL);
755 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO29',
756 NULL);
757 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NO30',
758 NULL);
759 Ja_Cn_Utility.Add_Child_Node('BEGIN_BALANCE_SIDE',
760 NULL);
761 Ja_Cn_Utility.Add_Child_Node('END_BALANCE_SIDE',
762 NULL);
763 Ja_Cn_Utility.Add_Child_Node('CURRENCY_CODE',
764 NULL);
765 Ja_Cn_Utility.Add_Child_Node('UNIT_OF_MEASURE',
766 NULL);
767 Ja_Cn_Utility.Add_Fixed_Child_Node('ACCOUNTING_YEAR',
768 NULL,
769 4);
770 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER',
771 NULL);
772 Ja_Cn_Utility.Add_Child_Node('BEGINNING_ENTERED_BALANCE',
773 NULL,
774 Ja_Cn_Utility.GV_TYPE_NUMBER);
775 Ja_Cn_Utility.Add_Child_Node('BEGINNING_FUNCTIONAL_BALANCE',
776 NULL,
777 Ja_Cn_Utility.GV_TYPE_NUMBER);
778 Ja_Cn_Utility.Add_Child_Node('DEBIT_ENTERED_AMOUNT',
779 NULL,
780 Ja_Cn_Utility.GV_TYPE_NUMBER);
781 Ja_Cn_Utility.Add_Child_Node('DEBIT_FUNCTIONAL_AMOUNT',
782 NULL,
783 Ja_Cn_Utility.GV_TYPE_NUMBER);
784 Ja_Cn_Utility.Add_Child_Node('CREDIT_ENTERED_AMOUNT',
785 NULL,
786 Ja_Cn_Utility.GV_TYPE_NUMBER);
787 Ja_Cn_Utility.Add_Child_Node('CREDIT_FUNCTIONAL_AMOUNT',
788 NULL,
789 Ja_Cn_Utility.GV_TYPE_NUMBER);
790 Ja_Cn_Utility.Add_Child_Node('END_ENTERED_BALANCE',
791 NULL,
792 Ja_Cn_Utility.GV_TYPE_NUMBER);
793 Ja_Cn_Utility.Add_Child_Node('END_FUNCTIONAL_BALANCE',
794 NULL,
795 Ja_Cn_Utility.GV_TYPE_NUMBER);
796 Ja_Cn_Utility.Add_Sub_Root_Node('ACC_PERIOD_AMOUNT_AND_BALANCE',
797 Ja_Cn_Utility.GV_TAG_TYPE_END);
798 END IF; --IF (ln_row_count = 0)
799
800 --logging for debug
801 IF (ln_proc_level >= ln_dbg_level)
802 THEN
803 FND_LOG.STRING(ln_proc_level,
804 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
805 'Exit procedure');
806 END IF; -- (ln_proc_level>=ln_dbg_level)
807
808 --To handle the No Data Found Exception
809 EXCEPTION
810 WHEN OTHERS THEN
811 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
812 THEN
813 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
814 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
815 '.Other_Exception ',
816 SQLCODE || SQLERRM);
817 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
818 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
819 RAISE;
820
821 END Add_Account_Amount_Balance;
822
823 END JA_CN_APAB_EXPORT_PKG;