[Home] [Help]
PACKAGE BODY: APPS.JA_CN_POST_UTILITY_PKG
Source
1 PACKAGE BODY JA_CN_POST_UTILITY_PKG AS
2 --$Header: JACNPSTB.pls 120.9.12020000.2 2013/04/11 10:11:01 chongwan ship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNPSTB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used in account and journal itemizatoin to post |
13 --| the CNAO journal to CNAO balance |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE put_line |
17 --| PROCEDURE put_log |
18 --| PROCEDURE post_journal_itemized |
19 --| PROCEDURE open_period |
20 --| |
21 --| HISTORY |
22 --| 02/21/2006 Jogen Hu Created |
23 --| 04/28/2007 Qingjun Zhao change SOB to Ledger for |
24 -- upgrade from 11i to R12 |
25 --| 25/03/2010 Chaoqun Wu Update for posting journals also |
26 -- by customer or supplier |
27 --| 26/03/2010 Chaoqun Wu Add procedure Create_Dynamic_Index|
28 --| 18/05/2010 Chaoqun Wu Fixing bug# 9662105 |
29 --| 27/05/2010 Chaoqun Wu Updated for fixing bug 9582957, |
30 --| Too many accounts are inserted |
31 --| into balance table |
32 --+======================================================================*/
33
34 TYPE cnao_balance_rec IS RECORD
35 ( ledger_id ja_cn_account_balances.ledger_ID%TYPE
36 , LEGAL_ENTITY_ID ja_cn_account_balances.LEGAL_ENTITY_ID%TYPE
37 , COMPANY_SEGMENT ja_cn_account_balances.COMPANY_SEGMENT%TYPE
38 , PERIOD_NAME ja_cn_account_balances.PERIOD_NAME %TYPE
39 , CURRENCY_CODE ja_cn_account_balances.CURRENCY_CODE %TYPE
40 , COST_CENTER ja_cn_account_balances.COST_CENTER %TYPE
41 , THIRD_PARTY_TYPE ja_cn_account_balances.THIRD_PARTY_TYPE %TYPE
42 , THIRD_PARTY_ID ja_cn_account_balances.THIRD_PARTY_ID %TYPE
43 , THIRD_PARTY_NUMBER ja_cn_account_balances.THIRD_PARTY_NUMBER %TYPE
44 , PROJECT_ID ja_cn_account_balances.PROJECT_ID %TYPE
45 , PROJECT_NUMBER ja_cn_account_balances.PROJECT_NUMBER %TYPE
46 , PROJECT_SOURCE ja_cn_account_balances.PROJECT_SOURCE %TYPE
47 , ACCOUNT_SEGMENT ja_cn_account_balances.ACCOUNT_SEGMENT %TYPE
48 , ACCOUNT_type ja_cn_account_balances.ACCOUNT_type %TYPE
49 , PERSONNEL_ID ja_cn_account_balances.PERSONNEL_ID %TYPE
50 , PERSONNEL_NUMBER ja_cn_account_balances.PERSONNEL_NUMBER %TYPE
51 , FUNC_PERIOD_NET_DR ja_cn_account_balances.FUNC_PERIOD_NET_DR %TYPE
52 , FUNC_PERIOD_NET_CR ja_cn_account_balances.FUNC_PERIOD_NET_CR %TYPE
53 , ORIG_PERIOD_NET_DR ja_cn_account_balances.ORIG_PERIOD_NET_DR %TYPE
54 , ORIG_PERIOD_NET_CR ja_cn_account_balances.ORIG_PERIOD_NET_CR %TYPE
55 , PERIOD_MON ja_cn_account_balances.PERIOD_MON %TYPE
56 , segment1 ja_cn_account_balances.SEGMENT1 %TYPE
57 , segment2 ja_cn_account_balances.SEGMENT2 %TYPE
58 , segment3 ja_cn_account_balances.SEGMENT3 %TYPE
59 , segment4 ja_cn_account_balances.SEGMENT4 %TYPE
60 , segment5 ja_cn_account_balances.SEGMENT5 %TYPE
61 , segment6 ja_cn_account_balances.SEGMENT6 %TYPE
62 , segment7 ja_cn_account_balances.SEGMENT7 %TYPE
63 , segment8 ja_cn_account_balances.SEGMENT8 %TYPE
64 , segment9 ja_cn_account_balances.SEGMENT9 %TYPE
65 , segment10 ja_cn_account_balances.SEGMENT10 %TYPE
66 , segment11 ja_cn_account_balances.SEGMENT11 %TYPE
67 , segment12 ja_cn_account_balances.SEGMENT12 %TYPE
68 , segment13 ja_cn_account_balances.SEGMENT13 %TYPE
69 , segment14 ja_cn_account_balances.SEGMENT14 %TYPE
70 , segment15 ja_cn_account_balances.SEGMENT15 %TYPE
71 , segment16 ja_cn_account_balances.SEGMENT16 %TYPE
72 , segment17 ja_cn_account_balances.SEGMENT17 %TYPE
73 , segment18 ja_cn_account_balances.SEGMENT18 %TYPE
74 , segment19 ja_cn_account_balances.SEGMENT19 %TYPE
75 , segment20 ja_cn_account_balances.SEGMENT20 %TYPE
76 , segment21 ja_cn_account_balances.SEGMENT21 %TYPE
77 , segment22 ja_cn_account_balances.SEGMENT22 %TYPE
78 , segment23 ja_cn_account_balances.SEGMENT23 %TYPE
79 , segment24 ja_cn_account_balances.SEGMENT24 %TYPE
80 , segment25 ja_cn_account_balances.SEGMENT25 %TYPE
81 , segment26 ja_cn_account_balances.SEGMENT26 %TYPE
82 , segment27 ja_cn_account_balances.SEGMENT27 %TYPE
83 , segment28 ja_cn_account_balances.SEGMENT28 %TYPE
84 , segment29 ja_cn_account_balances.SEGMENT29 %TYPE
85 , segment30 ja_cn_account_balances.SEGMENT30 %TYPE
86 );
87
88 G_MODULE_PREFIX VARCHAR2(30):='JA_CN_POST_UTILITY_PKG.';
89 G_PROC_LEVEL INT :=fnd_log.LEVEL_PROCEDURE;
90 G_STATEMENT_LEVEL INT :=fnd_log.LEVEL_STATEMENT;
91 g_debug_devel INT;
92
93 --==========================================================================
94 -- PROCEDURE NAME:
95 -- Put_Line private
96 --
97 -- DESCRIPTION:
98 -- This procedure write data to concurrent output file.
99 --
100 -- PARAMETERS:
101 -- In: p_str VARCHAR2
102 --
103 -- DESIGN REFERENCES:
104 -- None
105 --
106 -- CHANGE HISTORY:
107 -- 02/21/2006 Jogen Hu Created
108 --===========================================================================
109 PROCEDURE put_line
110 ( p_str IN VARCHAR2
111 )
112 IS
113 BEGIN
114 FND_FILE.Put_Line(FND_FILE.Output,p_str);
115 END put_line;
116
117 --==========================================================================
118 -- PROCEDURE NAME:
119 -- Put_Line private
120 --
121 -- DESCRIPTION:
122 -- This procedure write data to log file.
123 --
124 -- PARAMETERS:
125 -- In: p_str VARCHAR2
126 --
127 -- DESIGN REFERENCES:
128 -- None
129 --
130 -- CHANGE HISTORY:
131 -- 02/21/2006 Jogen Hu Created
132 --===========================================================================
133 PROCEDURE put_log
134 ( p_module IN VARCHAR2
135 , p_message IN VARCHAR2
136 )
137 IS
138 BEGIN
139 --fnd_file.PUT_LINE(fnd_file.LOG,p_module||':'||p_message);
140 IF( fnd_log.LEVEL_STATEMENT >= g_debug_devel )
141 THEN
142 fnd_log.STRING( LOG_LEVEL => fnd_log.LEVEL_STATEMENT
143 , MODULE => p_module
144 , MESSAGE => p_message
145 );
146 END IF;
147 END put_log;
148
149 --==========================================================================
150 -- PROCEDURE NAME:
151 -- open_period private
152 --
153 -- DESCRIPTION:
154 -- This procedure is used to open a period which had never post
155 -- journal from "Itemized journal table" to "Itemized balance table".
156 -- if the period is the first period of the fiscal year, transfer the
157 -- income and expense account to retained earnings account
158 --
159 -- PARAMETERS:
160 -- In: p_period_name the period name needing to open
161 -- p_ledger_id Set of book ID
162 -- p_legal_entity_ID Legal entity id
163
164 --
165 -- DESIGN REFERENCES:
166 -- None
167 --
168 -- CHANGE HISTORY:
169 -- 02/21/2006 Jogen Hu Created
170 -- 04/28/2007 Qingjun Zhao Change SOB to Ledger for upgrade
171 -- from 11i to R12
172 --===========================================================================
173 PROCEDURE open_period
174 ( p_period_name IN VARCHAR2
175 , p_ledger_id IN NUMBER
176 , p_legal_entity_ID IN NUMBER
177 )
178 IS
179 l_procedure_name VARCHAR2(30):='open_period';
180 l_row_count NUMBER;
181 --l_period_set_name GL_PERIODS.PERIOD_SET_NAME%TYPE;
182 l_period_wrong EXCEPTION;
183 l_flex_value_set_id FND_FLEX_VALUE_SETS.Flex_Value_Set_Id%TYPE;
184 l_period_year gl_periods.period_year%TYPE;
185 l_period_num ja_cn_periods.period_num%TYPE;
186 l_first_fiscal_period_flag CHAR(1);
187 l_prior_period_name gl_periods.period_name%TYPE;
188 l_prior_period_num ja_cn_periods.period_num%TYPE;
189
190 l_period_month ja_cn_account_balances.period_mon%TYPE;
191 /*l_RET_EARN_CODE_COMBINATION_ID gl_ledgers.ret_earn_code_combination_id%TYPE;
192
193 l_ret_company_segment ja_cn_account_balances.company_segment%TYPE;
194 l_ret_cost_center_seg ja_cn_account_balances.cost_center%TYPE;
195 l_ret_account_segment ja_cn_account_balances.account_segment%TYPE;
196 l_ret_project_number ja_cn_account_balances.company_segment%TYPE;
197 l_ret_project_id ja_cn_account_balances.cost_center%TYPE;
198 l_ret_currence_code ja_cn_account_balances.currency_code%TYPE;
199
200 l_ret_acct_balance_dr gl_balances.begin_balance_dr%TYPE;
201 l_ret_acct_balance_cr gl_balances.begin_balance_cr%TYPE;
202 l_ret_acct_balance_dr_beq gl_balances.begin_balance_dr_beq%TYPE;
203 l_ret_acct_balance_cr_beq gl_balances.begin_balance_cr_beq%TYPE;
204
205 l_ret_account_type ja_cn_account_balances.account_type%TYPE;
206
207 l_cost_center_second_tracking BOOLEAN:=FALSE;
208
209 --get retained earning account segements: company, account, cost center
210 CURSOR c_retain_account1(pc_RET_EARN_CODE_COMBIN_ID IN NUMBER
211 ,pc_flex_value_set_id IN NUMBER) IS
212 SELECT jcc.company_segment
213 , jcc.account_segment
214 , jcc.cost_segment
215 , jcc.project_number
216 , jcc.project_id
217 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1) account_type
218 FROM ja_cn_code_combination_v jcc
219 , FND_FLEX_VALUES ffv
220 WHERE jcc.ledger_id = p_ledger_id
221 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
222 AND ffv.FLEX_VALUE_SET_ID = pc_flex_value_set_id
223 AND ffv.flex_value = jcc.account_segment
224 AND (jcc.company_segment, jcc.account_segment, jcc.cost_segment) IN
225 (SELECT jcc1.company_segment
226 , jcc1.account_segment
227 , jcc1.cost_segment
228 FROM ja_cn_code_combination_v jcc1
229 WHERE jcc1.CODE_COMBINATION_ID = pc_RET_EARN_CODE_COMBIN_ID
230 AND jcc1.ledger_id = p_ledger_id);
231
232 --get retained earning account segements: company, account, cost center
233 CURSOR c_retain_account2(pc_RET_EARN_CODE_COMBIN_ID IN NUMBER
234 ,pc_flex_value_set_id IN NUMBER) IS
235 SELECT jcc.company_segment
236 , jcc.account_segment
237 , jcc.cost_segment
238 , jcc.project_number
239 , jcc.project_id
240 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1) account_type
241 FROM ja_cn_code_combination_v jcc
242 , FND_FLEX_VALUES ffv
243 WHERE jcc.ledger_id = p_ledger_id
244 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
245 AND ffv.FLEX_VALUE_SET_ID = pc_flex_value_set_id
246 AND ffv.flex_value = jcc.account_segment
247 AND (jcc.company_segment, jcc.account_segment) IN
248 (SELECT jcc1.company_segment
249 , jcc1.account_segment
250 FROM ja_cn_code_combination_v jcc1
251 WHERE jcc1.CODE_COMBINATION_ID = pc_RET_EARN_CODE_COMBIN_ID
252 AND jcc1.ledger_id = p_ledger_id);*/
253
254 BEGIN
255
256 IF( G_PROC_LEVEL >= g_debug_devel )
257 THEN
258 FND_LOG.STRING(G_PROC_LEVEL
259 ,G_MODULE_PREFIX||l_procedure_name||'.begin'
260 ,'Enter procedure');
261 END IF; --( G_PROC_LEVEL >= g_debug_devel )
262
263 put_log('open_period parameter',p_period_name);
264
265 SELECT ifs.flex_value_set_id
266 INTO l_flex_value_set_id
267 FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
268 , gl_ledgers led
269 , Fnd_Id_Flex_Segments ifs
270 WHERE sav.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
271 AND sav.ID_FLEX_CODE = 'GL#'
272 AND sav.APPLICATION_ID = 101
273 AND sav.attribute_value = 'Y'
274 AND sav.ID_FLEX_NUM = led.chart_of_accounts_id
275 AND ifs.application_id = 101
276 AND ifs.Id_Flex_Code = 'GL#'
277 AND ifs.id_flex_num = led.chart_of_accounts_id
278 AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
279 AND led.ledger_id = p_ledger_id;
280
281 IF( G_STATEMENT_LEVEL >= g_debug_devel )
282 THEN
283 put_log( G_MODULE_PREFIX||l_procedure_name||'.flex_value_set_id'
284 , l_flex_value_set_id);
285 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel )
286
287 SELECT COUNT(*)
288 INTO l_row_count
289 FROM JA_CN_PERIODS
290 WHERE period_name = p_period_name
291 AND ledger_id=p_ledger_id;
292
293 IF l_row_count = 0 --The period is not processed before
294 THEN
295 --open the period
296 INSERT INTO JA_CN_PERIODS
297 ( ledger_id
298 , START_DATE
299 , END_DATE
300 , PERIOD_NAME
301 , PERIOD_NUM
302 , PERIOD_YEAR
303 , FIRST_FISCAL_PERIOD_FLAG
304 , STATUS
305 , CREATION_DATE
306 , CREATED_BY
307 , LAST_UPDATE_DATE
308 , LAST_UPDATED_BY
309 , LAST_UPDATE_LOGIN
310 )
311 SELECT p_ledger_id
312 , gp.start_date
313 , gp.end_date
314 , p_period_name
315 , gp.period_num+gp.period_year*1000
316 , gp.period_year
317 , decode(gp.period_num,1,'Y','N')
318 , 'O'
319 , SYSDATE
320 , fnd_global.USER_ID
321 , SYSDATE
322 , fnd_global.USER_ID
323 , fnd_global.LOGIN_ID
324 FROM gl_ledgers led
325 , gl_periods gp
326 WHERE led.ledger_id=p_ledger_id
327 AND led.period_set_name = gp.period_set_name
328 AND gp.period_name = p_period_name;
329
330 IF SQL%ROWCOUNT = 0 --the given set of book ID or period name error
331 THEN
332 RAISE l_period_wrong;
333 END IF;
334
335 --get the prior period
336 SELECT gp.period_year
337 , gp.period_num
338 , decode(gp.period_num,1,'Y','N')
339 INTO l_period_year
340 , l_period_num
341 , l_first_fiscal_period_flag
342 FROM gl_ledgers led
343 , gl_periods gp
344 WHERE led.ledger_id=p_ledger_id
345 AND led.period_set_name = gp.period_set_name
346 AND gp.period_name = p_period_name;
347
348 l_period_month := l_period_num;
349
350 IF( G_STATEMENT_LEVEL >= g_debug_devel )
351 THEN
352 put_log( G_MODULE_PREFIX||l_procedure_name||'.first_fiscal_period_flag'
353 , l_first_fiscal_period_flag);
354 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel )
355
356 SELECT MAX(period_num)
357 INTO l_prior_period_num
358 FROM JA_CN_PERIODS jjb
359 WHERE jjb.ledger_id=p_ledger_id
360 AND jjb.period_num <l_period_year*1000+l_period_num;
361
362 IF( G_STATEMENT_LEVEL >= g_debug_devel )
363 THEN
364 put_log( G_MODULE_PREFIX||l_procedure_name||'.prior_period_num'
365 , l_prior_period_num);
366 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel )
367
368 --first time to open period, needn't transfer prior periond end balance
369 IF l_prior_period_num IS NOT NULL
370 THEN
371 SELECT period_name
372 INTO l_prior_period_name
373 FROM JA_CN_PERIODS jjb
374 WHERE jjb.ledger_id=p_ledger_id
375 AND jjb.period_num =l_prior_period_num;
376
377 --transfer prior period end balance the period begin balance
378 INSERT INTO ja_cn_account_balances(
379 ledger_id
380 , legal_entity_id
381 , company_segment
382 , period_name
383 , currency_code
384 , cost_center
385 , third_party_type
386 , third_party_id
387 , third_party_number
388 , project_id
389 , project_number
390 , project_source
391 , account_segment
392 , account_type
393 , personnel_id
394 , personnel_number
395 , SEGMENT1
396 , SEGMENT2
397 , SEGMENT3
398 , SEGMENT4
399 , SEGMENT5
400 , SEGMENT6
401 , SEGMENT7
402 , SEGMENT8
403 , SEGMENT9
404 , SEGMENT10
405 , SEGMENT11
406 , SEGMENT12
407 , SEGMENT13
408 , SEGMENT14
409 , SEGMENT15
410 , SEGMENT16
411 , SEGMENT17
412 , SEGMENT18
413 , SEGMENT19
414 , SEGMENT20
415 , SEGMENT21
416 , SEGMENT22
417 , SEGMENT23
418 , SEGMENT24
419 , SEGMENT25
420 , SEGMENT26
421 , SEGMENT27
422 , SEGMENT28
423 , SEGMENT29
424 , SEGMENT30
425 , func_begin_balance_dr
426 , func_begin_balance_cr
427 , orig_begin_balance_dr
428 , Orig_Begin_Balance_Cr
429 , func_period_net_dr
430 , func_period_net_cr
431 , Orig_Period_Net_Dr
432 , Orig_Period_Net_Cr
433 , period_mon
434 , created_by
435 , creation_date
436 , last_updated_by
437 , last_update_date
438 , last_update_login
439 )
440 SELECT ledger_id
441 , legal_entity_id
442 , company_segment
443 , p_period_name
444 , currency_code
445 , cost_center
446 , third_party_type
447 , third_party_id
448 , third_party_number
449 , project_id
450 , project_number
451 , project_source
452 , account_segment
453 , account_type
454 , personnel_id
455 , personnel_number
456 , SEGMENT1
457 , SEGMENT2
458 , SEGMENT3
459 , SEGMENT4
460 , SEGMENT5
461 , SEGMENT6
462 , SEGMENT7
463 , SEGMENT8
464 , SEGMENT9
465 , SEGMENT10
466 , SEGMENT11
467 , SEGMENT12
468 , SEGMENT13
469 , SEGMENT14
470 , SEGMENT15
471 , SEGMENT16
472 , SEGMENT17
473 , SEGMENT18
474 , SEGMENT19
475 , SEGMENT20
476 , SEGMENT21
477 , SEGMENT22
478 , SEGMENT23
479 , SEGMENT24
480 , SEGMENT25
481 , SEGMENT26
482 , SEGMENT27
483 , SEGMENT28
484 , SEGMENT29
485 , SEGMENT30
486 , func_end_Balance_dr
487 , func_end_balance_cr
488 , orig_end_balance_dr
489 , orig_end_balance_cr
490 , 0
491 , 0
492 , 0
493 , 0
494 , l_period_month
495 , fnd_global.USER_ID
496 , SYSDATE
497 , fnd_global.USER_ID
498 , SYSDATE
499 , fnd_global.LOGIN_ID
500 FROM ja_cn_account_balances_v a
501 WHERE a.ledger_id = p_ledger_id
502 AND a.legal_entity_id = p_legal_entity_ID
503 AND a.period_name = l_prior_period_name;
504
505 IF l_first_fiscal_period_flag='Y'
506 --transfer the income and expense account to retained earnings account
507 THEN
508 --set the non-retained earning account balance to zero
509 UPDATE ja_cn_account_balances jab
510 SET func_begin_balance_dr = 0
511 , func_begin_balance_cr = 0
512 , orig_begin_balance_dr = 0
513 , Orig_Begin_Balance_Cr = 0
514 WHERE ledger_id = p_ledger_id
515 AND legal_entity_id = p_legal_entity_ID
516 AND period_name = p_period_name
517 AND EXISTS(SELECT *
518 FROM FND_FLEX_VALUES ffv
519 WHERE PARENT_FLEX_VALUE_LOW IS NULL
520 AND FLEX_VALUE_SET_ID = l_flex_value_set_id
521 AND ffv.flex_value = jab.account_segment
522 AND substr(COMPILED_VALUE_ATTRIBUTES,5,1) IN ('R','E')
523 );
524 /*
525 --get retain ccid and base currency code
526 SELECT sob.RET_EARN_CODE_COMBINATION_ID
527 , sob.currency_code
528 INTO l_RET_EARN_CODE_COMBINATION_ID
529 , l_ret_currence_code
530 FROM gl_ledgers sob
531 WHERE sob.ledger_id = p_ledger_id;
532
533 SELECT COUNT(*)
534 INTO l_row_count
535 FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
536 , gl_ledgers sob
537 , Fnd_Id_Flex_Segments ifs
538 WHERE sav.ID_FLEX_CODE = 'GL#'
539 AND sav.APPLICATION_ID = 101
540 AND sav.attribute_value = 'Y'
541 AND sav.ID_FLEX_NUM = sob.chart_of_accounts_id
542 AND ifs.application_id = 101
543 AND ifs.Id_Flex_Code = 'GL#'
544 AND ifs.id_flex_num = sob.chart_of_accounts_id
545 AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
546 AND sob.ledger_id = p_ledger_id
547 AND sav.SEGMENT_ATTRIBUTE_TYPE IN ('FA_COST_CTR','GL_SECONDARY_TRACKING');
548
549 IF l_row_count>=2
550 THEN
551 l_cost_center_second_tracking := TRUE;
552 END IF;
553
554 --get retained earning account segements: company, account, cost center
555 SELECT jcc.company_segment
556 , jcc.account_segment
557 , jcc.cost_segment
558 , jcc.project_number
559 , jcc.project_id
560 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
561 INTO l_ret_company_segment
562 , l_ret_account_segment
563 , l_ret_cost_center_seg
564 , l_ret_project_number
565 , l_ret_project_id
566 , l_ret_account_type
567 FROM ja_cn_code_combination_v jcc
568 , FND_FLEX_VALUES ffv
569 WHERE jcc.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
570 AND jcc.ledger_id = p_ledger_id
571 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
572 AND ffv.FLEX_VALUE_SET_ID = l_flex_value_set_id
573 AND ffv.flex_value = jcc.account_segment;
574
575 IF l_cost_center_second_tracking = TRUE
576 THEN
577 FOR rec_retain_accout1 IN c_retain_account1
578 LOOP
579 /*BEGIN
580 --get retain ccid and its balance
581 SELECT sob.RET_EARN_CODE_COMBINATION_ID
582 , gb.begin_balance_dr
583 , gb.begin_balance_cr
584 , gb.begin_balance_dr_beq
585 , gb.begin_balance_cr_beq
586 , gb.currency_code
587 INTO l_RET_EARN_CODE_COMBINATION_ID
588 , l_ret_acct_balance_dr
589 , l_ret_acct_balance_cr
590 , l_ret_acct_balance_dr_beq
591 , l_ret_acct_balance_cr_beq
592 , l_ret_currence_code
593 FROM gl_ledgers sob
594 , gl_balances gb
595 WHERE sob.ledger_id = p_ledger_id
596 AND sob.ret_earn_code_combination_id = gb.code_combination_id
597 AND gb.ledger_id = p_ledger_id
598 AND gb.period_name = p_period_name
599 AND gb.actual_flag = 'A'
600 AND gb.currency_code = sob.currency_code;
601
602 --get retained earning account segements: company, account, cost center
603 SELECT jcc.company_segment
604 , jcc.account_segment
605 , jcc.cost_segment
606 , jcc.project_number
607 , jcc.project_id
608 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
609 INTO l_ret_company_segment
610 , l_ret_account_segment
611 , l_ret_cost_center_seg
612 , l_ret_project_number
613 , l_ret_project_id
614 , l_ret_account_type
615 FROM ja_cn_code_combination_v jcc
616 , FND_FLEX_VALUES ffv
617 WHERE jcc.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
618 AND jcc.ledger_id = p_ledger_id
619 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
620 AND ffv.FLEX_VALUE_SET_ID = l_flex_value_set_id
621 AND ffv.flex_value = jcc.account_segment;
622
623 --change the retained earning account balance
624 UPDATE ja_cn_account_balances
625 SET func_begin_balance_dr = l_ret_acct_balance_dr_beq
626 , func_begin_balance_cr = l_ret_acct_balance_cr_beq
627 , orig_begin_balance_dr = l_ret_acct_balance_dr
628 , Orig_Begin_Balance_Cr = l_ret_acct_balance_cr
629 , last_updated_by = fnd_global.USER_ID
630 , last_update_date = SYSDATE
631 , last_update_login = fnd_global.LOGIN_ID
632 WHERE ledger_id = p_ledger_id
633 AND legal_entity_id = p_legal_entity_ID
634 AND company_segment = l_ret_company_segment
635 AND period_name = p_period_name
636 AND currency_code = l_ret_currence_code
637 AND cost_center = l_ret_cost_center_seg
638 -- AND project_id = l_ret_project_id
639 AND nvl(project_number,'0')= nvl(l_ret_project_number,'0')
640 AND account_segment = l_ret_account_segment;
641
642 --first period after EBS run
643 IF SQL%ROWCOUNT = 0
644 THEN
645 INSERT INTO ja_cn_account_balances(
646 ledger_id
647 , LEGAL_ENTITY_ID
648 , COMPANY_SEGMENT
649 , PERIOD_NAME
650 , CURRENCY_CODE
651 , COST_CENTER
652 , THIRD_PARTY_TYPE
653 , THIRD_PARTY_ID
654 , THIRD_PARTY_NUMBER
655 , PROJECT_ID
656 , PROJECT_NUMBER
657 , PROJECT_SOURCE
658 , ACCOUNT_SEGMENT
659 , account_type
660 , PERSONNEL_ID
661 , PERSONNEL_NUMBER
662 , FUNC_BEGIN_BALANCE_DR
663 , FUNC_BEGIN_BALANCE_CR
664 , ORIG_BEGIN_BALANCE_DR
665 , ORIG_BEGIN_BALANCE_CR
666 , FUNC_PERIOD_NET_DR
667 , FUNC_PERIOD_NET_CR
668 , ORIG_PERIOD_NET_DR
669 , ORIG_PERIOD_NET_CR
670 , PERIOD_MON
671 , CREATED_BY
672 , CREATION_DATE
673 , LAST_UPDATED_BY
674 , LAST_UPDATE_DATE
675 , LAST_UPDATE_LOGIN )
676 VALUES( p_ledger_id
677 , p_legal_entity_ID
678 , l_ret_company_segment
679 , p_period_name
680 , l_ret_currence_code
681 , l_ret_cost_center_seg
682 , NULL
683 , NULL
684 , NULL
685 , l_ret_project_id
686 , l_ret_project_number
687 , 'COA'
688 , l_ret_account_segment
689 , l_ret_account_type
690 , NULL
691 , NULL
692 , l_ret_acct_balance_dr_beq
693 , l_ret_acct_balance_cr_beq
694 , l_ret_acct_balance_dr
695 , l_ret_acct_balance_cr
696 , 0
697 , 0
698 , 0
699 , 0
700 , l_period_month
701 , fnd_global.USER_ID
702 , SYSDATE
703 , fnd_global.USER_ID
704 , SYSDATE
705 , fnd_global.LOGIN_ID);
706 END IF;
707 EXCEPTION --get retain ccid and its balance
708 WHEN NO_DATA_FOUND THEN
709 IF( G_PROC_LEVEL >= g_debug_devel )
710 THEN
711 put_log( G_MODULE_PREFIX||l_procedure_name||'.end'
712 ,'The retain earning account is not startup in period '
713 ||p_period_name);
714 END IF; --( G_PROC_LEVEL >= g_debug_devel)
715
716 END; --get retain ccid and its balance
717 END LOOP;
718 END IF;--l_cost_center_second_tracking = TRUE */
719 END IF;--l_first_fiscal_period_flag='Y'
720 END IF; --l_prior_period_num IS NULL
721 END IF; --l_row_count = 0
722
723 IF( G_PROC_LEVEL >= g_debug_devel )
724 THEN
725 FND_LOG.STRING(G_PROC_LEVEL
726 ,G_MODULE_PREFIX||l_procedure_name||'.end'
727 ,'End procedure');
728 END IF; --( G_PROC_LEVEL >= g_debug_devel)
729 EXCEPTION
730 WHEN l_period_wrong THEN
731 IF(FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel)
732 THEN
733 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
734 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
735 , 'The input period is invalid.');
736 END IF;
737 RAISE;
738
739 WHEN OTHERS THEN
740 IF(FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel)
741 THEN
742 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
743 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
744 , SQLCODE||SQLERRM);
745 END IF;
746 RAISE;
747
748 END open_period;
749
750 --==========================================================================
751 -- PROCEDURE NAME:
752 -- update_balance Private
753 --
754 -- DESCRIPTION:
755 -- Update CNAO balance table
756 --
757 -- PARAMETERS:
758 -- In: p_balance_rec cnao_balance_rec
759 -- p_current_period_flag whether update period is current period
760 --
761 -- DESIGN REFERENCES:
762 -- None
763 --
764 -- CHANGE HISTORY:
765 -- 05/24/2006 Jogen Hu Created
766 -- 04/28/2007 Qingjun Zhao Change SOB to Ledger for upgrade
767 -- from 11i to R12
768 -- 05/02/2010 Shujuan Yan Change for CNAO V2
769 -- 25/03/2010 Chaoqun Wu Update for updating balance also by customer
770 -- or supplier
771 --===========================================================================
772 PROCEDURE update_balance
773 ( p_balance_rec IN cnao_balance_rec
774 , p_current_period_flag IN VARCHAR2
775 )
776 IS
777 l_procedure_name VARCHAR2(20):='update_balance';
778 BEGIN
779 IF( G_PROC_LEVEL >= g_debug_devel )
780 THEN
781 FND_LOG.STRING(G_PROC_LEVEL
782 ,G_MODULE_PREFIX||l_procedure_name||'.begin'
783 ,'Begin procedure');
784 END IF; --( G_PROC_LEVEL >= g_debug_devel)
785
786 IF( G_STATEMENT_LEVEL >= g_debug_devel )
787 THEN
788 put_log( G_MODULE_PREFIX||l_procedure_name||'.update_balance'
789 , p_balance_rec.PERIOD_NAME||':'||p_balance_rec.PERIOD_MON);
790 END IF;
791
792 --if the period in parameter is the journal's period
793 IF p_current_period_flag='Y'
794 THEN
795 UPDATE ja_cn_account_balances
796 SET FUNC_PERIOD_NET_DR = FUNC_PERIOD_NET_DR + p_balance_rec.FUNC_PERIOD_NET_DR
797 , FUNC_PERIOD_NET_CR = FUNC_PERIOD_NET_CR + p_balance_rec.FUNC_PERIOD_NET_CR
798 , ORIG_PERIOD_NET_DR = ORIG_PERIOD_NET_DR + p_balance_rec.ORIG_PERIOD_NET_DR
799 , ORIG_PERIOD_NET_CR = ORIG_PERIOD_NET_CR + p_balance_rec.ORIG_PERIOD_NET_CR
800 , LAST_UPDATED_BY = fnd_global.USER_ID
801 , LAST_UPDATE_DATE = SYSDATE
802 , LAST_UPDATE_LOGIN = fnd_global.login_id
803 WHERE ledger_id = p_balance_rec.ledger_id
804 AND LEGAL_ENTITY_ID = p_balance_rec.LEGAL_ENTITY_ID
805 AND COMPANY_SEGMENT = p_balance_rec.COMPANY_SEGMENT
806 AND PERIOD_NAME = p_balance_rec.PERIOD_NAME
807 AND CURRENCY_CODE = p_balance_rec.CURRENCY_CODE
808 /*AND nvl(COST_CENTER,0) = nvl(p_balance_rec.COST_CENTER,0)*/
809 AND ACCOUNT_SEGMENT = p_balance_rec.ACCOUNT_SEGMENT
810 AND nvl(THIRD_PARTY_TYPE ,0) = nvl(p_balance_rec.THIRD_PARTY_TYPE ,0) -- Update for updating balance also by customer or supplier
811 AND nvl(THIRD_PARTY_ID ,0) = nvl(p_balance_rec.THIRD_PARTY_ID ,0)
812 /*AND nvl(THIRD_PARTY_NUMBER,0) = nvl(p_balance_rec.THIRD_PARTY_NUMBER,0)
813 AND nvl(PROJECT_ID ,0) = nvl(p_balance_rec.PROJECT_ID ,0)
814 AND nvl(PROJECT_NUMBER ,0) = nvl(p_balance_rec.PROJECT_NUMBER ,0)
815 AND nvl(PROJECT_SOURCE ,0) = nvl(p_balance_rec.PROJECT_SOURCE ,0)
816 AND nvl(PERSONNEL_ID ,0) = nvl(p_balance_rec.PERSONNEL_ID ,0)
817 AND nvl(PERSONNEL_NUMBER ,0) = nvl(p_balance_rec.PERSONNEL_NUMBER ,0)
818 AND nvl(PERIOD_MON ,0) = nvl(p_balance_rec.PERIOD_MON ,0)*/
819 -- Add for CNAO V2
820 AND nvl(SEGMENT1 ,0) = nvl(p_balance_rec.SEGMENT1 ,0)
821 AND nvl(SEGMENT2 ,0) = nvl(p_balance_rec.SEGMENT2 ,0)
822 AND nvl(SEGMENT3 ,0) = nvl(p_balance_rec.SEGMENT3 ,0)
823 AND nvl(SEGMENT4 ,0) = nvl(p_balance_rec.SEGMENT4 ,0)
824 AND nvl(SEGMENT5 ,0) = nvl(p_balance_rec.SEGMENT5 ,0)
825 AND nvl(SEGMENT6 ,0) = nvl(p_balance_rec.SEGMENT6 ,0)
826 AND nvl(SEGMENT7 ,0) = nvl(p_balance_rec.SEGMENT7 ,0)
827 AND nvl(SEGMENT8 ,0) = nvl(p_balance_rec.SEGMENT8 ,0)
828 AND nvl(SEGMENT9 ,0) = nvl(p_balance_rec.SEGMENT9 ,0)
829 AND nvl(SEGMENT10 ,0) = nvl(p_balance_rec.SEGMENT10 ,0)
830 AND nvl(SEGMENT11 ,0) = nvl(p_balance_rec.SEGMENT11 ,0)
831 AND nvl(SEGMENT12 ,0) = nvl(p_balance_rec.SEGMENT12 ,0)
832 AND nvl(SEGMENT13 ,0) = nvl(p_balance_rec.SEGMENT13 ,0)
833 AND nvl(SEGMENT14 ,0) = nvl(p_balance_rec.SEGMENT14 ,0)
834 AND nvl(SEGMENT15 ,0) = nvl(p_balance_rec.SEGMENT15 ,0)
835 AND nvl(SEGMENT16 ,0) = nvl(p_balance_rec.SEGMENT16 ,0)
836 AND nvl(SEGMENT17 ,0) = nvl(p_balance_rec.SEGMENT17 ,0)
837 AND nvl(SEGMENT18 ,0) = nvl(p_balance_rec.SEGMENT18 ,0)
838 AND nvl(SEGMENT19 ,0) = nvl(p_balance_rec.SEGMENT19 ,0)
839 AND nvl(SEGMENT20 ,0) = nvl(p_balance_rec.SEGMENT20 ,0)
840 AND nvl(SEGMENT21 ,0) = nvl(p_balance_rec.SEGMENT21 ,0)
841 AND nvl(SEGMENT22 ,0) = nvl(p_balance_rec.SEGMENT22 ,0)
842 AND nvl(SEGMENT23 ,0) = nvl(p_balance_rec.SEGMENT23 ,0)
843 AND nvl(SEGMENT24 ,0) = nvl(p_balance_rec.SEGMENT24 ,0)
844 AND nvl(SEGMENT25 ,0) = nvl(p_balance_rec.SEGMENT25 ,0)
845 AND nvl(SEGMENT26 ,0) = nvl(p_balance_rec.SEGMENT26 ,0)
846 AND nvl(SEGMENT27 ,0) = nvl(p_balance_rec.SEGMENT27 ,0)
847 AND nvl(SEGMENT28 ,0) = nvl(p_balance_rec.SEGMENT28 ,0)
848 AND nvl(SEGMENT29 ,0) = nvl(p_balance_rec.SEGMENT29 ,0)
849 AND nvl(SEGMENT30 ,0) = nvl(p_balance_rec.SEGMENT30 ,0);
850
851 --first time balance
852 IF SQL%ROWCOUNT = 0
853 THEN
854
855 IF( G_STATEMENT_LEVEL >= g_debug_devel )
856 THEN
857
858 put_log( G_MODULE_PREFIX||l_procedure_name||'.update_balance'
859 , p_balance_rec.ledger_id
860 ||':'|| p_balance_rec.LEGAL_ENTITY_ID
861 ||':'|| p_balance_rec.COMPANY_SEGMENT
862 ||':'|| p_balance_rec.PERIOD_NAME
863 ||':'|| p_balance_rec.CURRENCY_CODE
864 -- ||':'|| p_balance_rec.COST_CENTER
865 ||':'|| p_balance_rec.THIRD_PARTY_TYPE --Update for updating balance also by customer or supplier
866 ||':'|| p_balance_rec.THIRD_PARTY_ID
867 -- ||':'|| p_balance_rec.THIRD_PARTY_NUMBER
868 -- ||':'|| p_balance_rec.PROJECT_ID
869 -- ||':'|| p_balance_rec.PROJECT_NUMBER
870 -- ||':'|| p_balance_rec.PROJECT_SOURCE
871 ||':'|| p_balance_rec.ACCOUNT_SEGMENT
872 ||':'|| p_balance_rec.ACCOUNT_type
873 ||':'|| p_balance_rec.PERSONNEL_ID
874 ||':'|| p_balance_rec.PERSONNEL_NUMBER
875 -- Add for CNAO V2
876 ||':'|| p_balance_rec.SEGMENT1
877 ||':'|| p_balance_rec.SEGMENT2
878 ||':'|| p_balance_rec.SEGMENT3
879 ||':'|| p_balance_rec.SEGMENT4
880 ||':'|| p_balance_rec.SEGMENT5
881 ||':'|| p_balance_rec.SEGMENT6
882 ||':'|| p_balance_rec.SEGMENT7
883 ||':'|| p_balance_rec.SEGMENT8
884 ||':'|| p_balance_rec.SEGMENT9
885 ||':'|| p_balance_rec.SEGMENT10
886 ||':'|| p_balance_rec.SEGMENT11
887 ||':'|| p_balance_rec.SEGMENT12
888 ||':'|| p_balance_rec.SEGMENT13
889 ||':'|| p_balance_rec.SEGMENT14
890 ||':'|| p_balance_rec.SEGMENT15
891 ||':'|| p_balance_rec.SEGMENT16
892 ||':'|| p_balance_rec.SEGMENT17
893 ||':'|| p_balance_rec.SEGMENT18
894 ||':'|| p_balance_rec.SEGMENT19
895 ||':'|| p_balance_rec.SEGMENT20
896 ||':'|| p_balance_rec.SEGMENT21
897 ||':'|| p_balance_rec.SEGMENT22
898 ||':'|| p_balance_rec.SEGMENT23
899 ||':'|| p_balance_rec.SEGMENT24
900 ||':'|| p_balance_rec.SEGMENT25
901 ||':'|| p_balance_rec.SEGMENT26
902 ||':'|| p_balance_rec.SEGMENT27
903 ||':'|| p_balance_rec.SEGMENT28
904 ||':'|| p_balance_rec.SEGMENT29
905 ||':'|| p_balance_rec.SEGMENT30);
906 END IF;
907
908 INSERT INTO ja_cn_account_balances(
909 ledger_id
910 , LEGAL_ENTITY_ID
911 , COMPANY_SEGMENT
912 , PERIOD_NAME
913 , CURRENCY_CODE
914 --, COST_CENTER
915 , THIRD_PARTY_TYPE --Update for updating balance also by customer or supplier
916 , THIRD_PARTY_ID
917 /*, THIRD_PARTY_NUMBER
918 , PROJECT_ID
919 , PROJECT_NUMBER
920 , PROJECT_SOURCE*/
921 , ACCOUNT_SEGMENT
922 , account_type
923 , PERSONNEL_ID
924 , PERSONNEL_NUMBER
925 , SEGMENT1
926 , SEGMENT2
927 , SEGMENT3
928 , SEGMENT4
929 , SEGMENT5
930 , SEGMENT6
931 , SEGMENT7
932 , SEGMENT8
933 , SEGMENT9
934 , SEGMENT10
935 , SEGMENT11
936 , SEGMENT12
937 , SEGMENT13
938 , SEGMENT14
939 , SEGMENT15
940 , SEGMENT16
941 , SEGMENT17
942 , SEGMENT18
943 , SEGMENT19
944 , SEGMENT20
945 , SEGMENT21
946 , SEGMENT22
947 , SEGMENT23
948 , SEGMENT24
949 , SEGMENT25
950 , SEGMENT26
951 , SEGMENT27
952 , SEGMENT28
953 , SEGMENT29
954 , SEGMENT30
955 , FUNC_BEGIN_BALANCE_DR
956 , FUNC_BEGIN_BALANCE_CR
957 , ORIG_BEGIN_BALANCE_DR
958 , ORIG_BEGIN_BALANCE_CR
959 , FUNC_PERIOD_NET_DR
960 , FUNC_PERIOD_NET_CR
961 , ORIG_PERIOD_NET_DR
962 , ORIG_PERIOD_NET_CR
963 , PERIOD_MON
964 , CREATED_BY
965 , CREATION_DATE
966 , LAST_UPDATED_BY
967 , LAST_UPDATE_DATE
968 , LAST_UPDATE_LOGIN
969 )
970 VALUES
971 ( p_balance_rec.ledger_id
972 , p_balance_rec.LEGAL_ENTITY_ID
973 , p_balance_rec.COMPANY_SEGMENT
974 , p_balance_rec.PERIOD_NAME
975 , p_balance_rec.CURRENCY_CODE
976 --, p_balance_rec.COST_CENTER
977 , p_balance_rec.THIRD_PARTY_TYPE --Update for updating balance also by customer or supplier
978 , p_balance_rec.THIRD_PARTY_ID
979 /*, p_balance_rec.THIRD_PARTY_NUMBER
980 , p_balance_rec.PROJECT_ID
981 , p_balance_rec.PROJECT_NUMBER
982 , p_balance_rec.PROJECT_SOURCE*/
983 , p_balance_rec.ACCOUNT_SEGMENT
984 , p_balance_rec.ACCOUNT_type
985 , p_balance_rec.PERSONNEL_ID
986 , p_balance_rec.PERSONNEL_NUMBER
987 , p_balance_rec.SEGMENT1
988 , p_balance_rec.SEGMENT2
989 , p_balance_rec.SEGMENT3
990 , p_balance_rec.SEGMENT4
991 , p_balance_rec.SEGMENT5
992 , p_balance_rec.SEGMENT6
993 , p_balance_rec.SEGMENT7
994 , p_balance_rec.SEGMENT8
995 , p_balance_rec.SEGMENT9
996 , p_balance_rec.SEGMENT10
997 , p_balance_rec.SEGMENT11
998 , p_balance_rec.SEGMENT12
999 , p_balance_rec.SEGMENT13
1000 , p_balance_rec.SEGMENT14
1001 , p_balance_rec.SEGMENT15
1002 , p_balance_rec.SEGMENT16
1003 , p_balance_rec.SEGMENT17
1004 , p_balance_rec.SEGMENT18
1005 , p_balance_rec.SEGMENT19
1006 , p_balance_rec.SEGMENT20
1007 , p_balance_rec.SEGMENT21
1008 , p_balance_rec.SEGMENT22
1009 , p_balance_rec.SEGMENT23
1010 , p_balance_rec.SEGMENT24
1011 , p_balance_rec.SEGMENT25
1012 , p_balance_rec.SEGMENT26
1013 , p_balance_rec.SEGMENT27
1014 , p_balance_rec.SEGMENT28
1015 , p_balance_rec.SEGMENT29
1016 , p_balance_rec.SEGMENT30
1017 , 0
1018 , 0
1019 , 0
1020 , 0
1021 , p_balance_rec.FUNC_PERIOD_NET_DR
1022 , p_balance_rec.FUNC_PERIOD_NET_CR
1023 , p_balance_rec.ORIG_PERIOD_NET_DR
1024 , p_balance_rec.ORIG_PERIOD_NET_CR
1025 , p_balance_rec.PERIOD_MON
1026 , fnd_global.USER_ID
1027 , SYSDATE
1028 , fnd_global.USER_ID
1029 , SYSDATE
1030 , fnd_global.login_id
1031 );
1032 END IF; --SQL%ROWCOUNT = 0
1033 ELSE --p_current_period_flag='Y'
1034 UPDATE ja_cn_account_balances
1035 SET FUNC_begin_BALANCE_DR = FUNC_begin_BALANCE_DR + p_balance_rec.FUNC_PERIOD_NET_DR
1036 , FUNC_begin_BALANCE_CR = FUNC_begin_BALANCE_cR + p_balance_rec.FUNC_PERIOD_NET_CR
1037 -- added by jarwang for bug 16572904
1038 --, ORIG_begin_BALANCE_DR = FUNC_begin_BALANCE_DR + p_balance_rec.ORIG_PERIOD_NET_DR
1039 --, ORIG_begin_BALANCE_CR = FUNC_begin_BALANCE_cR + p_balance_rec.ORIG_PERIOD_NET_CR
1040 ,ORIG_begin_BALANCE_DR = ORIG_begin_BALANCE_DR + p_balance_rec.ORIG_PERIOD_NET_DR
1041 , ORIG_begin_BALANCE_CR = ORIG_begin_BALANCE_CR + p_balance_rec.ORIG_PERIOD_NET_CR
1042 , LAST_UPDATED_BY = fnd_global.USER_ID
1043 , LAST_UPDATE_DATE = SYSDATE
1044 , LAST_UPDATE_LOGIN = fnd_global.login_id
1045 WHERE ledger_id = p_balance_rec.ledger_id
1046 AND LEGAL_ENTITY_ID = p_balance_rec.LEGAL_ENTITY_ID
1047 AND COMPANY_SEGMENT = p_balance_rec.COMPANY_SEGMENT
1048 AND PERIOD_NAME = p_balance_rec.PERIOD_NAME
1049 AND CURRENCY_CODE = p_balance_rec.CURRENCY_CODE
1050 --AND nvl(COST_CENTER,0) = nvl(p_balance_rec.COST_CENTER,0)
1051 AND ACCOUNT_SEGMENT = p_balance_rec.ACCOUNT_SEGMENT
1052 AND nvl(THIRD_PARTY_TYPE ,0) = nvl(p_balance_rec.THIRD_PARTY_TYPE ,0) -- Update for updating balance also by customer or supplier
1053 AND nvl(THIRD_PARTY_ID ,0) = nvl(p_balance_rec.THIRD_PARTY_ID ,0)
1054 AND nvl(SEGMENT1, 0) = nvl(p_balance_rec.SEGMENT1,0)
1055 AND nvl(SEGMENT2, 0) = nvl(p_balance_rec.SEGMENT2,0)
1056 AND nvl(SEGMENT3, 0) = nvl(p_balance_rec.SEGMENT3,0)
1057 AND nvl(SEGMENT4, 0) = nvl(p_balance_rec.SEGMENT4,0)
1058 AND nvl(SEGMENT5, 0) = nvl(p_balance_rec.SEGMENT5,0)
1059 AND nvl(SEGMENT6, 0) = nvl(p_balance_rec.SEGMENT6,0)
1060 AND nvl(SEGMENT7, 0) = nvl(p_balance_rec.SEGMENT7,0)
1061 AND nvl(SEGMENT8, 0) = nvl(p_balance_rec.SEGMENT8,0)
1062 AND nvl(SEGMENT9, 0) = nvl(p_balance_rec.SEGMENT9,0)
1063 AND nvl(SEGMENT10, 0) = nvl(p_balance_rec.SEGMENT10,0)
1064 AND nvl(SEGMENT11, 0) = nvl(p_balance_rec.SEGMENT11,0)
1065 AND nvl(SEGMENT12, 0) = nvl(p_balance_rec.SEGMENT12,0)
1066 AND nvl(SEGMENT13, 0) = nvl(p_balance_rec.SEGMENT13,0)
1067 AND nvl(SEGMENT14, 0) = nvl(p_balance_rec.SEGMENT14,0)
1068 AND nvl(SEGMENT15, 0) = nvl(p_balance_rec.SEGMENT15,0)
1069 AND nvl(SEGMENT16, 0) = nvl(p_balance_rec.SEGMENT16,0)
1070 AND nvl(SEGMENT17, 0) = nvl(p_balance_rec.SEGMENT17,0)
1071 AND nvl(SEGMENT18, 0) = nvl(p_balance_rec.SEGMENT18,0)
1072 AND nvl(SEGMENT19, 0) = nvl(p_balance_rec.SEGMENT19,0)
1073 AND nvl(SEGMENT20, 0) = nvl(p_balance_rec.SEGMENT20,0)
1074 AND nvl(SEGMENT21, 0) = nvl(p_balance_rec.SEGMENT21,0)
1075 AND nvl(SEGMENT22, 0) = nvl(p_balance_rec.SEGMENT22,0)
1076 AND nvl(SEGMENT23, 0) = nvl(p_balance_rec.SEGMENT23,0)
1077 AND nvl(SEGMENT24, 0) = nvl(p_balance_rec.SEGMENT24,0)
1078 AND nvl(SEGMENT25, 0) = nvl(p_balance_rec.SEGMENT25,0)
1079 AND nvl(SEGMENT26, 0) = nvl(p_balance_rec.SEGMENT26,0)
1080 AND nvl(SEGMENT27, 0) = nvl(p_balance_rec.SEGMENT27,0)
1081 AND nvl(SEGMENT28, 0) = nvl(p_balance_rec.SEGMENT28,0)
1082 AND nvl(SEGMENT29, 0) = nvl(p_balance_rec.SEGMENT29,0)
1083 AND nvl(SEGMENT30, 0) = nvl(p_balance_rec.SEGMENT30,0);
1084 -- AND nvl(PROJECT_ID ,0) = nvl(p_balance_rec.PROJECT_ID ,0)
1085 --AND nvl(PROJECT_NUMBER ,0) = nvl(p_balance_rec.PROJECT_NUMBER ,0)
1086 --AND nvl(PROJECT_SOURCE ,0) = nvl(p_balance_rec.PROJECT_SOURCE ,0)
1087 --AND nvl(PERSONNEL_ID ,0) = nvl(p_balance_rec.PERSONNEL_ID ,0)
1088 --AND nvl(PERSONNEL_NUMBER ,0) = nvl(p_balance_rec.PERSONNEL_NUMBER ,0)
1089 -- AND nvl(PERIOD_MON ,0) = nvl(p_balance_rec.PERIOD_MON ,0);
1090
1091 --first time balance
1092 IF SQL%ROWCOUNT = 0
1093 THEN
1094
1095 IF( G_STATEMENT_LEVEL >= g_debug_devel )
1096 THEN
1097 put_log( G_MODULE_PREFIX||l_procedure_name||'.update_balance'
1098 , p_balance_rec.ledger_id
1099 ||':'|| p_balance_rec.LEGAL_ENTITY_ID
1100 ||':'|| p_balance_rec.COMPANY_SEGMENT
1101 ||':'|| p_balance_rec.PERIOD_NAME
1102 ||':'|| p_balance_rec.CURRENCY_CODE
1103 ||':'|| p_balance_rec.COST_CENTER
1104 ||':'|| p_balance_rec.THIRD_PARTY_TYPE
1105 ||':'|| p_balance_rec.THIRD_PARTY_ID
1106 ||':'|| p_balance_rec.THIRD_PARTY_NUMBER
1107 ||':'|| p_balance_rec.PROJECT_ID
1108 ||':'|| p_balance_rec.PROJECT_NUMBER
1109 ||':'|| p_balance_rec.PROJECT_SOURCE
1110 ||':'|| p_balance_rec.ACCOUNT_SEGMENT
1111 ||':'|| p_balance_rec.ACCOUNT_type
1112 ||':'|| p_balance_rec.PERSONNEL_ID
1113 ||':'|| p_balance_rec.PERSONNEL_NUMBER
1114 ||':'|| p_balance_rec.SEGMENT1
1115 ||':'|| p_balance_rec.SEGMENT2
1116 ||':'|| p_balance_rec.SEGMENT3
1117 ||':'|| p_balance_rec.SEGMENT4
1118 ||':'|| p_balance_rec.SEGMENT5
1119 ||':'|| p_balance_rec.SEGMENT6
1120 ||':'|| p_balance_rec.SEGMENT7
1121 ||':'|| p_balance_rec.SEGMENT8
1122 ||':'|| p_balance_rec.SEGMENT9
1123 ||':'|| p_balance_rec.SEGMENT10
1124 ||':'|| p_balance_rec.SEGMENT11
1125 ||':'|| p_balance_rec.SEGMENT12
1126 ||':'|| p_balance_rec.SEGMENT13
1127 ||':'|| p_balance_rec.SEGMENT14
1128 ||':'|| p_balance_rec.SEGMENT15
1129 ||':'|| p_balance_rec.SEGMENT16
1130 ||':'|| p_balance_rec.SEGMENT17
1131 ||':'|| p_balance_rec.SEGMENT18
1132 ||':'|| p_balance_rec.SEGMENT19
1133 ||':'|| p_balance_rec.SEGMENT20
1134 ||':'|| p_balance_rec.SEGMENT21
1135 ||':'|| p_balance_rec.SEGMENT22
1136 ||':'|| p_balance_rec.SEGMENT23
1137 ||':'|| p_balance_rec.SEGMENT24
1138 ||':'|| p_balance_rec.SEGMENT25
1139 ||':'|| p_balance_rec.SEGMENT26
1140 ||':'|| p_balance_rec.SEGMENT27
1141 ||':'|| p_balance_rec.SEGMENT28
1142 ||':'|| p_balance_rec.SEGMENT29
1143 ||':'|| p_balance_rec.SEGMENT30 );
1144 END IF;
1145
1146 INSERT INTO ja_cn_account_balances(
1147 ledger_id
1148 , LEGAL_ENTITY_ID
1149 , COMPANY_SEGMENT
1150 , PERIOD_NAME
1151 , CURRENCY_CODE
1152 --, COST_CENTER -- Update for updating balance also by customer or supplier
1153 , THIRD_PARTY_TYPE
1154 , THIRD_PARTY_ID
1155 /*, THIRD_PARTY_NUMBER
1156 , PROJECT_ID
1157 , PROJECT_NUMBER
1158 , PROJECT_SOURCE*/
1159 , ACCOUNT_SEGMENT
1160 , account_type
1161 , PERSONNEL_ID
1162 , PERSONNEL_NUMBER
1163 , SEGMENT1
1164 , SEGMENT2
1165 , SEGMENT3
1166 , SEGMENT4
1167 , SEGMENT5
1168 , SEGMENT6
1169 , SEGMENT7
1170 , SEGMENT8
1171 , SEGMENT9
1172 , SEGMENT10
1173 , SEGMENT11
1174 , SEGMENT12
1175 , SEGMENT13
1176 , SEGMENT14
1177 , SEGMENT15
1178 , SEGMENT16
1179 , SEGMENT17
1180 , SEGMENT18
1181 , SEGMENT19
1182 , SEGMENT20
1183 , SEGMENT21
1184 , SEGMENT22
1185 , SEGMENT23
1186 , SEGMENT24
1187 , SEGMENT25
1188 , SEGMENT26
1189 , SEGMENT27
1190 , SEGMENT28
1191 , SEGMENT29
1192 , SEGMENT30
1193 , FUNC_BEGIN_BALANCE_DR
1194 , FUNC_BEGIN_BALANCE_CR
1195 , ORIG_BEGIN_BALANCE_DR
1196 , ORIG_BEGIN_BALANCE_CR
1197 , FUNC_PERIOD_NET_DR
1198 , FUNC_PERIOD_NET_CR
1199 , ORIG_PERIOD_NET_DR
1200 , ORIG_PERIOD_NET_CR
1201 , PERIOD_MON
1202 , CREATED_BY
1203 , CREATION_DATE
1204 , LAST_UPDATED_BY
1205 , LAST_UPDATE_DATE
1206 , LAST_UPDATE_LOGIN
1207 )
1208 VALUES
1209 ( p_balance_rec.ledger_id
1210 , p_balance_rec.LEGAL_ENTITY_ID
1211 , p_balance_rec.COMPANY_SEGMENT
1212 , p_balance_rec.PERIOD_NAME
1213 , p_balance_rec.CURRENCY_CODE
1214 -- , p_balance_rec.COST_CENTER
1215 , p_balance_rec.THIRD_PARTY_TYPE -- Update for updating balance also by customer or supplier
1216 , p_balance_rec.THIRD_PARTY_ID
1217 /* , p_balance_rec.THIRD_PARTY_NUMBER
1218 , p_balance_rec.PROJECT_ID
1219 , p_balance_rec.PROJECT_NUMBER
1220 , p_balance_rec.PROJECT_SOURCE*/
1221 , p_balance_rec.ACCOUNT_SEGMENT
1222 , p_balance_rec.ACCOUNT_type
1223 , p_balance_rec.PERSONNEL_ID
1224 , p_balance_rec.PERSONNEL_NUMBER
1225 , p_balance_rec.SEGMENT1
1226 , p_balance_rec.SEGMENT2
1227 , p_balance_rec.SEGMENT3
1228 , p_balance_rec.SEGMENT4
1229 , p_balance_rec.SEGMENT5
1230 , p_balance_rec.SEGMENT6
1231 , p_balance_rec.SEGMENT7
1232 , p_balance_rec.SEGMENT8
1233 , p_balance_rec.SEGMENT9
1234 , p_balance_rec.SEGMENT10
1235 , p_balance_rec.SEGMENT11
1236 , p_balance_rec.SEGMENT12
1237 , p_balance_rec.SEGMENT13
1238 , p_balance_rec.SEGMENT14
1239 , p_balance_rec.SEGMENT15
1240 , p_balance_rec.SEGMENT16
1241 , p_balance_rec.SEGMENT17
1242 , p_balance_rec.SEGMENT18
1243 , p_balance_rec.SEGMENT19
1244 , p_balance_rec.SEGMENT20
1245 , p_balance_rec.SEGMENT21
1246 , p_balance_rec.SEGMENT22
1247 , p_balance_rec.SEGMENT23
1248 , p_balance_rec.SEGMENT24
1249 , p_balance_rec.SEGMENT25
1250 , p_balance_rec.SEGMENT26
1251 , p_balance_rec.SEGMENT27
1252 , p_balance_rec.SEGMENT28
1253 , p_balance_rec.SEGMENT29
1254 , p_balance_rec.SEGMENT30
1255 , p_balance_rec.FUNC_PERIOD_NET_DR
1256 , p_balance_rec.FUNC_PERIOD_NET_CR
1257 , p_balance_rec.ORIG_PERIOD_NET_DR
1258 , p_balance_rec.ORIG_PERIOD_NET_CR
1259 , 0
1260 , 0
1261 , 0
1262 , 0
1263 , p_balance_rec.PERIOD_MON
1264 , fnd_global.USER_ID
1265 , SYSDATE
1266 , fnd_global.USER_ID
1267 , SYSDATE
1268 , fnd_global.login_id
1269 );
1270 END IF; --SQL%ROWCOUNT = 0
1271 END IF;--p_current_period_flag='Y'
1272
1273 IF( G_PROC_LEVEL >= g_debug_devel )
1274 THEN
1275 FND_LOG.STRING(G_PROC_LEVEL
1276 ,G_MODULE_PREFIX||l_procedure_name||'.end'
1277 ,'End procedure');
1278 END IF; --( G_PROC_LEVEL >= g_debug_devel)
1279 EXCEPTION
1280 WHEN OTHERS THEN
1281 IF(FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel)
1282 THEN
1283 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1284 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
1285 , SQLCODE||SQLERRM);
1286 END IF;
1287 RAISE;
1288 END update_balance;
1289
1290 --==========================================================================
1291 -- PROCEDURE NAME:
1292 -- update_retained_account private
1293 --
1294 -- DESCRIPTION:
1295 -- This procedure update the retained earning account
1296 --
1297 -- PARAMETERS:
1298 -- In: p_period_name the end period name after which
1299 -- the CNAO journal should be processed
1300 -- p_ledger_id Set of book ID
1301 -- p_legal_entity_ID Legal entity id
1302
1303 --
1304 -- DESIGN REFERENCES:
1305 -- None
1306 --
1307 -- CHANGE HISTORY:
1308 -- 02/21/2006 Jogen Hu Created
1309 -- 04/28/2007 Qingjun Zhao Change SOB to Ledger for upgrade
1310 -- from 11i to R12
1311 -- 05/02/2010 Shujuan Yan Change for CNAO V2
1312 -- 25/03/2010 Chaoqun Wu Update for updating balance also by customer
1313 -- or supplier
1314 -- 27/05/2010 Chaoqun Wu Update for bug 9582957
1315 --===========================================================================
1316 PROCEDURE update_retained_parent_account
1317 ( p_period_name IN VARCHAR2
1318 , p_ledger_id IN NUMBER
1319 , p_legal_entity_ID IN NUMBER
1320 )
1321 IS
1322 l_procedure_name VARCHAR2(30):='update_retained_account';
1323 l_period_year ja_cn_periods.period_year%TYPE;
1324 --l_period_name ja_cn_periods.period_name%TYPE;
1325
1326 l_RET_EARN_CODE_COMBINATION_ID gl_ledgers.ret_earn_code_combination_id%TYPE;
1327 l_ret_currence_code ja_cn_account_balances.currency_code%TYPE;
1328 l_row_count NUMBER;
1329 l_cost_center_second_tracking BOOLEAN:=FALSE;
1330 l_period_num ja_cn_periods.period_num%TYPE;
1331 l_last_period_num ja_cn_periods.period_num%TYPE;
1332 l_flex_value_set_id FND_FLEX_VALUE_SETS.Flex_Value_Set_Id%TYPE;
1333 i NUMBER;
1334
1335 l_number NUMBER;
1336 lv_account_number JA_CN_CODE_COMBINATION_V.ACCOUNT_SEGMENT%TYPE; --Added for bug 9582957
1337
1338 BEGIN
1339 IF( G_PROC_LEVEL >= g_debug_devel )
1340 THEN
1341 FND_LOG.STRING(G_PROC_LEVEL
1342 ,G_MODULE_PREFIX||l_procedure_name||'.begin'
1343 ,'Begin procedure');
1344 END IF; --( G_PROC_LEVEL >= g_debug_devel)
1345
1346 --------------------------------------------------------
1347 --get retain ccid and base currency code
1348 SELECT led.RET_EARN_CODE_COMBINATION_ID
1349 , led.currency_code
1350 INTO l_RET_EARN_CODE_COMBINATION_ID
1351 , l_ret_currence_code
1352 FROM gl_ledgers led
1353 WHERE led.ledger_id = p_ledger_id;
1354
1355 --Get retained account, added for bug 9582957
1356 SELECT DISTINCT Account_Segment
1357 INTO lv_account_number
1358 FROM ja_cn_code_combination_v Jcc
1359 WHERE Jcc.Code_Combination_ID = l_RET_EARN_CODE_COMBINATION_ID;
1360
1361 SELECT COUNT(*)
1362 INTO l_row_count
1363 FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
1364 , gl_ledgers led
1365 , Fnd_Id_Flex_Segments ifs
1366 WHERE sav.ID_FLEX_CODE = 'GL#'
1367 AND sav.APPLICATION_ID = 101
1368 AND sav.attribute_value = 'Y'
1369 AND sav.ID_FLEX_NUM = led.chart_of_accounts_id
1370 AND ifs.application_id = 101
1371 AND ifs.Id_Flex_Code = 'GL#'
1372 AND ifs.id_flex_num = led.chart_of_accounts_id
1373 AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
1374 AND led.ledger_id = p_ledger_id
1375 AND sav.SEGMENT_ATTRIBUTE_TYPE IN ('FA_COST_CTR','GL_SECONDARY_TRACKING');
1376
1377 IF l_row_count>=2
1378 THEN
1379 l_cost_center_second_tracking := TRUE;
1380 END IF;
1381
1382 SELECT period_year
1383 INTO l_period_year
1384 FROM ja_cn_periods
1385 WHERE period_name=p_period_name
1386 AND ledger_id=p_ledger_id;
1387
1388 l_period_num:=l_period_year*1000+1;
1389
1390 SELECT max(period_num)
1391 INTO l_last_period_num
1392 FROM ja_cn_periods
1393 WHERE ledger_id=p_ledger_id;
1394
1395 SELECT ifs.flex_value_set_id
1396 INTO l_flex_value_set_id
1397 FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
1398 , gl_ledgers led
1399 , Fnd_Id_Flex_Segments ifs
1400 WHERE sav.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
1401 AND sav.ID_FLEX_CODE = 'GL#'
1402 AND sav.APPLICATION_ID = 101
1403 AND sav.attribute_value = 'Y'
1404 AND sav.ID_FLEX_NUM = led.chart_of_accounts_id
1405 AND ifs.application_id = 101
1406 AND ifs.Id_Flex_Code = 'GL#'
1407 AND ifs.id_flex_num = led.chart_of_accounts_id
1408 AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
1409 AND led.ledger_id = p_ledger_id;
1410
1411 --Begin: Added for fixing bug 9582957 by Chaoqun on 27-May-2010
1412 --Step1: Populate balancing segment values to table ja_cn_ledger_le_bsv_gt
1413 DELETE
1414 FROM JA_CN_LEDGER_LE_BSV_GT;
1415 COMMIT ;
1416 IF ja_cn_utility.populate_ledger_le_bsv_gt(p_ledger_id, p_legal_entity_id) <> 'S'
1417 THEN
1418 RETURN;
1419 END IF;
1420 --Step2: Insert balances of retained account into table ja_cn_account_balances_post_gt
1421 INSERT INTO ja_cn_account_balances_post_gt(
1422 LEDGER_ID
1423 , LEGAL_ENTITY_ID
1424 , COMPANY_SEGMENT
1425 , PERIOD_NAME
1426 , CURRENCY_CODE
1427 , THIRD_PARTY_TYPE
1428 , THIRD_PARTY_ID
1429 , ACCOUNT_SEGMENT
1430 , PERSONNEL_ID
1431 , PERSONNEL_NUMBER
1432 , FUNC_BEGIN_BALANCE_DR
1433 , FUNC_BEGIN_BALANCE_CR
1434 , ORIG_BEGIN_BALANCE_DR
1435 , ORIG_BEGIN_BALANCE_CR
1436 , FUNC_PERIOD_NET_DR
1437 , FUNC_PERIOD_NET_CR
1438 , ORIG_PERIOD_NET_DR
1439 , ORIG_PERIOD_NET_CR
1440 , PERIOD_MON
1441 , ACCOUNT_TYPE
1442 , PERIOD_NUM
1443 , CREATED_BY
1444 , CREATION_DATE
1445 , LAST_UPDATED_BY
1446 , LAST_UPDATE_DATE
1447 , LAST_UPDATE_LOGIN
1448 , SEGMENT1
1449 , SEGMENT2
1450 , SEGMENT3
1451 , SEGMENT4
1452 , SEGMENT5
1453 , SEGMENT7
1454 , SEGMENT8
1455 , SEGMENT9
1456 , SEGMENT10
1457 , SEGMENT11
1458 , SEGMENT12
1459 , SEGMENT13
1460 , SEGMENT14
1461 , SEGMENT15
1462 , SEGMENT16
1463 , SEGMENT17
1464 , SEGMENT18
1465 , SEGMENT19
1466 , SEGMENT20
1467 , SEGMENT21
1468 , SEGMENT22
1469 , SEGMENT23
1470 , SEGMENT24
1471 , SEGMENT25
1472 , SEGMENT26
1473 , SEGMENT27
1474 , SEGMENT28
1475 , SEGMENT29
1476 , SEGMENT30
1477 )
1478 SELECT DISTINCT p_ledger_id
1479 , p_legal_entity_ID
1480 , jcc.company_segment
1481 , jcp.period_name
1482 , gb.currency_code
1483 , NULL
1484 , NULL
1485 , jcc.account_segment
1486 , NULL
1487 , NULL
1488 , sum(nvl(gb.begin_balance_dr_beq,gb.begin_balance_dr))
1489 , sum(nvl(gb.begin_balance_cr_beq,gb.begin_balance_cr))
1490 , sum(gb.begin_balance_dr)
1491 , sum(gb.begin_balance_cr)
1492 , sum(nvl(gb.PERIOD_NET_DR_BEQ, gb.PERIOD_NET_DR))
1493 , sum(nvl(gb.PERIOD_NET_CR_BEQ, gb.PERIOD_NET_CR))
1494 , sum(gb.PERIOD_NET_DR)
1495 , sum(gb.PERIOD_NET_CR)
1496 , jcp.period_num - jcp.period_year*1000
1497 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
1498 , jcp.period_num
1499 , fnd_global.USER_ID
1500 , SYSDATE
1501 , fnd_global.USER_ID
1502 , SYSDATE
1503 , fnd_global.LOGIN_ID
1504 , jcc.SEGMENT1
1505 , jcc.SEGMENT2
1506 , jcc.SEGMENT3
1507 , jcc.SEGMENT4
1508 , jcc.SEGMENT5
1509 , jcc.SEGMENT7
1510 , jcc.SEGMENT8
1511 , jcc.SEGMENT9
1512 , jcc.SEGMENT10
1513 , jcc.SEGMENT11
1514 , jcc.SEGMENT12
1515 , jcc.SEGMENT13
1516 , jcc.SEGMENT14
1517 , jcc.SEGMENT15
1518 , jcc.SEGMENT16
1519 , jcc.SEGMENT17
1520 , jcc.SEGMENT18
1521 , jcc.SEGMENT19
1522 , jcc.SEGMENT20
1523 , jcc.SEGMENT21
1524 , jcc.SEGMENT22
1525 , jcc.SEGMENT23
1526 , jcc.SEGMENT24
1527 , jcc.SEGMENT25
1528 , jcc.SEGMENT26
1529 , jcc.SEGMENT27
1530 , jcc.SEGMENT28
1531 , jcc.SEGMENT29
1532 , jcc.SEGMENT30
1533 FROM ja_cn_code_combination_v jcc
1534 , FND_FLEX_VALUES ffv
1535 , ja_cn_periods jcp
1536 , gl_balances gb
1537 , ja_cn_code_combination_v jcc1
1538 WHERE jcc.ledger_id = p_ledger_id --parameter: p_ledger_id
1539 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
1540 AND ffv.FLEX_VALUE_SET_ID = l_flex_value_set_id --variable: l_flex_value_set_id
1541 AND ffv.flex_value = jcc.account_segment
1542 AND jcp.ledger_id = jcc.ledger_id
1543 AND jcp.period_num BETWEEN l_period_num AND l_last_period_num --variable: l_period_num and l_last_period_num
1544 AND jcc.account_segment = lv_account_number --variable: lv_account_number
1545 --Balance condition
1546 AND gb.ledger_id = jcc1.ledger_id
1547 AND gb.code_combination_id = jcc1.code_combination_id
1548 AND gb.period_name = jcp.period_name
1549 AND gb.actual_flag = 'A'
1550 --Segment codition
1551 AND jcc1.ledger_id = jcc.ledger_id
1552 AND jcc1.code_combination_id = jcc.code_combination_id
1553 AND jcc1.company_segment = jcc.company_segment
1554 AND jcc1.account_segment = jcc.account_segment
1555 AND nvl(jcc1.SEGMENT1,'0')= nvl(jcc.SEGMENT1,'0')
1556 AND nvl(jcc1.SEGMENT2,'0')= nvl(jcc.SEGMENT2,'0')
1557 AND nvl(jcc1.SEGMENT3,'0')= nvl(jcc.SEGMENT3,'0')
1558 AND nvl(jcc1.SEGMENT4,'0')= nvl(jcc.SEGMENT4,'0')
1559 AND nvl(jcc1.SEGMENT5,'0')= nvl(jcc.SEGMENT5,'0')
1560 AND nvl(jcc1.SEGMENT6,'0')= nvl(jcc.SEGMENT6,'0')
1561 AND nvl(jcc1.SEGMENT7,'0')= nvl(jcc.SEGMENT7,'0')
1562 AND nvl(jcc1.SEGMENT8,'0')= nvl(jcc.SEGMENT8,'0')
1563 AND nvl(jcc1.SEGMENT9,'0')= nvl(jcc.SEGMENT9,'0')
1564 AND nvl(jcc1.SEGMENT10,'0')= nvl(jcc.SEGMENT10,'0')
1565 AND nvl(jcc1.SEGMENT11,'0')= nvl(jcc.SEGMENT11,'0')
1566 AND nvl(jcc1.SEGMENT12,'0')= nvl(jcc.SEGMENT12,'0')
1567 AND nvl(jcc1.SEGMENT13,'0')= nvl(jcc.SEGMENT13,'0')
1568 AND nvl(jcc1.SEGMENT14,'0')= nvl(jcc.SEGMENT14,'0')
1569 AND nvl(jcc1.SEGMENT15,'0')= nvl(jcc.SEGMENT15,'0')
1570 AND nvl(jcc1.SEGMENT16,'0')= nvl(jcc.SEGMENT16,'0')
1571 AND nvl(jcc1.SEGMENT17,'0')= nvl(jcc.SEGMENT17,'0')
1572 AND nvl(jcc1.SEGMENT18,'0')= nvl(jcc.SEGMENT18,'0')
1573 AND nvl(jcc1.SEGMENT19,'0')= nvl(jcc.SEGMENT19,'0')
1574 AND nvl(jcc1.SEGMENT20,'0')= nvl(jcc.SEGMENT20,'0')
1575 AND nvl(jcc1.SEGMENT21,'0')= nvl(jcc.SEGMENT21,'0')
1576 AND nvl(jcc1.SEGMENT22,'0')= nvl(jcc.SEGMENT22,'0')
1577 AND nvl(jcc1.SEGMENT23,'0')= nvl(jcc.SEGMENT23,'0')
1578 AND nvl(jcc1.SEGMENT24,'0')= nvl(jcc.SEGMENT24,'0')
1579 AND nvl(jcc1.SEGMENT25,'0')= nvl(jcc.SEGMENT25,'0')
1580 AND nvl(jcc1.SEGMENT26,'0')= nvl(jcc.SEGMENT26,'0')
1581 AND nvl(jcc1.SEGMENT27,'0')= nvl(jcc.SEGMENT27,'0')
1582 AND nvl(jcc1.SEGMENT28,'0')= nvl(jcc.SEGMENT28,'0')
1583 AND nvl(jcc1.SEGMENT29,'0')= nvl(jcc.SEGMENT29,'0')
1584 AND nvl(jcc1.SEGMENT30,'0')= nvl(jcc.SEGMENT30,'0')
1585 --BSV condition
1586 AND EXISTS (SELECT llbg.Bal_Seg_Value
1587 FROM ja_cn_ledger_le_bsv_gt llbg
1588 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(jcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
1589 AND llbg.Ledger_Id = p_ledger_id --parameter: p_ledger_id
1590 AND llbg.Legal_Entity_Id = p_legal_entity_id) --parameter: p_legal_entity_id
1591 GROUP BY jcc.company_segment
1592 , jcp.period_name
1593 , gb.currency_code
1594 , jcc.account_segment
1595 , jcp.period_num - jcp.period_year*1000
1596 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
1597 , jcp.period_num
1598 /*, fnd_global.USER_ID
1599 , SYSDATE
1600 , fnd_global.USER_ID
1601 , SYSDATE
1602 , fnd_global.LOGIN_ID*/
1603 , jcc.SEGMENT1
1604 , jcc.SEGMENT2
1605 , jcc.SEGMENT3
1606 , jcc.SEGMENT4
1607 , jcc.SEGMENT5
1608 , jcc.SEGMENT7
1609 , jcc.SEGMENT8
1610 , jcc.SEGMENT9
1611 , jcc.SEGMENT10
1612 , jcc.SEGMENT11
1613 , jcc.SEGMENT12
1614 , jcc.SEGMENT13
1615 , jcc.SEGMENT14
1616 , jcc.SEGMENT15
1617 , jcc.SEGMENT16
1618 , jcc.SEGMENT17
1619 , jcc.SEGMENT18
1620 , jcc.SEGMENT19
1621 , jcc.SEGMENT20
1622 , jcc.SEGMENT21
1623 , jcc.SEGMENT22
1624 , jcc.SEGMENT23
1625 , jcc.SEGMENT24
1626 , jcc.SEGMENT25
1627 , jcc.SEGMENT26
1628 , jcc.SEGMENT27
1629 , jcc.SEGMENT28
1630 , jcc.SEGMENT29
1631 , jcc.SEGMENT30;
1632 --End: Added for fixing bug 9582957 by Chaoqun on 27-May-2010
1633
1634 --Begin: Deleted for fixing bug 9582957
1635 /* IF l_cost_center_second_tracking
1636 THEN*/
1637 --insert all possible retain account into temp table
1638 /* INSERT INTO ja_cn_account_balances_post_gt(
1639 ledger_id
1640 , LEGAL_ENTITY_ID
1641 , COMPANY_SEGMENT
1642 , PERIOD_NAME
1643 , CURRENCY_CODE
1644 --, COST_CENTER
1645 , THIRD_PARTY_TYPE -- Update for updating balance also by customer or supplier
1646 , THIRD_PARTY_ID
1647 \*, THIRD_PARTY_NUMBER
1648 , PROJECT_ID
1649 , PROJECT_NUMBER
1650 , PROJECT_SOURCE*\
1651 , ACCOUNT_SEGMENT
1652 , PERSONNEL_ID
1653 , PERSONNEL_NUMBER
1654 , FUNC_BEGIN_BALANCE_DR
1655 , FUNC_BEGIN_BALANCE_CR
1656 , ORIG_BEGIN_BALANCE_DR
1657 , ORIG_BEGIN_BALANCE_CR
1658 , FUNC_PERIOD_NET_DR
1659 , FUNC_PERIOD_NET_CR
1660 , ORIG_PERIOD_NET_DR
1661 , ORIG_PERIOD_NET_CR
1662 , PERIOD_MON
1663 , ACCOUNT_TYPE
1664 , period_num
1665 , CREATED_BY
1666 , CREATION_DATE
1667 , LAST_UPDATED_BY
1668 , LAST_UPDATE_DATE
1669 , LAST_UPDATE_LOGIN
1670 , SEGMENT1
1671 , SEGMENT2
1672 , SEGMENT3
1673 , SEGMENT4
1674 , SEGMENT5
1675 , SEGMENT7
1676 , SEGMENT8
1677 , SEGMENT9
1678 , SEGMENT10
1679 , SEGMENT11
1680 , SEGMENT12
1681 , SEGMENT13
1682 , SEGMENT14
1683 , SEGMENT15
1684 , SEGMENT16
1685 , SEGMENT17
1686 , SEGMENT18
1687 , SEGMENT19
1688 , SEGMENT20
1689 , SEGMENT21
1690 , SEGMENT22
1691 , SEGMENT23
1692 , SEGMENT24
1693 , SEGMENT25
1694 , SEGMENT26
1695 , SEGMENT27
1696 , SEGMENT28
1697 , SEGMENT29
1698 , SEGMENT30
1699 )
1700 SELECT DISTINCT p_ledger_id
1701 , p_legal_entity_ID
1702 , jcc.company_segment
1703 , jcp.period_name
1704 , l_ret_currence_code
1705 -- , NULL
1706 , NULL -- Update for updating balance also by customer or supplier
1707 , NULL
1708 \*, NULL
1709 , NULL
1710 , NULL
1711 , 'COA'*\
1712 , jcc.account_segment
1713 , NULL
1714 , NULL
1715 , 0
1716 , 0
1717 , 0
1718 , 0
1719 , 0
1720 , 0
1721 , 0
1722 , 0
1723 , jcp.period_num - jcp.period_year*1000
1724 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
1725 , jcp.period_num
1726 , fnd_global.USER_ID
1727 , SYSDATE
1728 , fnd_global.USER_ID
1729 , SYSDATE
1730 , fnd_global.LOGIN_ID
1731 , jcc.SEGMENT1
1732 , jcc.SEGMENT2
1733 , jcc.SEGMENT3
1734 , jcc.SEGMENT4
1735 , jcc.SEGMENT5
1736 , jcc.SEGMENT7
1737 , jcc.SEGMENT8
1738 , jcc.SEGMENT9
1739 , jcc.SEGMENT10
1740 , jcc.SEGMENT11
1741 , jcc.SEGMENT12
1742 , jcc.SEGMENT13
1743 , jcc.SEGMENT14
1744 , jcc.SEGMENT15
1745 , jcc.SEGMENT16
1746 , jcc.SEGMENT17
1747 , jcc.SEGMENT18
1748 , jcc.SEGMENT19
1749 , jcc.SEGMENT20
1750 , jcc.SEGMENT21
1751 , jcc.SEGMENT22
1752 , jcc.SEGMENT23
1753 , jcc.SEGMENT24
1754 , jcc.SEGMENT25
1755 , jcc.SEGMENT26
1756 , jcc.SEGMENT27
1757 , jcc.SEGMENT28
1758 , jcc.SEGMENT29
1759 , jcc.SEGMENT30
1760 FROM ja_cn_code_combination_v jcc
1761 , FND_FLEX_VALUES ffv
1762 , ja_cn_periods jcp
1763 WHERE jcc.ledger_id = p_ledger_id
1764 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
1765 AND ffv.FLEX_VALUE_SET_ID = l_flex_value_set_id
1766 AND ffv.flex_value = jcc.account_segment
1767 AND jcp.ledger_id = p_ledger_id
1768 AND jcp.period_num BETWEEN l_period_num AND l_last_period_num*/
1769 /*AND EXISTS((SELECT *
1770 FROM ja_cn_code_combination_v jcc1
1771 WHERE jcc1.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
1772 AND jcc1.ledger_id = p_ledger_id
1773 AND jcc1.account_segment=jcc.account_segment
1774 AND nvl(jcc1.project_id,'0') =nvl(jcc.project_id ,'0')
1775 AND nvl(jcc1.project_number,'0') =nvl(jcc.project_number,'0') ) )*//*;*/
1776
1777 /* ELSE --l_cost_center_second_tracking = false
1778
1779 INSERT INTO ja_cn_account_balances_post_gt(
1780 ledger_id
1781 , LEGAL_ENTITY_ID
1782 , COMPANY_SEGMENT
1783 , PERIOD_NAME
1784 , CURRENCY_CODE
1785 --, COST_CENTER
1786 , THIRD_PARTY_TYPE -- Update for updating balance also by customer or supplier
1787 , THIRD_PARTY_ID
1788 \*, THIRD_PARTY_NUMBER
1789 , PROJECT_ID
1790 , PROJECT_NUMBER
1791 , PROJECT_SOURCE*\
1792 , ACCOUNT_SEGMENT
1793 , PERSONNEL_ID
1794 , PERSONNEL_NUMBER
1795 , FUNC_BEGIN_BALANCE_DR
1796 , FUNC_BEGIN_BALANCE_CR
1797 , ORIG_BEGIN_BALANCE_DR
1798 , ORIG_BEGIN_BALANCE_CR
1799 , FUNC_PERIOD_NET_DR
1800 , FUNC_PERIOD_NET_CR
1801 , ORIG_PERIOD_NET_DR
1802 , ORIG_PERIOD_NET_CR
1803 , PERIOD_MON
1804 , ACCOUNT_TYPE
1805 , period_num
1806 , CREATED_BY
1807 , CREATION_DATE
1808 , LAST_UPDATED_BY
1809 , LAST_UPDATE_DATE
1810 , LAST_UPDATE_LOGIN
1811 , SEGMENT1
1812 , SEGMENT2
1813 , SEGMENT3
1814 , SEGMENT4
1815 , SEGMENT5
1816 , SEGMENT7
1817 , SEGMENT8
1818 , SEGMENT9
1819 , SEGMENT10
1820 , SEGMENT11
1821 , SEGMENT12
1822 , SEGMENT13
1823 , SEGMENT14
1824 , SEGMENT15
1825 , SEGMENT16
1826 , SEGMENT17
1827 , SEGMENT18
1828 , SEGMENT19
1829 , SEGMENT20
1830 , SEGMENT21
1831 , SEGMENT22
1832 , SEGMENT23
1833 , SEGMENT24
1834 , SEGMENT25
1835 , SEGMENT26
1836 , SEGMENT27
1837 , SEGMENT28
1838 , SEGMENT29
1839 , SEGMENT30 )
1840 SELECT DISTINCT p_ledger_id
1841 , p_legal_entity_ID
1842 , jcc.company_segment
1843 , jcp.period_name
1844 , l_ret_currence_code
1845 --, NULL
1846 , NULL -- Update for updating balance also by customer or supplier
1847 , NULL
1848 \*, NULL
1849 , NULL
1850 , NULL
1851 , 'COA'*\
1852 , jcc.account_segment
1853 , NULL
1854 , NULL
1855 , 0
1856 , 0
1857 , 0
1858 , 0
1859 , 0
1860 , 0
1861 , 0
1862 , 0
1863 , jcp.period_num - jcp.period_year*1000
1864 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
1865 , jcp.period_num
1866 , fnd_global.USER_ID
1867 , SYSDATE
1868 , fnd_global.USER_ID
1869 , SYSDATE
1870 , fnd_global.LOGIN_ID
1871 , jcc.SEGMENT1
1872 , jcc.SEGMENT2
1873 , jcc.SEGMENT3
1874 , jcc.SEGMENT4
1875 , jcc.SEGMENT5
1876 , jcc.SEGMENT7
1877 , jcc.SEGMENT8
1878 , jcc.SEGMENT9
1879 , jcc.SEGMENT10
1880 , jcc.SEGMENT11
1881 , jcc.SEGMENT12
1882 , jcc.SEGMENT13
1883 , jcc.SEGMENT14
1884 , jcc.SEGMENT15
1885 , jcc.SEGMENT16
1886 , jcc.SEGMENT17
1887 , jcc.SEGMENT18
1888 , jcc.SEGMENT19
1889 , jcc.SEGMENT20
1890 , jcc.SEGMENT21
1891 , jcc.SEGMENT22
1892 , jcc.SEGMENT23
1893 , jcc.SEGMENT24
1894 , jcc.SEGMENT25
1895 , jcc.SEGMENT26
1896 , jcc.SEGMENT27
1897 , jcc.SEGMENT28
1898 , jcc.SEGMENT29
1899 , jcc.SEGMENT30
1900 FROM ja_cn_code_combination_v jcc
1901 , FND_FLEX_VALUES ffv
1902 , ja_cn_periods jcp
1903 WHERE jcc.ledger_id = p_ledger_id
1904 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
1905 AND ffv.FLEX_VALUE_SET_ID = l_flex_value_set_id
1906 AND ffv.flex_value = jcc.account_segment
1907 AND jcp.ledger_id=p_ledger_id
1908 AND jcp.period_num BETWEEN l_period_num AND l_last_period_num
1909 \* AND EXISTS((SELECT *
1910 FROM ja_cn_code_combination_v jcc1
1911 WHERE jcc1.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
1912 AND jcc1.ledger_id = p_ledger_id
1913 AND jcc1.account_segment=jcc.account_segment
1914 AND jcc1.cost_segment =jcc.cost_segment
1915 AND nvl(jcc1.project_id,'0') =nvl(jcc.project_id ,'0')
1916 AND nvl(jcc1.project_number,'0') =nvl(jcc.project_number,'0') ) )*\;
1917
1918 END IF;--l_cost_center_second_tracking*/
1919
1920 --update the period net occurance DR and CR
1921 /* UPDATE ja_cn_account_balances_post_gt u
1922 SET (FUNC_PERIOD_NET_DR
1923 , FUNC_PERIOD_NET_CR
1924 , ORIG_PERIOD_NET_DR
1925 , ORIG_PERIOD_NET_CR
1926 , project_source)=
1927 (SELECT FUNC_PERIOD_NET_DR
1928 , FUNC_PERIOD_NET_CR
1929 , ORIG_PERIOD_NET_DR
1930 , ORIG_PERIOD_NET_CR
1931 , project_source
1932 FROM ja_cn_account_balances
1933 WHERE ledger_id = p_ledger_id
1934 AND period_name = u.period_name
1935 AND LEGAL_ENTITY_ID = p_legal_entity_ID
1936 AND COMPANY_SEGMENT = u.company_segment
1937 AND CURRENCY_CODE = u.currency_code
1938 --AND nvl(COST_CENTER,0) = nvl(u.cost_center,0)
1939 AND THIRD_PARTY_TYPE IS NULL -- Update for updating balance also by customer or supplier
1940 AND THIRD_PARTY_ID IS NULL
1941 \*AND THIRD_PARTY_NUMBER IS NULL
1942 AND nvl(PROJECT_ID,'0')= nvl(u.project_id,'0')
1943 AND nvl(PROJECT_NUMBER,'0')= nvl(u.project_number,'0')
1944 and nvl(PROJECT_source,' ')=nvl(u.PROJECT_source,'COA')*\
1945 AND ACCOUNT_SEGMENT = u.account_segment
1946 -- AND PERSONNEL_ID IS NULL
1947 -- AND PERSONNEL_NUMBER IS NULL
1948 -- CNAO V2
1949 AND nvl(SEGMENT1,'0')= nvl(u.SEGMENT1,'0')
1950 AND nvl(SEGMENT2,'0')= nvl(u.SEGMENT2,'0')
1951 AND nvl(SEGMENT3,'0')= nvl(u.SEGMENT3,'0')
1952 AND nvl(SEGMENT4,'0')= nvl(u.SEGMENT4,'0')
1953 AND nvl(SEGMENT5,'0')= nvl(u.SEGMENT5,'0')
1954 AND nvl(SEGMENT6,'0')= nvl(u.SEGMENT6,'0')
1955 AND nvl(SEGMENT7,'0')= nvl(u.SEGMENT7,'0')
1956 AND nvl(SEGMENT8,'0')= nvl(u.SEGMENT8,'0')
1957 AND nvl(SEGMENT9,'0')= nvl(u.SEGMENT9,'0')
1958 AND nvl(SEGMENT10,'0')= nvl(u.SEGMENT10,'0')
1959 AND nvl(SEGMENT11,'0')= nvl(u.SEGMENT11,'0')
1960 AND nvl(SEGMENT12,'0')= nvl(u.SEGMENT12,'0')
1961 AND nvl(SEGMENT13,'0')= nvl(u.SEGMENT13,'0')
1962 AND nvl(SEGMENT14,'0')= nvl(u.SEGMENT14,'0')
1963 AND nvl(SEGMENT15,'0')= nvl(u.SEGMENT15,'0')
1964 AND nvl(SEGMENT16,'0')= nvl(u.SEGMENT16,'0')
1965 AND nvl(SEGMENT17,'0')= nvl(u.SEGMENT17,'0')
1966 AND nvl(SEGMENT18,'0')= nvl(u.SEGMENT18,'0')
1967 AND nvl(SEGMENT19,'0')= nvl(u.SEGMENT19,'0')
1968 AND nvl(SEGMENT20,'0')= nvl(u.SEGMENT20,'0')
1969 AND nvl(SEGMENT21,'0')= nvl(u.SEGMENT21,'0')
1970 AND nvl(SEGMENT22,'0')= nvl(u.SEGMENT22,'0')
1971 AND nvl(SEGMENT23,'0')= nvl(u.SEGMENT23,'0')
1972 AND nvl(SEGMENT24,'0')= nvl(u.SEGMENT24,'0')
1973 AND nvl(SEGMENT25,'0')= nvl(u.SEGMENT25,'0')
1974 AND nvl(SEGMENT26,'0')= nvl(u.SEGMENT26,'0')
1975 AND nvl(SEGMENT27,'0')= nvl(u.SEGMENT27,'0')
1976 AND nvl(SEGMENT28,'0')= nvl(u.SEGMENT28,'0')
1977 AND nvl(SEGMENT29,'0')= nvl(u.SEGMENT29,'0')
1978 AND nvl(SEGMENT30,'0')= nvl(u.SEGMENT30,'0')
1979 );*/
1980
1981 -- INSERT INTO hu_hu SELECT * FROM ja_cn_account_balances_post_gt;
1982
1983 --update the first priod begin balance in each year
1984 /* UPDATE ja_cn_account_balances_post_gt u
1985 SET ( FUNC_BEGIN_BALANCE_DR
1986 , FUNC_BEGIN_BALANCE_CR
1987 , ORIG_BEGIN_BALANCE_DR
1988 , ORIG_BEGIN_BALANCE_CR)=
1989 ( SELECT sum(nvl(gb.begin_balance_dr_beq,gb.begin_balance_dr))
1990 , sum(nvl(gb.begin_balance_cr_beq,gb.begin_balance_cr))
1991 , sum(gb.begin_balance_dr)
1992 , sum(gb.begin_balance_cr)
1993 FROM gl_balances gb
1994 , ja_cn_code_combination_v jcc
1995 , ja_cn_code_combination_v jcc1
1996 WHERE gb.ledger_id = p_ledger_id
1997 AND jcc.ledger_id = p_ledger_id
1998 AND gb.code_combination_id = jcc.CODE_COMBINATION_ID
1999 AND jcc.company_segment = u.company_segment
2000 AND jcc.account_segment = u.account_segment
2001 -- AND nvl(jcc.cost_segment,0)= nvl(u.cost_center,0)
2002 -- AND nvl(jcc.project_number,'0') = nvl(u.project_number,'0')
2003 AND gb.currency_code = u.currency_code
2004 AND gb.period_name = u.period_name
2005 AND jcc1.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
2006 AND jcc1.ledger_id = p_ledger_id
2007 AND jcc1.company_segment = jcc.company_segment
2008 AND jcc1.account_segment = jcc.account_segment
2009 --AND jcc1.cost_segment = jcc.cost_segment
2010 --AND jcc1.other_columns = jcc.other_columns
2011 AND gb.actual_flag = 'A'
2012 )
2013 WHERE period_mon=1;
2014
2015 UPDATE ja_cn_account_balances_post_gt u
2016 SET ( FUNC_BEGIN_BALANCE_DR
2017 , FUNC_BEGIN_BALANCE_CR
2018 , ORIG_BEGIN_BALANCE_DR
2019 , ORIG_BEGIN_BALANCE_CR)=
2020 ( SELECT sum(nvl(gb.begin_balance_dr_beq,gb.begin_balance_dr))
2021 , sum(nvl(gb.begin_balance_cr_beq,gb.begin_balance_cr))
2022 , sum(gb.begin_balance_dr)
2023 , sum(gb.begin_balance_cr)
2024 FROM gl_balances gb
2025 , ja_cn_code_combination_v jcc
2026 , ja_cn_code_combination_v jcc1
2027 WHERE gb.ledger_id = p_ledger_id
2028 AND jcc.ledger_id = p_ledger_id
2029 AND gb.code_combination_id = jcc.CODE_COMBINATION_ID
2030 AND jcc.company_segment = u.company_segment
2031 AND jcc.account_segment = u.account_segment
2032 -- AND nvl(jcc.cost_segment,0)= nvl(u.cost_center,0)
2033 -- AND nvl(jcc.project_number,'0') = nvl(u.project_number,'0')
2034 AND gb.currency_code = u.currency_code
2035 AND gb.period_name = u.period_name
2036 AND jcc1.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
2037 AND jcc1.ledger_id = p_ledger_id
2038 AND jcc1.company_segment = jcc.company_segment
2039 AND jcc1.account_segment = jcc.account_segment
2040 --AND jcc1.cost_segment = jcc.cost_segment
2041 --AND jcc1.other_columns = jcc.other_columns
2042 AND gb.actual_flag = 'A'
2043 )
2044 WHERE period_mon=1;*/
2045
2046 UPDATE ja_cn_account_balances_post_gt u
2047 SET FUNC_BEGIN_BALANCE_DR = nvl(FUNC_BEGIN_BALANCE_DR,0)
2048 , FUNC_BEGIN_BALANCE_CR = nvl(FUNC_BEGIN_BALANCE_CR,0)
2049 , ORIG_BEGIN_BALANCE_DR = nvl(ORIG_BEGIN_BALANCE_DR,0)
2050 , ORIG_BEGIN_BALANCE_CR = nvl(ORIG_BEGIN_BALANCE_CR,0)
2051 , FUNC_PERIOD_NET_DR = nvl(FUNC_PERIOD_NET_DR ,0)
2052 , FUNC_PERIOD_NET_CR = nvl(FUNC_PERIOD_NET_CR ,0)
2053 , ORIG_PERIOD_NET_DR = nvl(ORIG_PERIOD_NET_DR ,0)
2054 , ORIG_PERIOD_NET_CR = nvl(ORIG_PERIOD_NET_CR ,0);
2055
2056 /* --update the other month balance
2057 SELECT max(jcp.period_num - l_period_year*1000)
2058 INTO l_number
2059 FROM ja_cn_periods jcp;
2060
2061 FOR i IN 2..l_number
2062 LOOP
2063 UPDATE ja_cn_account_balances_post_gt u
2064 SET (FUNC_BEGIN_BALANCE_DR
2065 , FUNC_BEGIN_BALANCE_CR
2066 , ORIG_BEGIN_BALANCE_DR
2067 , ORIG_BEGIN_BALANCE_CR )=
2068 (SELECT FUNC_BEGIN_BALANCE_DR + FUNC_PERIOD_NET_DR
2069 , FUNC_BEGIN_BALANCE_CR + FUNC_PERIOD_NET_CR
2070 , ORIG_BEGIN_BALANCE_DR + ORIG_PERIOD_NET_DR
2071 , ORIG_BEGIN_BALANCE_CR + ORIG_PERIOD_NET_CR
2072 FROM ja_cn_account_balances_post_gt
2073 WHERE period_num=u.period_num - 1
2074 AND ledger_id = p_ledger_id
2075 AND LEGAL_ENTITY_ID = p_legal_entity_ID
2076 AND COMPANY_SEGMENT = u.company_segment
2077 AND CURRENCY_CODE = u.currency_code
2078 --AND nvl(COST_CENTER,0) = nvl(u.cost_center,0)
2079 AND THIRD_PARTY_TYPE IS NULL -- Update for updating balance also by customer or supplier
2080 AND THIRD_PARTY_ID IS NULL
2081 \*AND THIRD_PARTY_NUMBER IS NULL
2082 AND nvl(PROJECT_ID ,'0') = nvl(u.project_id ,'0')
2083 AND nvl(PROJECT_NUMBER ,'0') = nvl(u.project_number,'0')*\
2084 AND ACCOUNT_SEGMENT = u.account_segment
2085 AND PERSONNEL_ID IS NULL
2086 AND PERSONNEL_NUMBER IS NULL
2087 -- CNAO V2
2088 AND nvl(SEGMENT1,'0')= nvl(u.SEGMENT1,'0')
2089 AND nvl(SEGMENT2,'0')= nvl(u.SEGMENT2,'0')
2090 AND nvl(SEGMENT3,'0')= nvl(u.SEGMENT3,'0')
2091 AND nvl(SEGMENT4,'0')= nvl(u.SEGMENT4,'0')
2092 AND nvl(SEGMENT5,'0')= nvl(u.SEGMENT5,'0')
2093 AND nvl(SEGMENT6,'0')= nvl(u.SEGMENT6,'0')
2094 AND nvl(SEGMENT7,'0')= nvl(u.SEGMENT7,'0')
2095 AND nvl(SEGMENT8,'0')= nvl(u.SEGMENT8,'0')
2096 AND nvl(SEGMENT9,'0')= nvl(u.SEGMENT9,'0')
2097 AND nvl(SEGMENT10,'0')= nvl(u.SEGMENT10,'0')
2098 AND nvl(SEGMENT11,'0')= nvl(u.SEGMENT11,'0')
2099 AND nvl(SEGMENT12,'0')= nvl(u.SEGMENT12,'0')
2100 AND nvl(SEGMENT13,'0')= nvl(u.SEGMENT13,'0')
2101 AND nvl(SEGMENT14,'0')= nvl(u.SEGMENT14,'0')
2102 AND nvl(SEGMENT15,'0')= nvl(u.SEGMENT15,'0')
2103 AND nvl(SEGMENT16,'0')= nvl(u.SEGMENT16,'0')
2104 AND nvl(SEGMENT17,'0')= nvl(u.SEGMENT17,'0')
2105 AND nvl(SEGMENT18,'0')= nvl(u.SEGMENT18,'0')
2106 AND nvl(SEGMENT19,'0')= nvl(u.SEGMENT19,'0')
2107 AND nvl(SEGMENT20,'0')= nvl(u.SEGMENT20,'0')
2108 AND nvl(SEGMENT21,'0')= nvl(u.SEGMENT21,'0')
2109 AND nvl(SEGMENT22,'0')= nvl(u.SEGMENT22,'0')
2110 AND nvl(SEGMENT23,'0')= nvl(u.SEGMENT23,'0')
2111 AND nvl(SEGMENT24,'0')= nvl(u.SEGMENT24,'0')
2112 AND nvl(SEGMENT25,'0')= nvl(u.SEGMENT25,'0')
2113 AND nvl(SEGMENT26,'0')= nvl(u.SEGMENT26,'0')
2114 AND nvl(SEGMENT27,'0')= nvl(u.SEGMENT27,'0')
2115 AND nvl(SEGMENT28,'0')= nvl(u.SEGMENT28,'0')
2116 AND nvl(SEGMENT29,'0')= nvl(u.SEGMENT29,'0')
2117 AND nvl(SEGMENT30,'0')= nvl(u.SEGMENT30,'0'))
2118 WHERE u.period_mon=i;
2119
2120 END LOOP;*/
2121 --End: Deleted for fixing bug 9582957
2122
2123 -- replace the new balance of retain earning account
2124 DELETE ja_cn_account_balances u
2125 WHERE EXISTS
2126 (SELECT *
2127 FROM ja_cn_account_balances_post_gt t
2128 WHERE t.ledger_id =u.ledger_id
2129 AND t.LEGAL_ENTITY_ID =u.LEGAL_ENTITY_ID
2130 AND t.COMPANY_SEGMENT =u.COMPANY_SEGMENT
2131 AND t.CURRENCY_CODE =u.CURRENCY_CODE
2132 AND t.Period_Name =u.Period_Name --Added for fixing bug 9582957
2133 /* AND nvl(t.COST_CENTER,0) =nvl(u.COST_CENTER,0)*/
2134 AND t.ACCOUNT_SEGMENT =u.ACCOUNT_SEGMENT
2135 AND u.ACCOUNT_SEGMENT =lv_account_number --Added for fixing bug 9582957
2136 AND nvl(t.THIRD_PARTY_TYPE ,'0') =nvl(u.THIRD_PARTY_TYPE ,'0') -- Update for updating balance also by customer or supplier
2137 AND nvl(t.THIRD_PARTY_ID ,'0') =nvl(u.THIRD_PARTY_ID ,'0')
2138 /*AND nvl(t.THIRD_PARTY_NUMBER,'0') =nvl(u.THIRD_PARTY_NUMBER,'0')
2139 AND nvl(t.PROJECT_ID ,'0') =nvl(u.PROJECT_ID ,'0')
2140 AND nvl(t.PROJECT_NUMBER ,'0') =nvl(u.PROJECT_NUMBER ,'0')
2141 AND nvl(t.PERSONNEL_ID ,'0') =nvl(u.PERSONNEL_ID ,'0')
2142 AND nvl(t.PERSONNEL_NUMBER ,'0') =nvl(u.PERSONNEL_NUMBER ,'0')*/
2143 -- CNAO V2
2144 AND nvl(t.SEGMENT1, '0') = nvl(u.SEGMENT1,'0')
2145 AND nvl(t.SEGMENT2, '0') = nvl(u.SEGMENT2,'0')
2146 AND nvl(t.SEGMENT3, '0') = nvl(u.SEGMENT3,'0')
2147 AND nvl(t.SEGMENT4, '0') = nvl(u.SEGMENT4,'0')
2148 AND nvl(t.SEGMENT5, '0') = nvl(u.SEGMENT5,'0')
2149 AND nvl(t.SEGMENT6, '0') = nvl(u.SEGMENT6,'0')
2150 AND nvl(t.SEGMENT7, '0') = nvl(u.SEGMENT7,'0')
2151 AND nvl(t.SEGMENT8, '0') = nvl(u.SEGMENT8,'0')
2152 AND nvl(t.SEGMENT9, '0') = nvl(u.SEGMENT9,'0')
2153 AND nvl(t.SEGMENT10, '0') = nvl(u.SEGMENT10,'0')
2154 AND nvl(t.SEGMENT11, '0') = nvl(u.SEGMENT11,'0')
2155 AND nvl(t.SEGMENT12, '0') = nvl(u.SEGMENT12,'0')
2156 AND nvl(t.SEGMENT13, '0') = nvl(u.SEGMENT13,'0')
2157 AND nvl(t.SEGMENT14, '0') = nvl(u.SEGMENT14,'0')
2158 AND nvl(t.SEGMENT15, '0') = nvl(u.SEGMENT15,'0')
2159 AND nvl(t.SEGMENT16, '0') = nvl(u.SEGMENT16,'0')
2160 AND nvl(t.SEGMENT17, '0') = nvl(u.SEGMENT17,'0')
2161 AND nvl(t.SEGMENT18, '0') = nvl(u.SEGMENT18,'0')
2162 AND nvl(t.SEGMENT19, '0') = nvl(u.SEGMENT19,'0')
2163 AND nvl(t.SEGMENT20, '0') = nvl(u.SEGMENT20,'0')
2164 AND nvl(t.SEGMENT21, '0') = nvl(u.SEGMENT21,'0')
2165 AND nvl(t.SEGMENT22, '0') = nvl(u.SEGMENT22,'0')
2166 AND nvl(t.SEGMENT23, '0') = nvl(u.SEGMENT23,'0')
2167 AND nvl(t.SEGMENT24, '0') = nvl(u.SEGMENT24,'0')
2168 AND nvl(t.SEGMENT25, '0') = nvl(u.SEGMENT25,'0')
2169 AND nvl(t.SEGMENT26, '0') = nvl(u.SEGMENT26,'0')
2170 AND nvl(t.SEGMENT27, '0') = nvl(u.SEGMENT27,'0')
2171 AND nvl(t.SEGMENT28, '0') = nvl(u.SEGMENT28,'0')
2172 AND nvl(t.SEGMENT29, '0') = nvl(u.SEGMENT29,'0')
2173 AND nvl(t.SEGMENT30, '0') = nvl(u.SEGMENT30,'0')
2174 );
2175
2176
2177 INSERT INTO ja_cn_account_balances(
2178 ledger_id
2179 , LEGAL_ENTITY_ID
2180 , COMPANY_SEGMENT
2181 , PERIOD_NAME
2182 , CURRENCY_CODE
2183 --, COST_CENTER
2184 , THIRD_PARTY_TYPE -- Update for updating balance also by customer or supplier
2185 , THIRD_PARTY_ID
2186 /*, THIRD_PARTY_NUMBER
2187 , PROJECT_ID
2188 , PROJECT_NUMBER
2189 , PROJECT_SOURCE*/
2190 , ACCOUNT_SEGMENT
2191 , PERSONNEL_ID
2192 , PERSONNEL_NUMBER
2193 , FUNC_BEGIN_BALANCE_DR
2194 , FUNC_BEGIN_BALANCE_CR
2195 , ORIG_BEGIN_BALANCE_DR
2196 , ORIG_BEGIN_BALANCE_CR
2197 , FUNC_PERIOD_NET_DR
2198 , FUNC_PERIOD_NET_CR
2199 , ORIG_PERIOD_NET_DR
2200 , ORIG_PERIOD_NET_CR
2201 , PERIOD_MON
2202 , ACCOUNT_TYPE
2203 , CREATED_BY
2204 , CREATION_DATE
2205 , LAST_UPDATED_BY
2206 , LAST_UPDATE_DATE
2207 , LAST_UPDATE_LOGIN
2208 , SEGMENT1
2209 , SEGMENT2
2210 , SEGMENT3
2211 , SEGMENT4
2212 , SEGMENT5
2213 , SEGMENT7
2214 , SEGMENT8
2215 , SEGMENT9
2216 , SEGMENT10
2217 , SEGMENT11
2218 , SEGMENT12
2219 , SEGMENT13
2220 , SEGMENT14
2221 , SEGMENT15
2222 , SEGMENT16
2223 , SEGMENT17
2224 , SEGMENT18
2225 , SEGMENT19
2226 , SEGMENT20
2227 , SEGMENT21
2228 , SEGMENT22
2229 , SEGMENT23
2230 , SEGMENT24
2231 , SEGMENT25
2232 , SEGMENT26
2233 , SEGMENT27
2234 , SEGMENT28
2235 , SEGMENT29
2236 , SEGMENT30
2237 )
2238 SELECT ledger_id
2239 , LEGAL_ENTITY_ID
2240 , COMPANY_SEGMENT
2241 , PERIOD_NAME
2242 , CURRENCY_CODE
2243 --, COST_CENTER
2244 , THIRD_PARTY_TYPE -- Update for updating balance also by customer or supplier
2245 , THIRD_PARTY_ID
2246 /*, THIRD_PARTY_NUMBER
2247 , PROJECT_ID
2248 , PROJECT_NUMBER
2249 , PROJECT_SOURCE*/
2250 , ACCOUNT_SEGMENT
2251 , PERSONNEL_ID
2252 , PERSONNEL_NUMBER
2253 , FUNC_BEGIN_BALANCE_DR
2254 , FUNC_BEGIN_BALANCE_CR
2255 , ORIG_BEGIN_BALANCE_DR
2256 , ORIG_BEGIN_BALANCE_CR
2257 , FUNC_PERIOD_NET_DR
2258 , FUNC_PERIOD_NET_CR
2259 , ORIG_PERIOD_NET_DR
2260 , ORIG_PERIOD_NET_CR
2261 , PERIOD_MON
2262 , ACCOUNT_TYPE
2263 , fnd_global.USER_ID
2264 , SYSDATE
2265 , fnd_global.USER_ID
2266 , SYSDATE
2267 , fnd_global.LOGIN_ID
2268 , SEGMENT1
2269 , SEGMENT2
2270 , SEGMENT3
2271 , SEGMENT4
2272 , SEGMENT5
2273 , SEGMENT7
2274 , SEGMENT8
2275 , SEGMENT9
2276 , SEGMENT10
2277 , SEGMENT11
2278 , SEGMENT12
2279 , SEGMENT13
2280 , SEGMENT14
2281 , SEGMENT15
2282 , SEGMENT16
2283 , SEGMENT17
2284 , SEGMENT18
2285 , SEGMENT19
2286 , SEGMENT20
2287 , SEGMENT21
2288 , SEGMENT22
2289 , SEGMENT23
2290 , SEGMENT24
2291 , SEGMENT25
2292 , SEGMENT26
2293 , SEGMENT27
2294 , SEGMENT28
2295 , SEGMENT29
2296 , SEGMENT30
2297 FROM ja_cn_account_balances_post_gt;
2298
2299 /* -- delete the old parent account balance
2300 DELETE ja_cn_account_balances jcb
2301 WHERE jcb.ledger_id = p_ledger_id
2302 AND jcb.legal_entity_id = p_legal_entity_ID
2303 AND EXISTS (SELECT jcp.ledger_id
2304 FROM ja_cn_periods jcp
2305 , fnd_flex_values ffv
2306 WHERE jcp.period_name=jcb.period_name
2307 AND jcp.ledger_id = p_ledger_id
2308 AND jcp.period_num BETWEEN l_period_num AND l_last_period_num
2309 AND ffv.flex_value_set_id = l_flex_value_set_id
2310 AND ffv.flex_value = jcb.account_segment
2311 AND ffv.summary_flag = 'Y'
2312 );
2313
2314 -- insert the parent account balance
2315 INSERT INTO ja_cn_account_balances(
2316 ledger_id
2317 , LEGAL_ENTITY_ID
2318 , COMPANY_SEGMENT
2319 , PERIOD_NAME
2320 , CURRENCY_CODE
2321 , COST_CENTER
2322 , THIRD_PARTY_TYPE
2323 , THIRD_PARTY_ID
2324 , THIRD_PARTY_NUMBER
2325 , PROJECT_ID
2326 , PROJECT_NUMBER
2327 , PROJECT_SOURCE
2328 , ACCOUNT_SEGMENT
2329 , PERSONNEL_ID
2330 , PERSONNEL_NUMBER
2331 , FUNC_BEGIN_BALANCE_DR
2332 , FUNC_BEGIN_BALANCE_CR
2333 , ORIG_BEGIN_BALANCE_DR
2334 , ORIG_BEGIN_BALANCE_CR
2335 , FUNC_PERIOD_NET_DR
2336 , FUNC_PERIOD_NET_CR
2337 , ORIG_PERIOD_NET_DR
2338 , ORIG_PERIOD_NET_CR
2339 , PERIOD_MON
2340 , ACCOUNT_TYPE
2341 , CREATED_BY
2342 , CREATION_DATE
2343 , LAST_UPDATED_BY
2344 , LAST_UPDATE_DATE
2345 , LAST_UPDATE_LOGIN )
2346 SELECT p_ledger_id
2347 , p_legal_entity_ID
2348 , jcc.company_segment
2349 , gb.period_name
2350 , l_ret_currence_code
2351 , jcc.cost_segment
2352 , NULL
2353 , NULL
2354 , NULL
2355 , NULL
2356 , NULL
2357 , 'COA'
2358 , jcc.account_segment
2359 , NULL
2360 , NULL
2361 , SUM(nvl(gb.begin_balance_dr_beq,0))
2362 , SUM(nvl(gb.begin_balance_cr_beq,0))
2363 , SUM(nvl(gb.begin_balance_dr,0))
2364 , SUM(nvl(gb.begin_balance_cr,0))
2365 , SUM(nvl(gb.period_net_dr_beq,0))
2366 , SUM(nvl(gb.period_net_cr_beq,0))
2367 , SUM(nvl(gb.period_net_dr,0))
2368 , SUM(nvl(gb.period_net_cr,0))
2369 , gb.period_num
2370 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
2371 , fnd_global.USER_ID
2372 , SYSDATE
2373 , fnd_global.USER_ID
2374 , SYSDATE
2375 , fnd_global.LOGIN_ID
2376 FROM gl_balances gb
2377 , ja_cn_code_combination_v jcc
2378 , fnd_flex_values ffv
2379 WHERE gb.ledger_id = p_ledger_id
2380 AND jcc.ledger_id = p_ledger_id
2381 AND gb.code_combination_id = jcc.CODE_COMBINATION_ID
2382 AND jcc.account_segment = ffv.flex_value
2383 AND ffv.flex_value_set_id = l_flex_value_set_id
2384 AND ffv.summary_flag = 'Y'
2385 AND gb.actual_flag = 'Y'
2386 AND gb.currency_code = l_ret_currence_code
2387 AND gb.period_year*1000+gb.period_num BETWEEN l_period_num AND l_last_period_num
2388 GROUP BY jcc.company_segment
2389 , gb.period_name
2390 , l_ret_currence_code
2391 , jcc.cost_segment
2392 , jcc.account_segment
2393 , gb.period_num
2394 , ffv.COMPILED_VALUE_ATTRIBUTES;*/
2395
2396 -- delete the old parent account balance
2397 DELETE ja_cn_account_balances jcb
2398 WHERE jcb.ledger_id = p_ledger_id
2399 AND jcb.legal_entity_id = p_legal_entity_ID
2400 AND EXISTS (SELECT jcp.ledger_id
2401 FROM ja_cn_periods jcp
2402 , fnd_flex_values ffv
2403 WHERE jcp.period_name=jcb.period_name
2404 AND jcp.ledger_id = p_ledger_id
2405 AND jcp.period_num BETWEEN l_period_num AND l_last_period_num
2406 AND ffv.flex_value_set_id = l_flex_value_set_id
2407 AND ffv.flex_value = jcb.account_segment
2408 AND ffv.summary_flag = 'Y'
2409 );
2410
2411 --Delete data from JA_CN_ACCOUNT_CHILDREN_GT for fixing bug 9582957
2412 DELETE FROM JA_CN_ACCOUNT_CHILDREN_GT;
2413 -- generate the account structure to lowest level
2414 INSERT INTO JA_CN_ACCOUNT_CHILDREN_GT(
2415 parent_account
2416 , child_account
2417 , summary_flag
2418 , PARENT_TYPE
2419 )
2420 SELECT H.PARENT_FLEX_VALUE
2421 , V.FLEX_VALUE
2422 , V.SUMMARY_FLAG
2423 , substr(v2.compiled_value_attributes,5,1)
2424 FROM FND_FLEX_VALUES V
2425 , FND_FLEX_VALUE_NORM_HIERARCHY H
2426 , FND_FLEX_VALUES V2
2427 WHERE v.flex_value_set_id = l_flex_value_set_id
2428 AND h.flex_value_set_id = l_flex_value_set_id
2429 AND V.FLEX_VALUE BETWEEN H.CHILD_FLEX_VALUE_LOW AND H.CHILD_FLEX_VALUE_HIGH
2430 AND ((V.SUMMARY_FLAG = 'Y' AND H.RANGE_ATTRIBUTE = 'P') OR
2431 (V.SUMMARY_FLAG = 'N' AND H.RANGE_ATTRIBUTE = 'C'))
2432 AND v2.flex_value_set_id = l_flex_value_set_id
2433 AND h.PARENT_FLEX_VALUE = v2.FLEX_VALUE;
2434
2435 i:=0;
2436 LOOP
2437 i:=i+1;
2438 IF i>6
2439 THEN
2440 EXIT;
2441 END IF;
2442
2443 INSERT INTO JA_CN_ACCOUNT_CHILDREN_GT(
2444 parent_account
2445 , child_account
2446 , summary_flag
2447 , PARENT_TYPE
2448 )
2449 SELECT DISTINCT jca.parent_account
2450 , v.FLEX_VALUE
2451 , v.SUMMARY_FLAG
2452 , jca.parent_type
2453 FROM JA_CN_ACCOUNT_CHILDREN_GT jca
2454 , FND_FLEX_VALUES v
2455 , FND_FLEX_VALUE_NORM_HIERARCHY h
2456 WHERE jca.child_account = h.parent_flex_value
2457 AND v.flex_value_set_id = l_flex_value_set_id
2458 AND h.flex_value_set_id = l_flex_value_set_id
2459 AND V.FLEX_VALUE BETWEEN H.CHILD_FLEX_VALUE_LOW AND H.CHILD_FLEX_VALUE_HIGH
2460 AND ((V.SUMMARY_FLAG = 'Y' AND H.RANGE_ATTRIBUTE = 'P') OR
2461 (V.SUMMARY_FLAG = 'N' AND H.RANGE_ATTRIBUTE = 'C'))
2462 AND NOT EXISTS(SELECT *
2463 FROM JA_CN_ACCOUNT_CHILDREN_GT t
2464 WHERE t.parent_account = jca.parent_account
2465 AND t.child_account = v.FLEX_VALUE);
2466
2467 IF SQL%ROWCOUNT = 0 THEN
2468 EXIT;
2469 END IF;
2470
2471 END LOOP;
2472
2473 DELETE JA_CN_ACCOUNT_CHILDREN_GT WHERE summary_flag = 'Y';
2474
2475 --insert the parent account balance
2476 INSERT INTO ja_cn_account_balances(
2477 ledger_id
2478 , LEGAL_ENTITY_ID
2479 , COMPANY_SEGMENT
2480 , PERIOD_NAME
2481 , CURRENCY_CODE
2482 --, COST_CENTER
2483 , THIRD_PARTY_TYPE-- Update for updating balance also by customer or supplier
2484 , THIRD_PARTY_ID
2485 /*, THIRD_PARTY_NUMBER
2486 , PROJECT_ID
2487 , PROJECT_NUMBER
2488 , PROJECT_SOURCE*/
2489 , ACCOUNT_SEGMENT
2490 , PERSONNEL_ID
2491 , PERSONNEL_NUMBER
2492 , FUNC_BEGIN_BALANCE_DR
2493 , FUNC_BEGIN_BALANCE_CR
2494 , ORIG_BEGIN_BALANCE_DR
2495 , ORIG_BEGIN_BALANCE_CR
2496 , FUNC_PERIOD_NET_DR
2497 , FUNC_PERIOD_NET_CR
2498 , ORIG_PERIOD_NET_DR
2499 , ORIG_PERIOD_NET_CR
2500 , PERIOD_MON
2501 , ACCOUNT_TYPE
2502 , CREATED_BY
2503 , CREATION_DATE
2504 , LAST_UPDATED_BY
2505 , LAST_UPDATE_DATE
2506 , LAST_UPDATE_LOGIN
2507 , SEGMENT1
2508 , SEGMENT2
2509 , SEGMENT3
2510 , SEGMENT4
2511 , SEGMENT5
2512 , SEGMENT6
2513 , SEGMENT7
2514 , SEGMENT8
2515 , SEGMENT9
2516 , SEGMENT10
2517 , SEGMENT11
2518 , SEGMENT12
2519 , SEGMENT13
2520 , SEGMENT14
2521 , SEGMENT15
2522 , SEGMENT16
2523 , SEGMENT17
2524 , SEGMENT18
2525 , SEGMENT19
2526 , SEGMENT20
2527 , SEGMENT21
2528 , SEGMENT22
2529 , SEGMENT23
2530 , SEGMENT24
2531 , SEGMENT25
2532 , SEGMENT26
2533 , SEGMENT27
2534 , SEGMENT28
2535 , SEGMENT29
2536 , SEGMENT30)
2537 SELECT p_ledger_id
2538 , p_legal_entity_ID
2539 , jcb.company_segment
2540 , jcb.period_name
2541 --Added for fixing bug 9582957 by Chaoqun on 27-May-2010
2542 --Calculate balance based on currency to support foreign currency
2543 --, l_ret_currence_code
2544 , jcb.currency_code
2545 --, NULL
2546 , NULL-- Update for updating balance also by customer or supplier
2547 , NULL
2548 /*, NULL
2549 , NULL
2550 , NULL
2551 , 'COA'*/
2552 , jcc.parent_account
2553 , NULL
2554 , NULL
2555 , SUM(nvl(FUNC_BEGIN_BALANCE_DR,0))
2556 , SUM(nvl(FUNC_BEGIN_BALANCE_CR,0))
2557 , SUM(nvl(FUNC_BEGIN_BALANCE_DR,0))
2558 , SUM(nvl(FUNC_BEGIN_BALANCE_CR,0))
2559 , SUM(nvl(FUNC_PERIOD_NET_DR,0))
2560 , SUM(nvl(FUNC_PERIOD_NET_CR,0))
2561 , SUM(nvl(FUNC_PERIOD_NET_DR,0))
2562 , SUM(nvl(FUNC_PERIOD_NET_CR,0))
2563 , jcb.period_mon
2564 , jcc.parent_type
2565 , fnd_global.USER_ID
2566 , SYSDATE
2567 , fnd_global.USER_ID
2568 , SYSDATE
2569 , fnd_global.LOGIN_ID
2570 , NULL
2571 , NULL
2572 , NULL
2573 , NULL
2574 , NULL
2575 , NULL
2576 , NULL
2577 , NULL
2578 , NULL
2579 , NULL
2580 , NULL
2581 , NULL
2582 , NULL
2583 , NULL
2584 , NULL
2585 , NULL
2586 , NULL
2587 , NULL
2588 , NULL
2589 , NULL
2590 , NULL
2591 , NULL
2592 , NULL
2593 , NULL
2594 , NULL
2595 , NULL
2596 , NULL
2597 , NULL
2598 , NULL
2599 , NULL
2600 FROM ja_cn_account_balances jcb
2601 , JA_CN_ACCOUNT_CHILDREN_GT jcc
2602 , ja_cn_periods jcp
2603 WHERE jcc.child_account = jcb.account_segment
2604 AND jcp.ledger_id = p_ledger_id
2605 AND jcb.legal_entity_id = p_legal_entity_ID
2606 AND jcb.ledger_id = p_ledger_id
2607 AND jcb.period_name = jcp.period_name
2608 AND jcp.period_num BETWEEN l_period_num AND l_last_period_num
2609 GROUP BY jcb.company_segment
2610 , jcb.period_name
2611 , jcc.parent_account
2612 , jcb.period_mon
2613 , jcc.parent_type
2614 --Added for fixing bug 9582957 by Chaoqun on 27-May-2010
2615 --Calculate balance based on currency to support foreign currency
2616 , jcb.currency_code;
2617
2618 /* --initiate the working area
2619 DELETE ja_cn_account_balances_post_gt;*/
2620
2621 --insert all parent account having only non-parent account
2622 /* INSERT INTO ja_cn_account_balances(
2623 ledger_id
2624 , LEGAL_ENTITY_ID
2625 , COMPANY_SEGMENT
2626 , PERIOD_NAME
2627 , CURRENCY_CODE
2628 , COST_CENTER
2629 , THIRD_PARTY_TYPE
2630 , THIRD_PARTY_ID
2631 , THIRD_PARTY_NUMBER
2632 , PROJECT_ID
2633 , PROJECT_NUMBER
2634 , PROJECT_SOURCE
2635 , ACCOUNT_SEGMENT
2636 , PERSONNEL_ID
2637 , PERSONNEL_NUMBER
2638 , FUNC_BEGIN_BALANCE_DR
2639 , FUNC_BEGIN_BALANCE_CR
2640 , ORIG_BEGIN_BALANCE_DR
2641 , ORIG_BEGIN_BALANCE_CR
2642 , FUNC_PERIOD_NET_DR
2643 , FUNC_PERIOD_NET_CR
2644 , ORIG_PERIOD_NET_DR
2645 , ORIG_PERIOD_NET_CR
2646 , PERIOD_MON
2647 , ACCOUNT_TYPE
2648 , CREATED_BY
2649 , CREATION_DATE
2650 , LAST_UPDATED_BY
2651 , LAST_UPDATE_DATE
2652 , LAST_UPDATE_LOGIN )
2653 SELECT p_ledger_id
2654 , p_legal_entity_ID
2655 , jcc.company_segment
2656 , gb.period_name
2657 , l_ret_currence_code
2658 , jcc.cost_segment
2659 , NULL
2660 , NULL
2661 , NULL
2662 , NULL
2663 , NULL
2664 , 'COA'
2665 , jcc.account_segment
2666 , NULL
2667 , NULL
2668 , SUM(nvl(gb.begin_balance_dr_beq,0))
2669 , SUM(nvl(gb.begin_balance_cr_beq,0))
2670 , SUM(nvl(gb.begin_balance_dr,0))
2671 , SUM(nvl(gb.begin_balance_cr,0))
2672 , SUM(nvl(gb.period_net_dr_beq,0))
2673 , SUM(nvl(gb.period_net_cr_beq,0))
2674 , SUM(nvl(gb.period_net_dr,0))
2675 , SUM(nvl(gb.period_net_cr,0))
2676 , gb.period_num
2677 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
2678 , fnd_global.USER_ID
2679 , SYSDATE
2680 , fnd_global.USER_ID
2681 , SYSDATE
2682 , fnd_global.LOGIN_ID
2683 FROM ja_cn_periods jcp
2684 , ja_cn_account_balances jca
2685 , FND_FLEX_VALUE_CHILDREN_V ffvc
2686 , fnd_flex_values ffv
2687 WHERE jca.account_segment = ffvc.flex_value*/
2688
2689 IF( G_PROC_LEVEL >= g_debug_devel )
2690 THEN
2691 FND_LOG.STRING(G_PROC_LEVEL
2692 ,G_MODULE_PREFIX||l_procedure_name||'.end'
2693 ,'End procedure');
2694 END IF; --( G_PROC_LEVEL >= g_debug_devel)
2695 EXCEPTION
2696 WHEN OTHERS THEN
2697 IF(FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel)
2698 THEN
2699 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
2700 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
2701 , SQLCODE||SQLERRM);
2702 END IF;
2703 RAISE;
2704 END update_retained_parent_account;
2705
2706 --==========================================================================
2707 -- PROCEDURE NAME:
2708 --
2709 -- Create_Dynamic_Index Public
2710 --
2711 -- DESCRIPTION:
2712 --
2713 -- This procedure is Re-create dynamic unique index for table JA_CN_ACCOUNT_BALANCES
2714 --
2715 -- PARAMETERS:
2716 -- In: pn_ledger_id ledger id
2717 -- pn_legal_entity_id legal entity id
2718 --
2719 -- DESIGN REFERENCES:
2720 -- GL_Chaoqun.doc
2721 --
2722 -- CHANGE HISTORY:
2723 -- 26-Mar-2010 Chaoqun Wu created
2724 --==========================================================================
2725 --
2726 PROCEDURE Create_Dynamic_Index
2727 (pn_ledger_id IN NUMBER
2728 ,pn_legal_entity_id IN NUMBER
2729 )
2730 IS
2731
2732 lv_procedure_name VARCHAR2(40) := 'Create_Dynamic_Index';
2733 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2734 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2735
2736 lv_create_sql VARCHAR2(2000);
2737 lv_drop_sql VARCHAR2(200);
2738
2739 CURSOR sub_segment_cur
2740 IS
2741 SELECT sam.SUBSIDIARY_SEGMENT_CODE
2742 FROM JA_CN_SUB_ACC_MAPPING sam
2743 ,GL_LEDGER_LE_V gllv
2744 WHERE sam.CHART_OF_ACCOUNTS_ID = gllv.CHART_OF_ACCOUNTS_ID
2745 AND gllv.LEDGER_ID = pn_ledger_id --parameter: pn_ledger_id
2746 AND gllv.LEGAL_ENTITY_ID = pn_legal_entity_id; --parameter: pn_legal_entity_id;
2747
2748 BEGIN
2749 --logging for debug
2750 IF (ln_proc_level >= ln_dbg_level)
2751 THEN
2752 FND_LOG.STRING(ln_proc_level,
2753 G_MODULE_PREFIX || '.' || lv_procedure_name ||
2754 '.begin',
2755 'Enter procedure');
2756 END IF; --l_proc_level>=l_dbg_level
2757
2758 lv_drop_sql := 'drop index JA_CN_ACCOUNT_BALANCES_U1';
2759
2760 lv_create_sql := 'create unique index JA_CN_ACCOUNT_BALANCES_U1 on '||
2761 'JA_CN_ACCOUNT_BALANCES (LEDGER_ID, LEGAL_ENTITY_ID, COMPANY_SEGMENT,'||
2762 ' PERIOD_NAME, CURRENCY_CODE, ACCOUNT_SEGMENT,THIRD_PARTY_TYPE,THIRD_PARTY_ID';
2763
2764 FOR v_row IN sub_segment_cur
2765 LOOP
2766 lv_create_sql := lv_create_sql || ',' || v_row.SUBSIDIARY_SEGMENT_CODE;
2767 END LOOP;
2768
2769 lv_create_sql := lv_create_sql || ') ' ||
2770 'tablespace APPS_TS_TX_IDX '||
2771 'pctfree 10 '||
2772 'initrans 11 '||
2773 'maxtrans 255 '||
2774 'storage '||
2775 '('||
2776 ' initial 128K'||
2777 ' next 128K'||
2778 ' minextents 1'||
2779 ' maxextents unlimited'||
2780 ' pctincrease 0'||
2781 ')';
2782
2783 EXECUTE IMMEDIATE lv_drop_sql;
2784 EXECUTE IMMEDIATE lv_create_sql;
2785
2786 --logging for debug
2787 IF (ln_proc_level >= ln_dbg_level)
2788 THEN
2789 FND_LOG.STRING(ln_proc_level,
2790 G_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
2791 'Exit procedure');
2792 END IF; -- (ln_proc_level>=ln_dbg_level)
2793
2794 EXCEPTION
2795 WHEN OTHERS THEN
2796 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2797 THEN
2798 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
2799 G_MODULE_PREFIX || '.' || lv_procedure_name ||
2800 '.Other_Exception ',
2801 SQLCODE || SQLERRM);
2802 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2803 EXECUTE IMMEDIATE lv_create_sql;
2804 END Create_Dynamic_Index;
2805
2806 --==========================================================================
2807 -- PROCEDURE NAME:
2808 -- post_journal_itemized Public
2809 --
2810 -- DESCRIPTION:
2811 -- This procedure is used to open a period which had never post
2812 -- journal from "Itemized journal table" to "Itemized balance table"
2813 --
2814 -- PARAMETERS:
2815 -- In: p_period_name the end period name in which
2816 -- the CNAO journal should be processed
2817 -- p_ledger_id Ledger ID
2818 -- p_legal_entity_ID Legal entity id
2819
2820 --
2821 -- DESIGN REFERENCES:
2822 -- None
2823 --
2824 -- CHANGE HISTORY:
2825 -- 02/21/2006 Jogen Hu Created
2826 -- 04/28/2007 Qingjun Zhao Change SOB to Ledger for upgrade
2827 -- from 11i to R12
2828 -- 05/02/2020 Shujan Yan Change for CNAO V2
2829 -- 25/03/2010 Chaoqun Wu Update for posting journals also by customer
2830 -- or supplier
2831 -- 18/05/2010 Chaoqun Wu Fixing bug# 9662105
2832 --===========================================================================
2833 PROCEDURE post_journal_itemized
2834 ( p_period_name IN VARCHAR2
2835 , p_ledger_id IN NUMBER
2836 , p_legal_entity_ID IN NUMBER
2837 )
2838 IS
2839 l_procedure_name VARCHAR2(30):='post_journal_itemized';
2840 --l_last_open_period_year ja_cn_periods.period_name%TYPE;
2841 --l_last_open_period ja_cn_periods.period_name%TYPE;
2842 l_period_year ja_cn_periods.period_year%TYPE;
2843 l_period_num ja_cn_periods.period_num%TYPE;
2844 l_flex_value_set_id FND_FLEX_VALUES.Flex_Value_Set_Id%TYPE;
2845 l_row_count NUMBER;
2846 l_balance_rec cnao_balance_rec;
2847
2848 l_earliest_changed_year ja_cn_periods.period_year%TYPE;
2849 l_earliest_changed_period ja_cn_periods.period_name%TYPE;
2850
2851 CURSOR c_periods(pc_year IN NUMBER
2852 ,pc_num IN NUMBER) IS
2853 SELECT DISTINCT gp.period_name,gp.period_year,gp.period_num
2854 FROM /*JA_CN_JOURNAL_LINES jjl
2855 , */gl_periods gp
2856 , gl_ledgers led
2857 WHERE /*jjl.ledger_id = p_ledger_id
2858 AND jjl.legal_entity_id = p_legal_entity_ID
2859 AND jjl.status = 'U'
2860 AND */led.ledger_id = p_ledger_id
2861 --AND jjl.period_name = gp.period_name
2862 AND gp.period_set_name = led.period_set_name
2863 AND gp.period_type = led.accounted_period_type
2864 AND (gp.period_year<pc_year
2865 OR (gp.period_year=pc_year AND gp.period_num<=pc_num))
2866 ORDER BY gp.period_year,gp.period_num;
2867
2868 CURSOR c_journal_lines(pc_flex_value_set_id IN NUMBER
2869 ,pc_period_name IN VARCHAR2) IS
2870 SELECT --jjl.journal_number
2871 SUM(nvl(jjl.ENTERED_DR,0)) entered_dr
2872 , SUM(nvl(jjl.ENTERED_CR,0)) entered_cr
2873 , SUM(nvl(jjl.ACCOUNTED_DR,0)) accounted_dr
2874 , SUM(nvl(jjl.ACCOUNTED_CR,0)) accounted_cr
2875 , jjl.CURRENCY_CODE
2876 , jjl.CURRENCY_CONVERSION_RATE
2877 , jjl.COMPANY_SEGMENT
2878 , jjl.CODE_COMBINATION_ID
2879 --, jjl.COST_CENTER
2880 , jjl.THIRD_PARTY_ID --Updated for posting journals by customer or supplier
2881 -- , jjl.THIRD_PARTY_NUMBER
2882 , jjl.third_party_type
2883 /*, jjl.PERSONNEL_ID
2884 , jjl.PERSONNEL_NUMBER
2885 , jjl.PROJECT_ID
2886 , jjl.PROJECT_NUMBER
2887 , jjl.project_source*/
2888 , substr(COMPILED_VALUE_ATTRIBUTES,5,1) account_type
2889 , jjl.segment1
2890 , jjl.segment2
2891 , jjl.segment3
2892 , jjl.segment4
2893 , jjl.segment5
2894 , jjl.segment6
2895 , jjl.segment7
2896 , jjl.segment8
2897 , jjl.segment9
2898 , jjl.segment10
2899 , jjl.segment11
2900 , jjl.segment12
2901 , jjl.segment13
2902 , jjl.segment14
2903 , jjl.segment15
2904 , jjl.segment16
2905 , jjl.segment17
2906 , jjl.segment18
2907 , jjl.segment19
2908 , jjl.segment20
2909 , jjl.segment21
2910 , jjl.segment22
2911 , jjl.segment23
2912 , jjl.segment24
2913 , jjl.segment25
2914 , jjl.segment26
2915 , jjl.segment27
2916 , jjl.segment28
2917 , jjl.segment29
2918 , jjl.segment30
2919 , jjl.ACCOUNT_SEGMENT
2920 , jjl.period_name
2921 FROM JA_CN_JOURNAL_LINES jjl
2922 , FND_FLEX_VALUES ffv
2923 WHERE jjl.ledger_id = p_ledger_id
2924 AND jjl.legal_entity_id = p_legal_entity_ID
2925 AND jjl.status = 'U'
2926 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
2927 AND ffv.FLEX_VALUE_SET_ID=pc_flex_value_set_id
2928 AND ffv.flex_value =jjl.account_segment
2929 AND jjl.period_name =pc_period_name
2930 GROUP BY --jjl.journal_number
2931 jjl.CURRENCY_CODE
2932 , jjl.CURRENCY_CONVERSION_RATE
2933 , jjl.COMPANY_SEGMENT
2934 , jjl.CODE_COMBINATION_ID
2935 -- , jjl.COST_CENTER
2936 , jjl.THIRD_PARTY_ID --Updated for posting journals by customer or supplier
2937 --, jjl.THIRD_PARTY_NUMBER
2938 , jjl.third_party_type
2939 /*, jjl.PERSONNEL_ID
2940 , jjl.PERSONNEL_NUMBER
2941 , jjl.PROJECT_ID
2942 , jjl.PROJECT_NUMBER
2943 , jjl.project_source*/
2944 , COMPILED_VALUE_ATTRIBUTES
2945 , jjl.segment1
2946 , jjl.segment2
2947 , jjl.segment3
2948 , jjl.segment4
2949 , jjl.segment5
2950 , jjl.segment6
2951 , jjl.segment7
2952 , jjl.segment8
2953 , jjl.segment9
2954 , jjl.segment10
2955 , jjl.segment11
2956 , jjl.segment12
2957 , jjl.segment13
2958 , jjl.segment14
2959 , jjl.segment15
2960 , jjl.segment16
2961 , jjl.segment17
2962 , jjl.segment18
2963 , jjl.segment19
2964 , jjl.segment20
2965 , jjl.segment21
2966 , jjl.segment22
2967 , jjl.segment23
2968 , jjl.segment24
2969 , jjl.segment25
2970 , jjl.segment26
2971 , jjl.segment27
2972 , jjl.segment28
2973 , jjl.segment29
2974 , jjl.segment30
2975 , jjl.ACCOUNT_SEGMENT
2976 , jjl.period_name
2977 ;
2978
2979 CURSOR c_after_periods(pc_period_num IN NUMBER) IS
2980 SELECT period_name
2981 , (period_num - period_year*1000) period_month
2982 FROM ja_cn_periods
2983 WHERE period_num>pc_period_num
2984 AND ledger_id=p_ledger_id
2985 AND status='O';
2986
2987 CURSOR c_after_periods_in_year( pc_period_num IN NUMBER
2988 , pc_period_year IN NUMBER) IS
2989 SELECT period_name
2990 , (period_num - period_year*1000) period_month
2991 FROM ja_cn_periods
2992 WHERE period_num>pc_period_num
2993 AND status='O'
2994 AND period_year=pc_period_year
2995 AND ledger_id=p_ledger_id;
2996
2997 BEGIN
2998 IF( G_PROC_LEVEL >= g_debug_devel )
2999 THEN
3000 FND_LOG.STRING(G_PROC_LEVEL
3001 ,G_MODULE_PREFIX||l_procedure_name||'.begin'
3002 ,'Begin procedure');
3003 END IF; --( G_PROC_LEVEL >= g_debug_devel)
3004
3005 --Create dynamic index for JA_CN_ACCOUNT_BALANCES, added by Chaoqun on 26-Mar-2010
3006 /* Create_Dynamic_Index(pn_ledger_id => p_ledger_id --Delete for fixing bug# 9662105
3007 ,pn_legal_entity_id => p_legal_entity_ID
3008 );*/
3009 --get period year and num
3010 SELECT gp.period_year
3011 , gp.period_num
3012 INTO l_period_year
3013 , l_period_num
3014 FROM gl_periods gp
3015 , gl_ledgers led
3016 WHERE gp.period_name=p_period_name
3017 AND gp.period_set_name=led.period_set_name
3018 AND led.ledger_id=p_ledger_id;
3019
3020 l_earliest_changed_year:=l_period_year;
3021 l_earliest_changed_period:=p_period_name;
3022
3023 IF( G_STATEMENT_LEVEL >= g_debug_devel )
3024 THEN
3025 put_log(G_MODULE_PREFIX||l_procedure_name||'.period year and num'
3026 ,l_period_year||':'||l_period_num);
3027 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
3028
3029 --get the flexfield set ID for chart of account
3030 SELECT ifs.flex_value_set_id
3031 INTO l_flex_value_set_id
3032 FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
3033 , gl_ledgers led
3034 , Fnd_Id_Flex_Segments ifs
3035 WHERE sav.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
3036 AND sav.ID_FLEX_CODE = 'GL#'
3037 AND sav.APPLICATION_ID = 101
3038 AND sav.attribute_value = 'Y'
3039 AND sav.ID_FLEX_NUM = led.chart_of_accounts_id
3040 AND ifs.application_id = 101
3041 AND ifs.Id_Flex_Code = 'GL#'
3042 AND ifs.id_flex_num = led.chart_of_accounts_id
3043 AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
3044 AND led.ledger_id = p_ledger_id;
3045
3046 IF( G_STATEMENT_LEVEL >= g_debug_devel )
3047 THEN
3048 put_log(G_MODULE_PREFIX||l_procedure_name||'.l_flex_value_set_id'
3049 ,l_flex_value_set_id);
3050 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
3051
3052 FOR rec_period IN c_periods(l_period_year,l_period_num)
3053 LOOP
3054
3055 SAVEPOINT each_periods;
3056 put_log(G_MODULE_PREFIX||l_procedure_name||'.period name'
3057 ,rec_period.period_name||':'||to_char(SYSDATE,'hh:mi:ss'));
3058 SELECT COUNT(*)
3059 INTO l_row_count
3060 FROM ja_cn_periods
3061 WHERE period_name=rec_period.period_name
3062 AND ledger_id=p_ledger_id;
3063
3064 IF( G_STATEMENT_LEVEL >= g_debug_devel )
3065 THEN
3066 put_log(G_MODULE_PREFIX||l_procedure_name||'.period rowcount'
3067 ,l_row_count);
3068 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
3069
3070 IF l_row_count=0 --current period is processed first time
3071 THEN
3072 open_period( p_period_name => rec_period.period_name
3073 , p_ledger_id => p_ledger_id
3074 , p_legal_entity_ID => p_legal_entity_ID
3075 );
3076 END IF;--l_row_count=0
3077
3078 FOR rec_line IN c_journal_lines(l_flex_value_set_id,rec_period.period_name)
3079 LOOP
3080
3081 IF l_earliest_changed_year > rec_period.period_year
3082 THEN
3083 l_earliest_changed_year:=rec_period.period_year;
3084 l_earliest_changed_period:=rec_period.period_name;
3085 END IF;
3086
3087 l_balance_rec.ledger_id := p_ledger_id ;
3088 l_balance_rec.LEGAL_ENTITY_ID := p_legal_entity_ID ;
3089 l_balance_rec.COMPANY_SEGMENT := rec_line.company_segment ;
3090 l_balance_rec.PERIOD_NAME := rec_line.period_name ;
3091 l_balance_rec.CURRENCY_CODE := rec_line.currency_code ;
3092 --l_balance_rec.COST_CENTER := rec_line.COST_CENTER ;
3093 l_balance_rec.THIRD_PARTY_TYPE := rec_line.third_party_type ;
3094 l_balance_rec.THIRD_PARTY_ID := rec_line.third_party_id ; --Updated for posting journals by customer or supplier
3095 /* l_balance_rec.THIRD_PARTY_NUMBER := rec_line.third_party_number;
3096 l_balance_rec.PROJECT_ID := rec_line.project_id ;
3097 l_balance_rec.PROJECT_NUMBER := rec_line.project_number ;
3098 l_balance_rec.PROJECT_SOURCE := rec_line.project_source ;*/
3099 l_balance_rec.ACCOUNT_SEGMENT := rec_line.account_segment ;
3100 l_balance_rec.account_type := rec_line.account_type ;
3101 /*l_balance_rec.PERSONNEL_ID := rec_line.personnel_id ;
3102 l_balance_rec.PERSONNEL_NUMBER := rec_line.personnel_number ;*/
3103 l_balance_rec.FUNC_PERIOD_NET_DR := rec_line.accounted_dr ;
3104 l_balance_rec.FUNC_PERIOD_NET_CR := rec_line.accounted_cr ;
3105 l_balance_rec.ORIG_PERIOD_NET_DR := rec_line.entered_dr ;
3106 l_balance_rec.ORIG_PERIOD_NET_CR := rec_line.entered_cr ;
3107 l_balance_rec.PERIOD_MON := rec_period.period_num ;
3108 -- Add for CNAO V2
3109 l_balance_rec.SEGMENT1 := rec_line.SEGMENT1 ;
3110 l_balance_rec.SEGMENT2 := rec_line.SEGMENT2 ;
3111 l_balance_rec.SEGMENT3 := rec_line.SEGMENT3 ;
3112 l_balance_rec.SEGMENT4 := rec_line.SEGMENT4 ;
3113 l_balance_rec.SEGMENT5 := rec_line.SEGMENT5 ;
3114 l_balance_rec.SEGMENT6 := rec_line.SEGMENT6 ;
3115 l_balance_rec.SEGMENT7 := rec_line.SEGMENT7 ;
3116 l_balance_rec.SEGMENT8 := rec_line.SEGMENT8 ;
3117 l_balance_rec.SEGMENT9 := rec_line.SEGMENT9 ;
3118 l_balance_rec.SEGMENT10 := rec_line.SEGMENT10 ;
3119 l_balance_rec.SEGMENT11 := rec_line.SEGMENT11 ;
3120 l_balance_rec.SEGMENT12 := rec_line.SEGMENT12 ;
3121 l_balance_rec.SEGMENT13 := rec_line.SEGMENT13 ;
3122 l_balance_rec.SEGMENT14 := rec_line.SEGMENT14 ;
3123 l_balance_rec.SEGMENT15 := rec_line.SEGMENT15 ;
3124 l_balance_rec.SEGMENT16 := rec_line.SEGMENT16 ;
3125 l_balance_rec.SEGMENT17 := rec_line.SEGMENT17 ;
3126 l_balance_rec.SEGMENT18 := rec_line.SEGMENT18 ;
3127 l_balance_rec.SEGMENT19 := rec_line.SEGMENT19 ;
3128 l_balance_rec.SEGMENT20 := rec_line.SEGMENT20 ;
3129 l_balance_rec.SEGMENT21 := rec_line.SEGMENT21 ;
3130 l_balance_rec.SEGMENT22 := rec_line.SEGMENT22 ;
3131 l_balance_rec.SEGMENT23 := rec_line.SEGMENT23 ;
3132 l_balance_rec.SEGMENT24 := rec_line.SEGMENT24 ;
3133 l_balance_rec.SEGMENT25 := rec_line.SEGMENT25 ;
3134 l_balance_rec.SEGMENT26 := rec_line.SEGMENT26 ;
3135 l_balance_rec.SEGMENT27 := rec_line.SEGMENT27 ;
3136 l_balance_rec.SEGMENT28 := rec_line.SEGMENT28 ;
3137 l_balance_rec.SEGMENT29 := rec_line.SEGMENT29 ;
3138 l_balance_rec.SEGMENT30 := rec_line.SEGMENT30 ;
3139
3140 -- the account is retained account
3141 IF rec_line.account_type IN ('A','L','O')
3142 THEN
3143 update_balance( p_balance_rec => l_balance_rec
3144 , p_current_period_flag => 'Y'
3145 );
3146
3147 IF( G_STATEMENT_LEVEL >= g_debug_devel )
3148 THEN
3149 put_log( G_MODULE_PREFIX||l_procedure_name||'.after period'
3150 , l_period_year*1000+l_period_num);
3151 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
3152
3153 FOR rec_after_period IN
3154 c_after_periods(rec_period.period_year*1000+
3155 rec_period.period_num)
3156 LOOP
3157 l_balance_rec.PERIOD_NAME := rec_after_period.period_name ;
3158 l_balance_rec.PERIOD_MON := rec_after_period.period_month;
3159 update_balance( p_balance_rec => l_balance_rec
3160 , p_current_period_flag => 'N'
3161 );
3162
3163 END LOOP;
3164
3165 ELSE --the account is not-retained earning account
3166 IF( G_STATEMENT_LEVEL >= g_debug_devel )
3167 THEN
3168 put_log( G_MODULE_PREFIX||l_procedure_name||'.after period in the same year'
3169 , l_period_year*1000+l_period_num);
3170 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
3171
3172 update_balance( p_balance_rec => l_balance_rec
3173 , p_current_period_flag => 'Y'
3174 );
3175
3176 FOR rec_after_period_in_year IN
3177 c_after_periods_in_year(
3178 rec_period.period_year*1000+rec_period.period_num
3179 ,rec_period.period_year)
3180 LOOP
3181 l_balance_rec.PERIOD_NAME := rec_after_period_in_year.period_name;
3182 l_balance_rec.PERIOD_MON := rec_after_period_in_year.period_month;
3183 update_balance( p_balance_rec => l_balance_rec
3184 , p_current_period_flag => 'N'
3185 );
3186
3187 END LOOP;
3188 END IF;--rec_line.account_type IN ('R','E')
3189
3190 END LOOP;
3191
3192 --update the journal status
3193 UPDATE ja_cn_journal_lines jl
3194 SET jl.status='P'
3195 WHERE jl.ledger_id = p_ledger_id
3196 AND jl.LEGAL_ENTITY_ID = p_legal_entity_ID
3197 AND jl.PERIOD_NAME = rec_period.period_name
3198 AND jl.status = 'U';
3199
3200 COMMIT;
3201 -- AND jl.PERSONNEL_NUMBER = rec_line.personnel_number
3202 END LOOP;
3203
3204 update_retained_parent_account
3205 ( p_period_name => l_earliest_changed_period
3206 , p_ledger_id => p_ledger_id
3207 , p_legal_entity_ID => p_legal_entity_ID
3208 );
3209
3210 IF( G_PROC_LEVEL >= g_debug_devel )
3211 THEN
3212 FND_LOG.STRING(G_PROC_LEVEL
3213 ,G_MODULE_PREFIX||l_procedure_name||'.end'
3214 ,'End procedure');
3215 END IF; --( G_PROC_LEVEL >= g_debug_devel)
3216 EXCEPTION
3217 WHEN OTHERS THEN
3218 IF(FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel)
3219 THEN
3220 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
3221 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
3222 , SQLCODE||SQLERRM);
3223 END IF;
3224 RAISE;
3225 END post_journal_itemized;
3226
3227 BEGIN
3228 g_debug_devel:=fnd_log.G_CURRENT_RUNTIME_LEVEL;
3229
3230 END JA_CN_POST_UTILITY_PKG;
3231