[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.1.12000000.1 2007/08/13 14:09:45 qzhao noship $
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 --+======================================================================*/
26
27 TYPE cnao_balance_rec IS RECORD
28 ( ledger_id ja_cn_account_balances.ledger_ID%TYPE
29 , LEGAL_ENTITY_ID ja_cn_account_balances.LEGAL_ENTITY_ID%TYPE
30 , COMPANY_SEGMENT ja_cn_account_balances.COMPANY_SEGMENT%TYPE
31 , PERIOD_NAME ja_cn_account_balances.PERIOD_NAME %TYPE
32 , CURRENCY_CODE ja_cn_account_balances.CURRENCY_CODE %TYPE
33 , COST_CENTER ja_cn_account_balances.COST_CENTER %TYPE
34 , THIRD_PARTY_TYPE ja_cn_account_balances.THIRD_PARTY_TYPE %TYPE
35 , THIRD_PARTY_ID ja_cn_account_balances.THIRD_PARTY_ID %TYPE
36 , THIRD_PARTY_NUMBER ja_cn_account_balances.THIRD_PARTY_NUMBER %TYPE
37 , PROJECT_ID ja_cn_account_balances.PROJECT_ID %TYPE
38 , PROJECT_NUMBER ja_cn_account_balances.PROJECT_NUMBER %TYPE
39 , PROJECT_SOURCE ja_cn_account_balances.PROJECT_SOURCE %TYPE
40 , ACCOUNT_SEGMENT ja_cn_account_balances.ACCOUNT_SEGMENT %TYPE
41 , ACCOUNT_type ja_cn_account_balances.ACCOUNT_type %TYPE
42 , PERSONNEL_ID ja_cn_account_balances.PERSONNEL_ID %TYPE
43 , PERSONNEL_NUMBER ja_cn_account_balances.PERSONNEL_NUMBER %TYPE
44 , FUNC_PERIOD_NET_DR ja_cn_account_balances.FUNC_PERIOD_NET_DR %TYPE
45 , FUNC_PERIOD_NET_CR ja_cn_account_balances.FUNC_PERIOD_NET_CR %TYPE
46 , ORIG_PERIOD_NET_DR ja_cn_account_balances.ORIG_PERIOD_NET_DR %TYPE
47 , ORIG_PERIOD_NET_CR ja_cn_account_balances.ORIG_PERIOD_NET_CR %TYPE
48 , PERIOD_MON ja_cn_account_balances.PERIOD_MON %TYPE
49 );
50
51 G_MODULE_PREFIX VARCHAR2(30):='JA_CN_POST_UTILITY_PKG.';
52 G_PROC_LEVEL INT :=fnd_log.LEVEL_PROCEDURE;
53 G_STATEMENT_LEVEL INT :=fnd_log.LEVEL_STATEMENT;
54 g_debug_devel INT;
55
56 --==========================================================================
57 -- PROCEDURE NAME:
58 -- Put_Line private
59 --
60 -- DESCRIPTION:
61 -- This procedure write data to concurrent output file.
62 --
63 -- PARAMETERS:
64 -- In: p_str VARCHAR2
65 --
66 -- DESIGN REFERENCES:
67 -- None
68 --
69 -- CHANGE HISTORY:
70 -- 02/21/2006 Jogen Hu Created
71 --===========================================================================
72 PROCEDURE put_line
73 ( p_str IN VARCHAR2
74 )
75 IS
76 BEGIN
77 FND_FILE.Put_Line(FND_FILE.Output,p_str);
78 END put_line;
79
80 --==========================================================================
81 -- PROCEDURE NAME:
82 -- Put_Line private
83 --
84 -- DESCRIPTION:
85 -- This procedure write data to log file.
86 --
87 -- PARAMETERS:
88 -- In: p_str VARCHAR2
89 --
90 -- DESIGN REFERENCES:
91 -- None
92 --
93 -- CHANGE HISTORY:
94 -- 02/21/2006 Jogen Hu Created
95 --===========================================================================
96 PROCEDURE put_log
97 ( p_module IN VARCHAR2
98 , p_message IN VARCHAR2
99 )
100 IS
101 BEGIN
102 --fnd_file.PUT_LINE(fnd_file.LOG,p_module||':'||p_message);
103 IF( fnd_log.LEVEL_STATEMENT >= g_debug_devel )
104 THEN
105 fnd_log.STRING( LOG_LEVEL => fnd_log.LEVEL_STATEMENT
106 , MODULE => p_module
107 , MESSAGE => p_message
108 );
109 END IF;
110 END put_log;
111
112 --==========================================================================
113 -- PROCEDURE NAME:
114 -- open_period private
115 --
116 -- DESCRIPTION:
117 -- This procedure is used to open a period which had never post
118 -- journal from "Itemized journal table" to "Itemized balance table".
119 -- if the period is the first period of the fiscal year, transfer the
120 -- income and expense account to retained earnings account
121 --
122 -- PARAMETERS:
123 -- In: p_period_name the period name needing to open
124 -- p_ledger_id Set of book ID
125 -- p_legal_entity_ID Legal entity id
126
127 --
128 -- DESIGN REFERENCES:
129 -- None
130 --
131 -- CHANGE HISTORY:
132 -- 02/21/2006 Jogen Hu Created
133 -- 04/28/2007 Qingjun Zhao Change SOB to Ledger for upgrade
134 -- from 11i to R12
135 --===========================================================================
136 PROCEDURE open_period
137 ( p_period_name IN VARCHAR2
138 , p_ledger_id IN NUMBER
139 , p_legal_entity_ID IN NUMBER
140 )
141 IS
142 l_procedure_name VARCHAR2(30):='open_period';
143 l_row_count NUMBER;
144 --l_period_set_name GL_PERIODS.PERIOD_SET_NAME%TYPE;
145 l_period_wrong EXCEPTION;
146 l_flex_value_set_id FND_FLEX_VALUE_SETS.Flex_Value_Set_Id%TYPE;
147 l_period_year gl_periods.period_year%TYPE;
148 l_period_num ja_cn_periods.period_num%TYPE;
149 l_first_fiscal_period_flag CHAR(1);
150 l_prior_period_name gl_periods.period_name%TYPE;
151 l_prior_period_num ja_cn_periods.period_num%TYPE;
152
153 l_period_month ja_cn_account_balances.period_mon%TYPE;
154 /*l_RET_EARN_CODE_COMBINATION_ID gl_ledgers.ret_earn_code_combination_id%TYPE;
155
156 l_ret_company_segment ja_cn_account_balances.company_segment%TYPE;
157 l_ret_cost_center_seg ja_cn_account_balances.cost_center%TYPE;
158 l_ret_account_segment ja_cn_account_balances.account_segment%TYPE;
159 l_ret_project_number ja_cn_account_balances.company_segment%TYPE;
160 l_ret_project_id ja_cn_account_balances.cost_center%TYPE;
161 l_ret_currence_code ja_cn_account_balances.currency_code%TYPE;
162
163 l_ret_acct_balance_dr gl_balances.begin_balance_dr%TYPE;
164 l_ret_acct_balance_cr gl_balances.begin_balance_cr%TYPE;
165 l_ret_acct_balance_dr_beq gl_balances.begin_balance_dr_beq%TYPE;
166 l_ret_acct_balance_cr_beq gl_balances.begin_balance_cr_beq%TYPE;
167
168 l_ret_account_type ja_cn_account_balances.account_type%TYPE;
169
170 l_cost_center_second_tracking BOOLEAN:=FALSE;
171
172 --get retained earning account segements: company, account, cost center
173 CURSOR c_retain_account1(pc_RET_EARN_CODE_COMBIN_ID IN NUMBER
174 ,pc_flex_value_set_id IN NUMBER) IS
175 SELECT jcc.company_segment
176 , jcc.account_segment
177 , jcc.cost_segment
178 , jcc.project_number
179 , jcc.project_id
180 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1) account_type
181 FROM ja_cn_code_combination_v jcc
182 , FND_FLEX_VALUES ffv
183 WHERE jcc.ledger_id = p_ledger_id
184 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
185 AND ffv.FLEX_VALUE_SET_ID = pc_flex_value_set_id
186 AND ffv.flex_value = jcc.account_segment
187 AND (jcc.company_segment, jcc.account_segment, jcc.cost_segment) IN
188 (SELECT jcc1.company_segment
189 , jcc1.account_segment
190 , jcc1.cost_segment
191 FROM ja_cn_code_combination_v jcc1
192 WHERE jcc1.CODE_COMBINATION_ID = pc_RET_EARN_CODE_COMBIN_ID
193 AND jcc1.ledger_id = p_ledger_id);
194
195 --get retained earning account segements: company, account, cost center
196 CURSOR c_retain_account2(pc_RET_EARN_CODE_COMBIN_ID IN NUMBER
197 ,pc_flex_value_set_id IN NUMBER) IS
198 SELECT jcc.company_segment
199 , jcc.account_segment
200 , jcc.cost_segment
201 , jcc.project_number
202 , jcc.project_id
203 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1) account_type
204 FROM ja_cn_code_combination_v jcc
205 , FND_FLEX_VALUES ffv
206 WHERE jcc.ledger_id = p_ledger_id
207 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
208 AND ffv.FLEX_VALUE_SET_ID = pc_flex_value_set_id
209 AND ffv.flex_value = jcc.account_segment
210 AND (jcc.company_segment, jcc.account_segment) IN
211 (SELECT jcc1.company_segment
212 , jcc1.account_segment
213 FROM ja_cn_code_combination_v jcc1
214 WHERE jcc1.CODE_COMBINATION_ID = pc_RET_EARN_CODE_COMBIN_ID
215 AND jcc1.ledger_id = p_ledger_id);*/
216
217 BEGIN
218
219 IF( G_PROC_LEVEL >= g_debug_devel )
220 THEN
221 FND_LOG.STRING(G_PROC_LEVEL
222 ,G_MODULE_PREFIX||l_procedure_name||'.begin'
223 ,'Enter procedure');
224 END IF; --( G_PROC_LEVEL >= g_debug_devel )
225
226 put_log('open_period parameter',p_period_name);
227
228 SELECT ifs.flex_value_set_id
229 INTO l_flex_value_set_id
230 FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
231 , gl_ledgers led
232 , Fnd_Id_Flex_Segments ifs
233 WHERE sav.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
234 AND sav.ID_FLEX_CODE = 'GL#'
235 AND sav.APPLICATION_ID = 101
236 AND sav.attribute_value = 'Y'
237 AND sav.ID_FLEX_NUM = led.chart_of_accounts_id
238 AND ifs.application_id = 101
239 AND ifs.Id_Flex_Code = 'GL#'
240 AND ifs.id_flex_num = led.chart_of_accounts_id
241 AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
242 AND led.ledger_id = p_ledger_id;
243
244 IF( G_STATEMENT_LEVEL >= g_debug_devel )
245 THEN
246 put_log( G_MODULE_PREFIX||l_procedure_name||'.flex_value_set_id'
247 , l_flex_value_set_id);
248 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel )
249
250 SELECT COUNT(*)
251 INTO l_row_count
252 FROM JA_CN_PERIODS
253 WHERE period_name = p_period_name
254 AND ledger_id=p_ledger_id;
255
256 IF l_row_count = 0 --The period is not processed before
257 THEN
258 --open the period
259 INSERT INTO JA_CN_PERIODS
260 ( ledger_id
261 , START_DATE
262 , END_DATE
263 , PERIOD_NAME
264 , PERIOD_NUM
265 , PERIOD_YEAR
266 , FIRST_FISCAL_PERIOD_FLAG
267 , STATUS
268 , CREATION_DATE
269 , CREATED_BY
270 , LAST_UPDATE_DATE
271 , LAST_UPDATED_BY
272 , LAST_UPDATE_LOGIN
273 )
274 SELECT p_ledger_id
275 , gp.start_date
276 , gp.end_date
277 , p_period_name
278 , gp.period_num+gp.period_year*1000
279 , gp.period_year
280 , decode(gp.period_num,1,'Y','N')
281 , 'O'
282 , SYSDATE
283 , fnd_global.USER_ID
284 , SYSDATE
285 , fnd_global.USER_ID
286 , fnd_global.LOGIN_ID
287 FROM gl_ledgers led
288 , gl_periods gp
289 WHERE led.ledger_id=p_ledger_id
290 AND led.period_set_name = gp.period_set_name
291 AND gp.period_name = p_period_name;
292
293 IF SQL%ROWCOUNT = 0 --the given set of book ID or period name error
294 THEN
295 RAISE l_period_wrong;
296 END IF;
297
298 --get the prior period
299 SELECT gp.period_year
300 , gp.period_num
301 , decode(gp.period_num,1,'Y','N')
302 INTO l_period_year
303 , l_period_num
304 , l_first_fiscal_period_flag
305 FROM gl_ledgers led
306 , gl_periods gp
307 WHERE led.ledger_id=p_ledger_id
308 AND led.period_set_name = gp.period_set_name
309 AND gp.period_name = p_period_name;
310
311 l_period_month := l_period_num;
312
313 IF( G_STATEMENT_LEVEL >= g_debug_devel )
314 THEN
315 put_log( G_MODULE_PREFIX||l_procedure_name||'.first_fiscal_period_flag'
316 , l_first_fiscal_period_flag);
317 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel )
318
319 SELECT MAX(period_num)
320 INTO l_prior_period_num
321 FROM JA_CN_PERIODS jjb
322 WHERE jjb.ledger_id=p_ledger_id
323 AND jjb.period_num <l_period_year*1000+l_period_num;
324
325 IF( G_STATEMENT_LEVEL >= g_debug_devel )
326 THEN
327 put_log( G_MODULE_PREFIX||l_procedure_name||'.prior_period_num'
328 , l_prior_period_num);
329 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel )
330
331 --first time to open period, needn't transfer prior periond end balance
332 IF l_prior_period_num IS NOT NULL
333 THEN
334 SELECT period_name
335 INTO l_prior_period_name
336 FROM JA_CN_PERIODS jjb
337 WHERE jjb.ledger_id=p_ledger_id
338 AND jjb.period_num =l_prior_period_num;
339
340 --transfer prior period end balance the period begin balance
341 INSERT INTO ja_cn_account_balances(
342 ledger_id
343 , legal_entity_id
344 , company_segment
345 , period_name
346 , currency_code
347 , cost_center
348 , third_party_type
349 , third_party_id
350 , third_party_number
351 , project_id
352 , project_number
353 , project_source
354 , account_segment
355 , account_type
356 , personnel_id
357 , personnel_number
358 , func_begin_balance_dr
359 , func_begin_balance_cr
360 , orig_begin_balance_dr
361 , Orig_Begin_Balance_Cr
362 , func_period_net_dr
363 , func_period_net_cr
364 , Orig_Period_Net_Dr
365 , Orig_Period_Net_Cr
366 , period_mon
367 , created_by
368 , creation_date
369 , last_updated_by
370 , last_update_date
371 , last_update_login
372 )
373 SELECT ledger_id
374 , legal_entity_id
375 , company_segment
376 , p_period_name
377 , currency_code
378 , cost_center
379 , third_party_type
380 , third_party_id
381 , third_party_number
382 , project_id
383 , project_number
384 , project_source
385 , account_segment
386 , account_type
387 , personnel_id
388 , personnel_number
389 , func_end_Balance_dr
390 , func_end_balance_cr
391 , orig_end_balance_dr
392 , orig_end_balance_cr
393 , 0
394 , 0
395 , 0
396 , 0
397 , l_period_month
398 , fnd_global.USER_ID
399 , SYSDATE
400 , fnd_global.USER_ID
401 , SYSDATE
402 , fnd_global.LOGIN_ID
403 FROM ja_cn_account_balances_v a
404 WHERE a.ledger_id = p_ledger_id
405 AND a.legal_entity_id = p_legal_entity_ID
406 AND a.period_name = l_prior_period_name;
407
408 IF l_first_fiscal_period_flag='Y'
409 --transfer the income and expense account to retained earnings account
410 THEN
411 --set the non-retained earning account balance to zero
412 UPDATE ja_cn_account_balances jab
413 SET func_begin_balance_dr = 0
414 , func_begin_balance_cr = 0
415 , orig_begin_balance_dr = 0
416 , Orig_Begin_Balance_Cr = 0
417 WHERE ledger_id = p_ledger_id
418 AND legal_entity_id = p_legal_entity_ID
419 AND period_name = p_period_name
420 AND EXISTS(SELECT *
421 FROM FND_FLEX_VALUES ffv
422 WHERE PARENT_FLEX_VALUE_LOW IS NULL
423 AND FLEX_VALUE_SET_ID = l_flex_value_set_id
424 AND ffv.flex_value = jab.account_segment
425 AND substr(COMPILED_VALUE_ATTRIBUTES,5,1) IN ('R','E')
426 );
427 /*
428 --get retain ccid and base currency code
429 SELECT sob.RET_EARN_CODE_COMBINATION_ID
430 , sob.currency_code
431 INTO l_RET_EARN_CODE_COMBINATION_ID
432 , l_ret_currence_code
433 FROM gl_ledgers sob
434 WHERE sob.ledger_id = p_ledger_id;
435
436 SELECT COUNT(*)
437 INTO l_row_count
438 FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
439 , gl_ledgers sob
440 , Fnd_Id_Flex_Segments ifs
441 WHERE sav.ID_FLEX_CODE = 'GL#'
442 AND sav.APPLICATION_ID = 101
443 AND sav.attribute_value = 'Y'
444 AND sav.ID_FLEX_NUM = sob.chart_of_accounts_id
445 AND ifs.application_id = 101
446 AND ifs.Id_Flex_Code = 'GL#'
447 AND ifs.id_flex_num = sob.chart_of_accounts_id
448 AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
449 AND sob.ledger_id = p_ledger_id
450 AND sav.SEGMENT_ATTRIBUTE_TYPE IN ('FA_COST_CTR','GL_SECONDARY_TRACKING');
451
452 IF l_row_count>=2
453 THEN
454 l_cost_center_second_tracking := TRUE;
455 END IF;
456
457 --get retained earning account segements: company, account, cost center
458 SELECT jcc.company_segment
459 , jcc.account_segment
460 , jcc.cost_segment
461 , jcc.project_number
462 , jcc.project_id
463 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
464 INTO l_ret_company_segment
465 , l_ret_account_segment
466 , l_ret_cost_center_seg
467 , l_ret_project_number
468 , l_ret_project_id
469 , l_ret_account_type
470 FROM ja_cn_code_combination_v jcc
471 , FND_FLEX_VALUES ffv
472 WHERE jcc.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
473 AND jcc.ledger_id = p_ledger_id
474 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
475 AND ffv.FLEX_VALUE_SET_ID = l_flex_value_set_id
476 AND ffv.flex_value = jcc.account_segment;
477
478 IF l_cost_center_second_tracking = TRUE
479 THEN
480 FOR rec_retain_accout1 IN c_retain_account1
481 LOOP
482 /*BEGIN
483 --get retain ccid and its balance
484 SELECT sob.RET_EARN_CODE_COMBINATION_ID
485 , gb.begin_balance_dr
486 , gb.begin_balance_cr
487 , gb.begin_balance_dr_beq
488 , gb.begin_balance_cr_beq
489 , gb.currency_code
490 INTO l_RET_EARN_CODE_COMBINATION_ID
491 , l_ret_acct_balance_dr
492 , l_ret_acct_balance_cr
493 , l_ret_acct_balance_dr_beq
494 , l_ret_acct_balance_cr_beq
495 , l_ret_currence_code
496 FROM gl_ledgers sob
497 , gl_balances gb
498 WHERE sob.ledger_id = p_ledger_id
499 AND sob.ret_earn_code_combination_id = gb.code_combination_id
500 AND gb.ledger_id = p_ledger_id
501 AND gb.period_name = p_period_name
502 AND gb.actual_flag = 'A'
503 AND gb.currency_code = sob.currency_code;
504
505 --get retained earning account segements: company, account, cost center
506 SELECT jcc.company_segment
507 , jcc.account_segment
508 , jcc.cost_segment
509 , jcc.project_number
510 , jcc.project_id
511 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
512 INTO l_ret_company_segment
513 , l_ret_account_segment
514 , l_ret_cost_center_seg
515 , l_ret_project_number
516 , l_ret_project_id
517 , l_ret_account_type
518 FROM ja_cn_code_combination_v jcc
519 , FND_FLEX_VALUES ffv
520 WHERE jcc.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
521 AND jcc.ledger_id = p_ledger_id
522 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
523 AND ffv.FLEX_VALUE_SET_ID = l_flex_value_set_id
524 AND ffv.flex_value = jcc.account_segment;
525
526 --change the retained earning account balance
527 UPDATE ja_cn_account_balances
528 SET func_begin_balance_dr = l_ret_acct_balance_dr_beq
529 , func_begin_balance_cr = l_ret_acct_balance_cr_beq
530 , orig_begin_balance_dr = l_ret_acct_balance_dr
531 , Orig_Begin_Balance_Cr = l_ret_acct_balance_cr
532 , last_updated_by = fnd_global.USER_ID
533 , last_update_date = SYSDATE
534 , last_update_login = fnd_global.LOGIN_ID
535 WHERE ledger_id = p_ledger_id
536 AND legal_entity_id = p_legal_entity_ID
537 AND company_segment = l_ret_company_segment
538 AND period_name = p_period_name
539 AND currency_code = l_ret_currence_code
540 AND cost_center = l_ret_cost_center_seg
541 -- AND project_id = l_ret_project_id
542 AND nvl(project_number,'0')= nvl(l_ret_project_number,'0')
543 AND account_segment = l_ret_account_segment;
544
545 --first period after EBS run
546 IF SQL%ROWCOUNT = 0
547 THEN
548 INSERT INTO ja_cn_account_balances(
549 ledger_id
550 , LEGAL_ENTITY_ID
551 , COMPANY_SEGMENT
552 , PERIOD_NAME
553 , CURRENCY_CODE
554 , COST_CENTER
555 , THIRD_PARTY_TYPE
556 , THIRD_PARTY_ID
557 , THIRD_PARTY_NUMBER
558 , PROJECT_ID
559 , PROJECT_NUMBER
560 , PROJECT_SOURCE
561 , ACCOUNT_SEGMENT
562 , account_type
563 , PERSONNEL_ID
564 , PERSONNEL_NUMBER
565 , FUNC_BEGIN_BALANCE_DR
566 , FUNC_BEGIN_BALANCE_CR
567 , ORIG_BEGIN_BALANCE_DR
568 , ORIG_BEGIN_BALANCE_CR
569 , FUNC_PERIOD_NET_DR
570 , FUNC_PERIOD_NET_CR
571 , ORIG_PERIOD_NET_DR
572 , ORIG_PERIOD_NET_CR
573 , PERIOD_MON
574 , CREATED_BY
575 , CREATION_DATE
576 , LAST_UPDATED_BY
577 , LAST_UPDATE_DATE
578 , LAST_UPDATE_LOGIN )
579 VALUES( p_ledger_id
580 , p_legal_entity_ID
581 , l_ret_company_segment
582 , p_period_name
583 , l_ret_currence_code
584 , l_ret_cost_center_seg
585 , NULL
586 , NULL
587 , NULL
588 , l_ret_project_id
589 , l_ret_project_number
590 , 'COA'
591 , l_ret_account_segment
592 , l_ret_account_type
593 , NULL
594 , NULL
595 , l_ret_acct_balance_dr_beq
596 , l_ret_acct_balance_cr_beq
597 , l_ret_acct_balance_dr
598 , l_ret_acct_balance_cr
599 , 0
600 , 0
601 , 0
602 , 0
603 , l_period_month
604 , fnd_global.USER_ID
605 , SYSDATE
606 , fnd_global.USER_ID
607 , SYSDATE
608 , fnd_global.LOGIN_ID);
609 END IF;
610 EXCEPTION --get retain ccid and its balance
611 WHEN NO_DATA_FOUND THEN
612 IF( G_PROC_LEVEL >= g_debug_devel )
613 THEN
614 put_log( G_MODULE_PREFIX||l_procedure_name||'.end'
615 ,'The retain earning account is not startup in period '
616 ||p_period_name);
617 END IF; --( G_PROC_LEVEL >= g_debug_devel)
618
619 END; --get retain ccid and its balance
620 END LOOP;
621 END IF;--l_cost_center_second_tracking = TRUE */
622 END IF;--l_first_fiscal_period_flag='Y'
623 END IF; --l_prior_period_num IS NULL
624 END IF; --l_row_count = 0
625
626 IF( G_PROC_LEVEL >= g_debug_devel )
627 THEN
628 FND_LOG.STRING(G_PROC_LEVEL
629 ,G_MODULE_PREFIX||l_procedure_name||'.end'
630 ,'End procedure');
631 END IF; --( G_PROC_LEVEL >= g_debug_devel)
632 EXCEPTION
633 WHEN l_period_wrong THEN
634 IF(FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel)
635 THEN
636 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
637 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
638 , 'The input period is invalid.');
639 END IF;
640 RAISE;
641
642 WHEN OTHERS THEN
643 IF(FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel)
644 THEN
645 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
646 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
647 , SQLCODE||SQLERRM);
648 END IF;
649 RAISE;
650
651 END open_period;
652
653 --==========================================================================
654 -- PROCEDURE NAME:
655 -- update_balance Private
656 --
657 -- DESCRIPTION:
658 -- Update CNAO balance table
659 --
660 -- PARAMETERS:
661 -- In: p_balance_rec cnao_balance_rec
662 -- p_current_period_flag whether update period is current period
663 --
664 -- DESIGN REFERENCES:
665 -- None
666 --
667 -- CHANGE HISTORY:
668 -- 05/24/2006 Jogen Hu Created
669 -- 04/28/2007 Qingjun Zhao Change SOB to Ledger for upgrade
670 -- from 11i to R12
671 --===========================================================================
672 PROCEDURE update_balance
673 ( p_balance_rec IN cnao_balance_rec
674 , p_current_period_flag IN VARCHAR2
675 )
676 IS
677 l_procedure_name VARCHAR2(20):='update_balance';
678 BEGIN
679 IF( G_PROC_LEVEL >= g_debug_devel )
680 THEN
681 FND_LOG.STRING(G_PROC_LEVEL
682 ,G_MODULE_PREFIX||l_procedure_name||'.begin'
683 ,'Begin procedure');
684 END IF; --( G_PROC_LEVEL >= g_debug_devel)
685
686 IF( G_STATEMENT_LEVEL >= g_debug_devel )
687 THEN
688 put_log( G_MODULE_PREFIX||l_procedure_name||'.update_balance'
689 , p_balance_rec.PERIOD_NAME||':'||p_balance_rec.PERIOD_MON);
690 END IF;
691
692 --if the period in parameter is the journal's period
693 IF p_current_period_flag='Y'
694 THEN
695 UPDATE ja_cn_account_balances
696 SET FUNC_PERIOD_NET_DR = FUNC_PERIOD_NET_DR + p_balance_rec.FUNC_PERIOD_NET_DR
697 , FUNC_PERIOD_NET_CR = FUNC_PERIOD_NET_CR + p_balance_rec.FUNC_PERIOD_NET_CR
698 , ORIG_PERIOD_NET_DR = ORIG_PERIOD_NET_DR + p_balance_rec.ORIG_PERIOD_NET_DR
699 , ORIG_PERIOD_NET_CR = ORIG_PERIOD_NET_CR + p_balance_rec.ORIG_PERIOD_NET_CR
700 , LAST_UPDATED_BY = fnd_global.USER_ID
701 , LAST_UPDATE_DATE = SYSDATE
702 , LAST_UPDATE_LOGIN = fnd_global.login_id
703 WHERE ledger_id = p_balance_rec.ledger_id
704 AND LEGAL_ENTITY_ID = p_balance_rec.LEGAL_ENTITY_ID
705 AND COMPANY_SEGMENT = p_balance_rec.COMPANY_SEGMENT
706 AND PERIOD_NAME = p_balance_rec.PERIOD_NAME
707 AND CURRENCY_CODE = p_balance_rec.CURRENCY_CODE
708 AND nvl(COST_CENTER,0) = nvl(p_balance_rec.COST_CENTER,0)
709 AND ACCOUNT_SEGMENT = p_balance_rec.ACCOUNT_SEGMENT
710 AND nvl(THIRD_PARTY_TYPE ,0) = nvl(p_balance_rec.THIRD_PARTY_TYPE ,0)
711 AND nvl(THIRD_PARTY_ID ,0) = nvl(p_balance_rec.THIRD_PARTY_ID ,0)
712 AND nvl(THIRD_PARTY_NUMBER,0) = nvl(p_balance_rec.THIRD_PARTY_NUMBER,0)
713 -- AND nvl(PROJECT_ID ,0) = nvl(p_balance_rec.PROJECT_ID ,0)
714 AND nvl(PROJECT_NUMBER ,0) = nvl(p_balance_rec.PROJECT_NUMBER ,0)
715 AND nvl(PROJECT_SOURCE ,0) = nvl(p_balance_rec.PROJECT_SOURCE ,0)
716 AND nvl(PERSONNEL_ID ,0) = nvl(p_balance_rec.PERSONNEL_ID ,0)
717 AND nvl(PERSONNEL_NUMBER ,0) = nvl(p_balance_rec.PERSONNEL_NUMBER ,0)
718 AND nvl(PERIOD_MON ,0) = nvl(p_balance_rec.PERIOD_MON ,0);
719
720 --first time balance
721 IF SQL%ROWCOUNT = 0
722 THEN
723
724 IF( G_STATEMENT_LEVEL >= g_debug_devel )
725 THEN
726
727 put_log( G_MODULE_PREFIX||l_procedure_name||'.update_balance'
728 , p_balance_rec.ledger_id
729 ||':'|| p_balance_rec.LEGAL_ENTITY_ID
730 ||':'|| p_balance_rec.COMPANY_SEGMENT
731 ||':'|| p_balance_rec.PERIOD_NAME
732 ||':'|| p_balance_rec.CURRENCY_CODE
733 ||':'|| p_balance_rec.COST_CENTER
734 ||':'|| p_balance_rec.THIRD_PARTY_TYPE
735 ||':'|| p_balance_rec.THIRD_PARTY_ID
736 ||':'|| p_balance_rec.THIRD_PARTY_NUMBER
737 ||':'|| p_balance_rec.PROJECT_ID
738 ||':'|| p_balance_rec.PROJECT_NUMBER
739 ||':'|| p_balance_rec.PROJECT_SOURCE
740 ||':'|| p_balance_rec.ACCOUNT_SEGMENT
741 ||':'|| p_balance_rec.ACCOUNT_type
742 ||':'|| p_balance_rec.PERSONNEL_ID
743 ||':'|| p_balance_rec.PERSONNEL_NUMBER);
744 END IF;
745
746 INSERT INTO ja_cn_account_balances(
747 ledger_id
748 , LEGAL_ENTITY_ID
749 , COMPANY_SEGMENT
750 , PERIOD_NAME
751 , CURRENCY_CODE
752 , COST_CENTER
753 , THIRD_PARTY_TYPE
754 , THIRD_PARTY_ID
755 , THIRD_PARTY_NUMBER
756 , PROJECT_ID
757 , PROJECT_NUMBER
758 , PROJECT_SOURCE
759 , ACCOUNT_SEGMENT
760 , account_type
761 , PERSONNEL_ID
762 , PERSONNEL_NUMBER
763 , FUNC_BEGIN_BALANCE_DR
764 , FUNC_BEGIN_BALANCE_CR
765 , ORIG_BEGIN_BALANCE_DR
766 , ORIG_BEGIN_BALANCE_CR
767 , FUNC_PERIOD_NET_DR
768 , FUNC_PERIOD_NET_CR
769 , ORIG_PERIOD_NET_DR
770 , ORIG_PERIOD_NET_CR
771 , PERIOD_MON
772 , CREATED_BY
773 , CREATION_DATE
774 , LAST_UPDATED_BY
775 , LAST_UPDATE_DATE
776 , LAST_UPDATE_LOGIN
777 )
778 VALUES
779 ( p_balance_rec.ledger_id
780 , p_balance_rec.LEGAL_ENTITY_ID
781 , p_balance_rec.COMPANY_SEGMENT
782 , p_balance_rec.PERIOD_NAME
783 , p_balance_rec.CURRENCY_CODE
784 , p_balance_rec.COST_CENTER
785 , p_balance_rec.THIRD_PARTY_TYPE
786 , p_balance_rec.THIRD_PARTY_ID
787 , p_balance_rec.THIRD_PARTY_NUMBER
788 , p_balance_rec.PROJECT_ID
789 , p_balance_rec.PROJECT_NUMBER
790 , p_balance_rec.PROJECT_SOURCE
791 , p_balance_rec.ACCOUNT_SEGMENT
792 , p_balance_rec.ACCOUNT_type
793 , p_balance_rec.PERSONNEL_ID
794 , p_balance_rec.PERSONNEL_NUMBER
795 , 0
796 , 0
797 , 0
798 , 0
799 , p_balance_rec.FUNC_PERIOD_NET_DR
800 , p_balance_rec.FUNC_PERIOD_NET_CR
801 , p_balance_rec.ORIG_PERIOD_NET_DR
802 , p_balance_rec.ORIG_PERIOD_NET_CR
803 , p_balance_rec.PERIOD_MON
804 , fnd_global.USER_ID
805 , SYSDATE
806 , fnd_global.USER_ID
807 , SYSDATE
808 , fnd_global.login_id
809 );
810 END IF; --SQL%ROWCOUNT = 0
811 ELSE --p_current_period_flag='Y'
812 UPDATE ja_cn_account_balances
813 SET FUNC_begin_BALANCE_DR = FUNC_begin_BALANCE_DR + p_balance_rec.FUNC_PERIOD_NET_DR
814 , FUNC_begin_BALANCE_CR = FUNC_begin_BALANCE_cR + p_balance_rec.FUNC_PERIOD_NET_CR
815 , ORIG_begin_BALANCE_DR = FUNC_begin_BALANCE_DR + p_balance_rec.ORIG_PERIOD_NET_DR
816 , ORIG_begin_BALANCE_CR = FUNC_begin_BALANCE_cR + p_balance_rec.ORIG_PERIOD_NET_CR
817 , LAST_UPDATED_BY = fnd_global.USER_ID
818 , LAST_UPDATE_DATE = SYSDATE
819 , LAST_UPDATE_LOGIN = fnd_global.login_id
820 WHERE ledger_id = p_balance_rec.ledger_id
821 AND LEGAL_ENTITY_ID = p_balance_rec.LEGAL_ENTITY_ID
822 AND COMPANY_SEGMENT = p_balance_rec.COMPANY_SEGMENT
823 AND PERIOD_NAME = p_balance_rec.PERIOD_NAME
824 AND CURRENCY_CODE = p_balance_rec.CURRENCY_CODE
825 AND nvl(COST_CENTER,0) = nvl(p_balance_rec.COST_CENTER,0)
826 AND ACCOUNT_SEGMENT = p_balance_rec.ACCOUNT_SEGMENT
827 AND nvl(THIRD_PARTY_TYPE ,0) = nvl(p_balance_rec.THIRD_PARTY_TYPE ,0)
828 AND nvl(THIRD_PARTY_ID ,0) = nvl(p_balance_rec.THIRD_PARTY_ID ,0)
829 AND nvl(THIRD_PARTY_NUMBER,0) = nvl(p_balance_rec.THIRD_PARTY_NUMBER,0)
830 -- AND nvl(PROJECT_ID ,0) = nvl(p_balance_rec.PROJECT_ID ,0)
831 AND nvl(PROJECT_NUMBER ,0) = nvl(p_balance_rec.PROJECT_NUMBER ,0)
832 AND nvl(PROJECT_SOURCE ,0) = nvl(p_balance_rec.PROJECT_SOURCE ,0)
833 AND nvl(PERSONNEL_ID ,0) = nvl(p_balance_rec.PERSONNEL_ID ,0)
834 AND nvl(PERSONNEL_NUMBER ,0) = nvl(p_balance_rec.PERSONNEL_NUMBER ,0)
835 AND nvl(PERIOD_MON ,0) = nvl(p_balance_rec.PERIOD_MON ,0);
836
837 --first time balance
838 IF SQL%ROWCOUNT = 0
839 THEN
840
841 IF( G_STATEMENT_LEVEL >= g_debug_devel )
842 THEN
843 put_log( G_MODULE_PREFIX||l_procedure_name||'.update_balance'
844 , p_balance_rec.ledger_id
845 ||':'|| p_balance_rec.LEGAL_ENTITY_ID
846 ||':'|| p_balance_rec.COMPANY_SEGMENT
847 ||':'|| p_balance_rec.PERIOD_NAME
848 ||':'|| p_balance_rec.CURRENCY_CODE
849 ||':'|| p_balance_rec.COST_CENTER
850 ||':'|| p_balance_rec.THIRD_PARTY_TYPE
851 ||':'|| p_balance_rec.THIRD_PARTY_ID
852 ||':'|| p_balance_rec.THIRD_PARTY_NUMBER
853 ||':'|| p_balance_rec.PROJECT_ID
854 ||':'|| p_balance_rec.PROJECT_NUMBER
855 ||':'|| p_balance_rec.PROJECT_SOURCE
856 ||':'|| p_balance_rec.ACCOUNT_SEGMENT
857 ||':'|| p_balance_rec.ACCOUNT_type
858 ||':'|| p_balance_rec.PERSONNEL_ID
859 ||':'|| p_balance_rec.PERSONNEL_NUMBER);
860 END IF;
861
862 INSERT INTO ja_cn_account_balances(
863 ledger_id
864 , LEGAL_ENTITY_ID
865 , COMPANY_SEGMENT
866 , PERIOD_NAME
867 , CURRENCY_CODE
868 , COST_CENTER
869 , THIRD_PARTY_TYPE
870 , THIRD_PARTY_ID
871 , THIRD_PARTY_NUMBER
872 , PROJECT_ID
873 , PROJECT_NUMBER
874 , PROJECT_SOURCE
875 , ACCOUNT_SEGMENT
876 , account_type
877 , PERSONNEL_ID
878 , PERSONNEL_NUMBER
879 , FUNC_BEGIN_BALANCE_DR
880 , FUNC_BEGIN_BALANCE_CR
881 , ORIG_BEGIN_BALANCE_DR
882 , ORIG_BEGIN_BALANCE_CR
883 , FUNC_PERIOD_NET_DR
884 , FUNC_PERIOD_NET_CR
885 , ORIG_PERIOD_NET_DR
886 , ORIG_PERIOD_NET_CR
887 , PERIOD_MON
888 , CREATED_BY
889 , CREATION_DATE
890 , LAST_UPDATED_BY
891 , LAST_UPDATE_DATE
892 , LAST_UPDATE_LOGIN
893 )
894 VALUES
895 ( p_balance_rec.ledger_id
896 , p_balance_rec.LEGAL_ENTITY_ID
897 , p_balance_rec.COMPANY_SEGMENT
898 , p_balance_rec.PERIOD_NAME
899 , p_balance_rec.CURRENCY_CODE
900 , p_balance_rec.COST_CENTER
901 , p_balance_rec.THIRD_PARTY_TYPE
902 , p_balance_rec.THIRD_PARTY_ID
903 , p_balance_rec.THIRD_PARTY_NUMBER
904 , p_balance_rec.PROJECT_ID
905 , p_balance_rec.PROJECT_NUMBER
906 , p_balance_rec.PROJECT_SOURCE
907 , p_balance_rec.ACCOUNT_SEGMENT
908 , p_balance_rec.ACCOUNT_type
909 , p_balance_rec.PERSONNEL_ID
910 , p_balance_rec.PERSONNEL_NUMBER
911 , p_balance_rec.FUNC_PERIOD_NET_DR
912 , p_balance_rec.FUNC_PERIOD_NET_CR
913 , p_balance_rec.ORIG_PERIOD_NET_DR
914 , p_balance_rec.ORIG_PERIOD_NET_CR
915 , 0
916 , 0
917 , 0
918 , 0
919 , p_balance_rec.PERIOD_MON
920 , fnd_global.USER_ID
921 , SYSDATE
922 , fnd_global.USER_ID
923 , SYSDATE
924 , fnd_global.login_id
925 );
926 END IF; --SQL%ROWCOUNT = 0
927 END IF;--p_current_period_flag='Y'
928
929 IF( G_PROC_LEVEL >= g_debug_devel )
930 THEN
931 FND_LOG.STRING(G_PROC_LEVEL
932 ,G_MODULE_PREFIX||l_procedure_name||'.end'
933 ,'End procedure');
934 END IF; --( G_PROC_LEVEL >= g_debug_devel)
935 EXCEPTION
936 WHEN OTHERS THEN
937 IF(FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel)
938 THEN
939 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
940 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
941 , SQLCODE||SQLERRM);
942 END IF;
943 RAISE;
944 END update_balance;
945
946 --==========================================================================
947 -- PROCEDURE NAME:
948 -- update_retained_account private
949 --
950 -- DESCRIPTION:
951 -- This procedure update the retained earning account
952 --
953 -- PARAMETERS:
954 -- In: p_period_name the end period name after which
955 -- the CNAO journal should be processed
956 -- p_ledger_id Set of book ID
957 -- p_legal_entity_ID Legal entity id
958
959 --
960 -- DESIGN REFERENCES:
961 -- None
962 --
963 -- CHANGE HISTORY:
964 -- 02/21/2006 Jogen Hu Created
965 -- 04/28/2007 Qingjun Zhao Change SOB to Ledger for upgrade
966 -- from 11i to R12
967 --===========================================================================
968 PROCEDURE update_retained_parent_account
969 ( p_period_name IN VARCHAR2
970 , p_ledger_id IN NUMBER
971 , p_legal_entity_ID IN NUMBER
972 )
973 IS
974 l_procedure_name VARCHAR2(30):='update_retained_account';
975 l_period_year ja_cn_periods.period_year%TYPE;
976 --l_period_name ja_cn_periods.period_name%TYPE;
977
978 l_RET_EARN_CODE_COMBINATION_ID gl_ledgers.ret_earn_code_combination_id%TYPE;
979 l_ret_currence_code ja_cn_account_balances.currency_code%TYPE;
980 l_row_count NUMBER;
981 l_cost_center_second_tracking BOOLEAN:=FALSE;
982 l_period_num ja_cn_periods.period_num%TYPE;
983 l_last_period_num ja_cn_periods.period_num%TYPE;
984 l_flex_value_set_id FND_FLEX_VALUE_SETS.Flex_Value_Set_Id%TYPE;
985 i NUMBER;
986
987 l_number NUMBER;
988
989
990 BEGIN
991 IF( G_PROC_LEVEL >= g_debug_devel )
992 THEN
993 FND_LOG.STRING(G_PROC_LEVEL
994 ,G_MODULE_PREFIX||l_procedure_name||'.begin'
995 ,'Begin procedure');
996 END IF; --( G_PROC_LEVEL >= g_debug_devel)
997
998 --------------------------------------------------------
999 --get retain ccid and base currency code
1000 SELECT led.RET_EARN_CODE_COMBINATION_ID
1001 , led.currency_code
1002 INTO l_RET_EARN_CODE_COMBINATION_ID
1003 , l_ret_currence_code
1004 FROM gl_ledgers led
1005 WHERE led.ledger_id = p_ledger_id;
1006
1007 SELECT COUNT(*)
1008 INTO l_row_count
1009 FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
1010 , gl_ledgers led
1011 , Fnd_Id_Flex_Segments ifs
1012 WHERE sav.ID_FLEX_CODE = 'GL#'
1013 AND sav.APPLICATION_ID = 101
1014 AND sav.attribute_value = 'Y'
1015 AND sav.ID_FLEX_NUM = led.chart_of_accounts_id
1016 AND ifs.application_id = 101
1017 AND ifs.Id_Flex_Code = 'GL#'
1018 AND ifs.id_flex_num = led.chart_of_accounts_id
1019 AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
1020 AND led.ledger_id = p_ledger_id
1021 AND sav.SEGMENT_ATTRIBUTE_TYPE IN ('FA_COST_CTR','GL_SECONDARY_TRACKING');
1022
1023 IF l_row_count>=2
1024 THEN
1025 l_cost_center_second_tracking := TRUE;
1026 END IF;
1027
1028 SELECT period_year
1029 INTO l_period_year
1030 FROM ja_cn_periods
1031 WHERE period_name=p_period_name
1032 AND ledger_id=p_ledger_id;
1033
1034 l_period_num:=l_period_year*1000+1;
1035
1036 SELECT max(period_num)
1037 INTO l_last_period_num
1038 FROM ja_cn_periods
1039 WHERE ledger_id=p_ledger_id;
1040
1041 SELECT ifs.flex_value_set_id
1042 INTO l_flex_value_set_id
1043 FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
1044 , gl_ledgers led
1045 , Fnd_Id_Flex_Segments ifs
1046 WHERE sav.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
1047 AND sav.ID_FLEX_CODE = 'GL#'
1048 AND sav.APPLICATION_ID = 101
1049 AND sav.attribute_value = 'Y'
1050 AND sav.ID_FLEX_NUM = led.chart_of_accounts_id
1051 AND ifs.application_id = 101
1052 AND ifs.Id_Flex_Code = 'GL#'
1053 AND ifs.id_flex_num = led.chart_of_accounts_id
1054 AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
1055 AND led.ledger_id = p_ledger_id;
1056
1057 IF l_cost_center_second_tracking
1058 THEN
1059 --insert all possible retain account into temp table
1060 INSERT INTO ja_cn_account_balances_post_gt(
1061 ledger_id
1062 , LEGAL_ENTITY_ID
1063 , COMPANY_SEGMENT
1064 , PERIOD_NAME
1065 , CURRENCY_CODE
1066 , COST_CENTER
1067 , THIRD_PARTY_TYPE
1068 , THIRD_PARTY_ID
1069 , THIRD_PARTY_NUMBER
1070 , PROJECT_ID
1071 , PROJECT_NUMBER
1072 , PROJECT_SOURCE
1073 , ACCOUNT_SEGMENT
1074 , PERSONNEL_ID
1075 , PERSONNEL_NUMBER
1076 , FUNC_BEGIN_BALANCE_DR
1077 , FUNC_BEGIN_BALANCE_CR
1078 , ORIG_BEGIN_BALANCE_DR
1079 , ORIG_BEGIN_BALANCE_CR
1080 , FUNC_PERIOD_NET_DR
1081 , FUNC_PERIOD_NET_CR
1082 , ORIG_PERIOD_NET_DR
1083 , ORIG_PERIOD_NET_CR
1084 , PERIOD_MON
1085 , ACCOUNT_TYPE
1086 , period_num
1087 , CREATED_BY
1088 , CREATION_DATE
1089 , LAST_UPDATED_BY
1090 , LAST_UPDATE_DATE
1091 , LAST_UPDATE_LOGIN
1092 )
1093 SELECT DISTINCT p_ledger_id
1094 , p_legal_entity_ID
1095 , jcc.company_segment
1096 , jcp.period_name
1097 , l_ret_currence_code
1098 , jcc.cost_segment
1099 , NULL
1100 , NULL
1101 , NULL
1102 , jcc.project_id
1103 , jcc.project_number
1104 , 'COA'
1105 , jcc.account_segment
1106 , NULL
1107 , NULL
1108 , 0
1109 , 0
1110 , 0
1111 , 0
1112 , 0
1113 , 0
1114 , 0
1115 , 0
1116 , jcp.period_num - jcp.period_year*1000
1117 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
1118 , jcp.period_num
1119 , fnd_global.USER_ID
1120 , SYSDATE
1121 , fnd_global.USER_ID
1122 , SYSDATE
1123 , fnd_global.LOGIN_ID
1124 FROM ja_cn_code_combination_v jcc
1125 , FND_FLEX_VALUES ffv
1126 , ja_cn_periods jcp
1127 WHERE jcc.ledger_id = p_ledger_id
1128 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
1129 AND ffv.FLEX_VALUE_SET_ID = l_flex_value_set_id
1130 AND ffv.flex_value = jcc.account_segment
1131 AND jcp.ledger_id=p_ledger_id
1132 AND jcp.period_num BETWEEN l_period_num AND l_last_period_num
1133 AND EXISTS((SELECT *
1134 FROM ja_cn_code_combination_v jcc1
1135 WHERE jcc1.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
1136 AND jcc1.ledger_id = p_ledger_id
1137 AND jcc1.account_segment=jcc.account_segment
1138 AND nvl(jcc1.project_id,'0') =nvl(jcc.project_id ,'0')
1139 AND nvl(jcc1.project_number,'0') =nvl(jcc.project_number,'0') ) );
1140
1141 ELSE --l_cost_center_second_tracking = false
1142
1143 INSERT INTO ja_cn_account_balances_post_gt(
1144 ledger_id
1145 , LEGAL_ENTITY_ID
1146 , COMPANY_SEGMENT
1147 , PERIOD_NAME
1148 , CURRENCY_CODE
1149 , COST_CENTER
1150 , THIRD_PARTY_TYPE
1151 , THIRD_PARTY_ID
1152 , THIRD_PARTY_NUMBER
1153 , PROJECT_ID
1154 , PROJECT_NUMBER
1155 , PROJECT_SOURCE
1156 , ACCOUNT_SEGMENT
1157 , PERSONNEL_ID
1158 , PERSONNEL_NUMBER
1159 , FUNC_BEGIN_BALANCE_DR
1160 , FUNC_BEGIN_BALANCE_CR
1161 , ORIG_BEGIN_BALANCE_DR
1162 , ORIG_BEGIN_BALANCE_CR
1163 , FUNC_PERIOD_NET_DR
1164 , FUNC_PERIOD_NET_CR
1165 , ORIG_PERIOD_NET_DR
1166 , ORIG_PERIOD_NET_CR
1167 , PERIOD_MON
1168 , ACCOUNT_TYPE
1169 , period_num
1170 , CREATED_BY
1171 , CREATION_DATE
1172 , LAST_UPDATED_BY
1173 , LAST_UPDATE_DATE
1174 , LAST_UPDATE_LOGIN )
1175 SELECT DISTINCT p_ledger_id
1176 , p_legal_entity_ID
1177 , jcc.company_segment
1178 , jcp.period_name
1179 , l_ret_currence_code
1180 , jcc.cost_segment
1181 , NULL
1182 , NULL
1183 , NULL
1184 , jcc.project_id
1185 , jcc.project_number
1186 , 'COA'
1187 , jcc.account_segment
1188 , NULL
1189 , NULL
1190 , 0
1191 , 0
1192 , 0
1193 , 0
1194 , 0
1195 , 0
1196 , 0
1197 , 0
1198 , jcp.period_num - jcp.period_year*1000
1199 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
1200 , jcp.period_num
1201 , fnd_global.USER_ID
1202 , SYSDATE
1203 , fnd_global.USER_ID
1204 , SYSDATE
1205 , fnd_global.LOGIN_ID
1206 FROM ja_cn_code_combination_v jcc
1207 , FND_FLEX_VALUES ffv
1208 , ja_cn_periods jcp
1209 WHERE jcc.ledger_id = p_ledger_id
1210 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
1211 AND ffv.FLEX_VALUE_SET_ID = l_flex_value_set_id
1212 AND ffv.flex_value = jcc.account_segment
1213 AND jcp.ledger_id=p_ledger_id
1214 AND jcp.period_num BETWEEN l_period_num AND l_last_period_num
1215 AND EXISTS((SELECT *
1216 FROM ja_cn_code_combination_v jcc1
1217 WHERE jcc1.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
1218 AND jcc1.ledger_id = p_ledger_id
1219 AND jcc1.account_segment=jcc.account_segment
1220 AND jcc1.cost_segment =jcc.cost_segment
1221 AND nvl(jcc1.project_id,'0') =nvl(jcc.project_id ,'0')
1222 AND nvl(jcc1.project_number,'0') =nvl(jcc.project_number,'0') ) );
1223
1224 END IF;--l_cost_center_second_tracking
1225
1226 --update the period net occurance DR and CR
1227 UPDATE ja_cn_account_balances_post_gt u
1228 SET (FUNC_PERIOD_NET_DR
1229 , FUNC_PERIOD_NET_CR
1230 , ORIG_PERIOD_NET_DR
1231 , ORIG_PERIOD_NET_CR
1232 , project_source)=
1233 (SELECT FUNC_PERIOD_NET_DR
1234 , FUNC_PERIOD_NET_CR
1235 , ORIG_PERIOD_NET_DR
1236 , ORIG_PERIOD_NET_CR
1237 , project_source
1238 FROM ja_cn_account_balances
1239 WHERE ledger_id = p_ledger_id
1240 AND period_name = u.period_name
1241 AND LEGAL_ENTITY_ID = p_legal_entity_ID
1242 AND COMPANY_SEGMENT = u.company_segment
1243 AND CURRENCY_CODE = u.currency_code
1244 AND nvl(COST_CENTER,0) = nvl(u.cost_center,0)
1245 AND THIRD_PARTY_TYPE IS NULL
1246 AND THIRD_PARTY_ID IS NULL
1247 AND THIRD_PARTY_NUMBER IS NULL
1248 AND nvl(PROJECT_ID,'0')= nvl(u.project_id,'0')
1249 AND nvl(PROJECT_NUMBER,'0')= nvl(u.project_number,'0')/*
1250 and nvl(PROJECT_source,' ')=nvl(u.PROJECT_source,'COA')*/
1251 AND ACCOUNT_SEGMENT = u.account_segment
1252 AND PERSONNEL_ID IS NULL
1253 AND PERSONNEL_NUMBER IS NULL
1254 );
1255
1256 -- INSERT INTO hu_hu SELECT * FROM ja_cn_account_balances_post_gt;
1257
1258 --update the first priod begin balance in each year
1259 UPDATE ja_cn_account_balances_post_gt u
1260 SET ( FUNC_BEGIN_BALANCE_DR
1261 , FUNC_BEGIN_BALANCE_CR
1262 , ORIG_BEGIN_BALANCE_DR
1263 , ORIG_BEGIN_BALANCE_CR)=
1264 ( SELECT sum(nvl(gb.begin_balance_dr_beq,gb.begin_balance_dr))
1265 , sum(nvl(gb.begin_balance_cr_beq,gb.begin_balance_cr))
1266 , sum(gb.begin_balance_dr)
1267 , sum(gb.begin_balance_cr)
1268 FROM gl_balances gb
1269 , ja_cn_code_combination_v jcc
1270 , ja_cn_code_combination_v jcc1
1271 WHERE gb.ledger_id = p_ledger_id
1272 AND jcc.ledger_id = p_ledger_id
1273 AND gb.code_combination_id = jcc.CODE_COMBINATION_ID
1274 AND jcc.company_segment = u.company_segment
1275 AND jcc.account_segment = u.account_segment
1276 AND nvl(jcc.cost_segment,0)= nvl(u.cost_center,0)
1277 AND nvl(jcc.project_number,'0') = nvl(u.project_number,'0')
1278 AND gb.currency_code = u.currency_code
1279 AND gb.period_name = u.period_name
1280 AND jcc1.CODE_COMBINATION_ID = l_RET_EARN_CODE_COMBINATION_ID
1281 AND jcc1.ledger_id = p_ledger_id
1282 AND jcc1.company_segment = jcc.company_segment
1283 AND jcc1.account_segment = jcc.account_segment
1284 AND jcc1.cost_segment = jcc.cost_segment
1285 AND jcc1.other_columns = jcc.other_columns
1286 AND gb.actual_flag = 'A'
1287 )
1288 WHERE period_mon=1;
1289
1290 UPDATE ja_cn_account_balances_post_gt u
1291 SET FUNC_BEGIN_BALANCE_DR = nvl(FUNC_BEGIN_BALANCE_DR,0)
1292 , FUNC_BEGIN_BALANCE_CR = nvl(FUNC_BEGIN_BALANCE_CR,0)
1293 , ORIG_BEGIN_BALANCE_DR = nvl(ORIG_BEGIN_BALANCE_DR,0)
1294 , ORIG_BEGIN_BALANCE_CR = nvl(ORIG_BEGIN_BALANCE_CR,0)
1295 , FUNC_PERIOD_NET_DR = nvl(FUNC_PERIOD_NET_DR ,0)
1296 , FUNC_PERIOD_NET_CR = nvl(FUNC_PERIOD_NET_CR ,0)
1297 , ORIG_PERIOD_NET_DR = nvl(ORIG_PERIOD_NET_DR ,0)
1298 , ORIG_PERIOD_NET_CR = nvl(ORIG_PERIOD_NET_CR ,0);
1299
1300 --update the other month balance
1301 SELECT max(jcp.period_num - l_period_year*1000)
1302 INTO l_number
1303 FROM ja_cn_periods jcp;
1304
1305 FOR i IN 2..l_number
1306 LOOP
1307 UPDATE ja_cn_account_balances_post_gt u
1308 SET (FUNC_BEGIN_BALANCE_DR
1309 , FUNC_BEGIN_BALANCE_CR
1310 , ORIG_BEGIN_BALANCE_DR
1311 , ORIG_BEGIN_BALANCE_CR )=
1312 (SELECT FUNC_BEGIN_BALANCE_DR + FUNC_PERIOD_NET_DR
1313 , FUNC_BEGIN_BALANCE_CR + FUNC_PERIOD_NET_CR
1314 , ORIG_BEGIN_BALANCE_DR + ORIG_PERIOD_NET_DR
1315 , ORIG_BEGIN_BALANCE_CR + ORIG_PERIOD_NET_CR
1316 FROM ja_cn_account_balances_post_gt
1317 WHERE period_num=u.period_num - 1
1318 AND ledger_id = p_ledger_id
1319 AND LEGAL_ENTITY_ID = p_legal_entity_ID
1320 AND COMPANY_SEGMENT = u.company_segment
1321 AND CURRENCY_CODE = u.currency_code
1322 AND nvl(COST_CENTER,0) = nvl(u.cost_center,0)
1323 AND THIRD_PARTY_TYPE IS NULL
1324 AND THIRD_PARTY_ID IS NULL
1325 AND THIRD_PARTY_NUMBER IS NULL
1326 AND nvl(PROJECT_ID ,'0') = nvl(u.project_id ,'0')
1327 AND nvl(PROJECT_NUMBER ,'0') = nvl(u.project_number,'0')
1328 AND ACCOUNT_SEGMENT = u.account_segment
1329 AND PERSONNEL_ID IS NULL
1330 AND PERSONNEL_NUMBER IS NULL
1331 )
1332 WHERE u.period_mon=i;
1333
1334 END LOOP;
1335
1336 -- replace the new balance of retain earning account
1337 DELETE ja_cn_account_balances u
1338 WHERE EXISTS
1339 (SELECT *
1340 FROM ja_cn_account_balances_post_gt t
1341 WHERE t.ledger_id =u.ledger_id
1342 AND t.LEGAL_ENTITY_ID =u.LEGAL_ENTITY_ID
1343 AND t.COMPANY_SEGMENT =u.COMPANY_SEGMENT
1344 AND t.CURRENCY_CODE =u.CURRENCY_CODE
1345 AND nvl(t.COST_CENTER,0) =nvl(u.COST_CENTER,0)
1346 AND t.ACCOUNT_SEGMENT =u.ACCOUNT_SEGMENT
1347 AND nvl(t.THIRD_PARTY_TYPE ,'0') =nvl(u.THIRD_PARTY_TYPE ,'0')
1348 AND nvl(t.THIRD_PARTY_ID ,'0') =nvl(u.THIRD_PARTY_ID ,'0')
1349 AND nvl(t.THIRD_PARTY_NUMBER,'0') =nvl(u.THIRD_PARTY_NUMBER,'0')
1350 AND nvl(t.PROJECT_ID ,'0') =nvl(u.PROJECT_ID ,'0')
1351 AND nvl(t.PROJECT_NUMBER ,'0') =nvl(u.PROJECT_NUMBER ,'0')
1352 AND nvl(t.PERSONNEL_ID ,'0') =nvl(u.PERSONNEL_ID ,'0')
1353 AND nvl(t.PERSONNEL_NUMBER ,'0') =nvl(u.PERSONNEL_NUMBER ,'0'));
1354
1355 INSERT INTO ja_cn_account_balances(
1356 ledger_id
1357 , LEGAL_ENTITY_ID
1358 , COMPANY_SEGMENT
1359 , PERIOD_NAME
1360 , CURRENCY_CODE
1361 , COST_CENTER
1362 , THIRD_PARTY_TYPE
1363 , THIRD_PARTY_ID
1364 , THIRD_PARTY_NUMBER
1365 , PROJECT_ID
1366 , PROJECT_NUMBER
1367 , PROJECT_SOURCE
1368 , ACCOUNT_SEGMENT
1369 , PERSONNEL_ID
1370 , PERSONNEL_NUMBER
1371 , FUNC_BEGIN_BALANCE_DR
1372 , FUNC_BEGIN_BALANCE_CR
1373 , ORIG_BEGIN_BALANCE_DR
1374 , ORIG_BEGIN_BALANCE_CR
1375 , FUNC_PERIOD_NET_DR
1376 , FUNC_PERIOD_NET_CR
1377 , ORIG_PERIOD_NET_DR
1378 , ORIG_PERIOD_NET_CR
1379 , PERIOD_MON
1380 , ACCOUNT_TYPE
1381 , CREATED_BY
1382 , CREATION_DATE
1383 , LAST_UPDATED_BY
1384 , LAST_UPDATE_DATE
1385 , LAST_UPDATE_LOGIN
1386 )
1387 SELECT ledger_id
1388 , LEGAL_ENTITY_ID
1389 , COMPANY_SEGMENT
1390 , PERIOD_NAME
1391 , CURRENCY_CODE
1392 , COST_CENTER
1393 , THIRD_PARTY_TYPE
1394 , THIRD_PARTY_ID
1395 , THIRD_PARTY_NUMBER
1396 , PROJECT_ID
1397 , PROJECT_NUMBER
1398 , PROJECT_SOURCE
1399 , ACCOUNT_SEGMENT
1400 , PERSONNEL_ID
1401 , PERSONNEL_NUMBER
1402 , FUNC_BEGIN_BALANCE_DR
1403 , FUNC_BEGIN_BALANCE_CR
1404 , ORIG_BEGIN_BALANCE_DR
1405 , ORIG_BEGIN_BALANCE_CR
1406 , FUNC_PERIOD_NET_DR
1407 , FUNC_PERIOD_NET_CR
1408 , ORIG_PERIOD_NET_DR
1409 , ORIG_PERIOD_NET_CR
1410 , PERIOD_MON
1411 , ACCOUNT_TYPE
1412 , fnd_global.USER_ID
1413 , SYSDATE
1414 , fnd_global.USER_ID
1415 , SYSDATE
1416 , fnd_global.LOGIN_ID
1417 FROM ja_cn_account_balances_post_gt;
1418
1419 /* -- delete the old parent account balance
1420 DELETE ja_cn_account_balances jcb
1421 WHERE jcb.ledger_id = p_ledger_id
1422 AND jcb.legal_entity_id = p_legal_entity_ID
1423 AND EXISTS (SELECT jcp.ledger_id
1424 FROM ja_cn_periods jcp
1425 , fnd_flex_values ffv
1426 WHERE jcp.period_name=jcb.period_name
1427 AND jcp.ledger_id = p_ledger_id
1428 AND jcp.period_num BETWEEN l_period_num AND l_last_period_num
1429 AND ffv.flex_value_set_id = l_flex_value_set_id
1430 AND ffv.flex_value = jcb.account_segment
1431 AND ffv.summary_flag = 'Y'
1432 );
1433
1434 -- insert the parent account balance
1435 INSERT INTO ja_cn_account_balances(
1436 ledger_id
1437 , LEGAL_ENTITY_ID
1438 , COMPANY_SEGMENT
1439 , PERIOD_NAME
1440 , CURRENCY_CODE
1441 , COST_CENTER
1442 , THIRD_PARTY_TYPE
1443 , THIRD_PARTY_ID
1444 , THIRD_PARTY_NUMBER
1445 , PROJECT_ID
1446 , PROJECT_NUMBER
1447 , PROJECT_SOURCE
1448 , ACCOUNT_SEGMENT
1449 , PERSONNEL_ID
1450 , PERSONNEL_NUMBER
1451 , FUNC_BEGIN_BALANCE_DR
1452 , FUNC_BEGIN_BALANCE_CR
1453 , ORIG_BEGIN_BALANCE_DR
1454 , ORIG_BEGIN_BALANCE_CR
1455 , FUNC_PERIOD_NET_DR
1456 , FUNC_PERIOD_NET_CR
1457 , ORIG_PERIOD_NET_DR
1458 , ORIG_PERIOD_NET_CR
1459 , PERIOD_MON
1460 , ACCOUNT_TYPE
1461 , CREATED_BY
1462 , CREATION_DATE
1463 , LAST_UPDATED_BY
1464 , LAST_UPDATE_DATE
1465 , LAST_UPDATE_LOGIN )
1466 SELECT p_ledger_id
1467 , p_legal_entity_ID
1468 , jcc.company_segment
1469 , gb.period_name
1470 , l_ret_currence_code
1471 , jcc.cost_segment
1472 , NULL
1473 , NULL
1474 , NULL
1475 , NULL
1476 , NULL
1477 , 'COA'
1478 , jcc.account_segment
1479 , NULL
1480 , NULL
1481 , SUM(nvl(gb.begin_balance_dr_beq,0))
1482 , SUM(nvl(gb.begin_balance_cr_beq,0))
1483 , SUM(nvl(gb.begin_balance_dr,0))
1484 , SUM(nvl(gb.begin_balance_cr,0))
1485 , SUM(nvl(gb.period_net_dr_beq,0))
1486 , SUM(nvl(gb.period_net_cr_beq,0))
1487 , SUM(nvl(gb.period_net_dr,0))
1488 , SUM(nvl(gb.period_net_cr,0))
1489 , gb.period_num
1490 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
1491 , fnd_global.USER_ID
1492 , SYSDATE
1493 , fnd_global.USER_ID
1494 , SYSDATE
1495 , fnd_global.LOGIN_ID
1496 FROM gl_balances gb
1497 , ja_cn_code_combination_v jcc
1498 , fnd_flex_values ffv
1499 WHERE gb.ledger_id = p_ledger_id
1500 AND jcc.ledger_id = p_ledger_id
1501 AND gb.code_combination_id = jcc.CODE_COMBINATION_ID
1502 AND jcc.account_segment = ffv.flex_value
1503 AND ffv.flex_value_set_id = l_flex_value_set_id
1504 AND ffv.summary_flag = 'Y'
1505 AND gb.actual_flag = 'Y'
1506 AND gb.currency_code = l_ret_currence_code
1507 AND gb.period_year*1000+gb.period_num BETWEEN l_period_num AND l_last_period_num
1508 GROUP BY jcc.company_segment
1509 , gb.period_name
1510 , l_ret_currence_code
1511 , jcc.cost_segment
1512 , jcc.account_segment
1513 , gb.period_num
1514 , ffv.COMPILED_VALUE_ATTRIBUTES;*/
1515
1516 -- delete the old parent account balance
1517 DELETE ja_cn_account_balances jcb
1518 WHERE jcb.ledger_id = p_ledger_id
1519 AND jcb.legal_entity_id = p_legal_entity_ID
1520 AND EXISTS (SELECT jcp.ledger_id
1521 FROM ja_cn_periods jcp
1522 , fnd_flex_values ffv
1523 WHERE jcp.period_name=jcb.period_name
1524 AND jcp.ledger_id = p_ledger_id
1525 AND jcp.period_num BETWEEN l_period_num AND l_last_period_num
1526 AND ffv.flex_value_set_id = l_flex_value_set_id
1527 AND ffv.flex_value = jcb.account_segment
1528 AND ffv.summary_flag = 'Y'
1529 );
1530
1531 -- generate the account structure to lowest level
1532 INSERT INTO JA_CN_ACCOUNT_CHILDREN_GT(
1533 parent_account
1534 , child_account
1535 , summary_flag
1536 , PARENT_TYPE
1537 )
1538 SELECT H.PARENT_FLEX_VALUE
1539 , V.FLEX_VALUE
1540 , V.SUMMARY_FLAG
1541 , substr(v2.compiled_value_attributes,5,1)
1542 FROM FND_FLEX_VALUES V
1543 , FND_FLEX_VALUE_NORM_HIERARCHY H
1544 , FND_FLEX_VALUES V2
1545 WHERE v.flex_value_set_id = l_flex_value_set_id
1546 AND h.flex_value_set_id = l_flex_value_set_id
1547 AND V.FLEX_VALUE BETWEEN H.CHILD_FLEX_VALUE_LOW AND H.CHILD_FLEX_VALUE_HIGH
1548 AND ((V.SUMMARY_FLAG = 'Y' AND H.RANGE_ATTRIBUTE = 'P') OR
1549 (V.SUMMARY_FLAG = 'N' AND H.RANGE_ATTRIBUTE = 'C'))
1550 AND v2.flex_value_set_id = l_flex_value_set_id
1551 AND h.PARENT_FLEX_VALUE = v2.FLEX_VALUE;
1552
1553 i:=0;
1554 LOOP
1555 i:=i+1;
1556 IF i>6
1557 THEN
1558 EXIT;
1559 END IF;
1560
1561 INSERT INTO JA_CN_ACCOUNT_CHILDREN_GT(
1562 parent_account
1563 , child_account
1564 , summary_flag
1565 , PARENT_TYPE
1566 )
1567 SELECT DISTINCT jca.parent_account
1568 , v.FLEX_VALUE
1569 , v.SUMMARY_FLAG
1570 , jca.parent_type
1571 FROM JA_CN_ACCOUNT_CHILDREN_GT jca
1572 , FND_FLEX_VALUES v
1573 , FND_FLEX_VALUE_NORM_HIERARCHY h
1574 WHERE jca.child_account = h.parent_flex_value
1575 AND v.flex_value_set_id = l_flex_value_set_id
1576 AND h.flex_value_set_id = l_flex_value_set_id
1577 AND V.FLEX_VALUE BETWEEN H.CHILD_FLEX_VALUE_LOW AND H.CHILD_FLEX_VALUE_HIGH
1578 AND ((V.SUMMARY_FLAG = 'Y' AND H.RANGE_ATTRIBUTE = 'P') OR
1579 (V.SUMMARY_FLAG = 'N' AND H.RANGE_ATTRIBUTE = 'C'))
1580 AND NOT EXISTS(SELECT *
1581 FROM JA_CN_ACCOUNT_CHILDREN_GT t
1582 WHERE t.parent_account = jca.parent_account
1583 AND t.child_account = v.FLEX_VALUE);
1584
1585 IF SQL%ROWCOUNT = 0 THEN
1586 EXIT;
1587 END IF;
1588
1589 END LOOP;
1590
1591 DELETE JA_CN_ACCOUNT_CHILDREN_GT WHERE summary_flag = 'Y';
1592
1593 --insert the parent account balance
1594 INSERT INTO ja_cn_account_balances(
1595 ledger_id
1596 , LEGAL_ENTITY_ID
1597 , COMPANY_SEGMENT
1598 , PERIOD_NAME
1599 , CURRENCY_CODE
1600 , COST_CENTER
1601 , THIRD_PARTY_TYPE
1602 , THIRD_PARTY_ID
1603 , THIRD_PARTY_NUMBER
1604 , PROJECT_ID
1605 , PROJECT_NUMBER
1606 , PROJECT_SOURCE
1607 , ACCOUNT_SEGMENT
1608 , PERSONNEL_ID
1609 , PERSONNEL_NUMBER
1610 , FUNC_BEGIN_BALANCE_DR
1611 , FUNC_BEGIN_BALANCE_CR
1612 , ORIG_BEGIN_BALANCE_DR
1613 , ORIG_BEGIN_BALANCE_CR
1614 , FUNC_PERIOD_NET_DR
1615 , FUNC_PERIOD_NET_CR
1616 , ORIG_PERIOD_NET_DR
1617 , ORIG_PERIOD_NET_CR
1618 , PERIOD_MON
1619 , ACCOUNT_TYPE
1620 , CREATED_BY
1621 , CREATION_DATE
1622 , LAST_UPDATED_BY
1623 , LAST_UPDATE_DATE
1624 , LAST_UPDATE_LOGIN )
1625 SELECT p_ledger_id
1626 , p_legal_entity_ID
1627 , jcb.company_segment
1628 , jcb.period_name
1629 , l_ret_currence_code
1630 , NULL
1631 , NULL
1632 , NULL
1633 , NULL
1634 , NULL
1635 , NULL
1636 , 'COA'
1637 , jcc.parent_account
1638 , NULL
1639 , NULL
1640 , SUM(nvl(FUNC_BEGIN_BALANCE_DR,0))
1641 , SUM(nvl(FUNC_BEGIN_BALANCE_CR,0))
1642 , SUM(nvl(FUNC_BEGIN_BALANCE_DR,0))
1643 , SUM(nvl(FUNC_BEGIN_BALANCE_CR,0))
1644 , SUM(nvl(FUNC_PERIOD_NET_DR,0))
1645 , SUM(nvl(FUNC_PERIOD_NET_CR,0))
1646 , SUM(nvl(FUNC_PERIOD_NET_DR,0))
1647 , SUM(nvl(FUNC_PERIOD_NET_CR,0))
1648 , jcb.period_mon
1649 , jcc.parent_type
1650 , fnd_global.USER_ID
1651 , SYSDATE
1652 , fnd_global.USER_ID
1653 , SYSDATE
1654 , fnd_global.LOGIN_ID
1655 FROM ja_cn_account_balances jcb
1656 , JA_CN_ACCOUNT_CHILDREN_GT jcc
1657 , ja_cn_periods jcp
1658 WHERE jcc.child_account = jcb.account_segment
1659 AND jcp.ledger_id = p_ledger_id
1660 AND jcb.legal_entity_id = p_legal_entity_ID
1661 AND jcb.ledger_id = p_ledger_id
1662 AND jcb.period_name = jcp.period_name
1663 AND jcp.period_num BETWEEN l_period_num AND l_last_period_num
1664 GROUP BY jcb.company_segment
1665 , jcb.period_name
1666 , jcc.parent_account
1667 , jcb.period_mon
1668 , jcc.parent_type;
1669
1670
1671 /* --initiate the working area
1672 DELETE ja_cn_account_balances_post_gt;*/
1673
1674 --insert all parent account having only non-parent account
1675 /* INSERT INTO ja_cn_account_balances(
1676 ledger_id
1677 , LEGAL_ENTITY_ID
1678 , COMPANY_SEGMENT
1679 , PERIOD_NAME
1680 , CURRENCY_CODE
1681 , COST_CENTER
1682 , THIRD_PARTY_TYPE
1683 , THIRD_PARTY_ID
1684 , THIRD_PARTY_NUMBER
1685 , PROJECT_ID
1686 , PROJECT_NUMBER
1687 , PROJECT_SOURCE
1688 , ACCOUNT_SEGMENT
1689 , PERSONNEL_ID
1690 , PERSONNEL_NUMBER
1691 , FUNC_BEGIN_BALANCE_DR
1692 , FUNC_BEGIN_BALANCE_CR
1693 , ORIG_BEGIN_BALANCE_DR
1694 , ORIG_BEGIN_BALANCE_CR
1695 , FUNC_PERIOD_NET_DR
1696 , FUNC_PERIOD_NET_CR
1697 , ORIG_PERIOD_NET_DR
1698 , ORIG_PERIOD_NET_CR
1699 , PERIOD_MON
1700 , ACCOUNT_TYPE
1701 , CREATED_BY
1702 , CREATION_DATE
1703 , LAST_UPDATED_BY
1704 , LAST_UPDATE_DATE
1705 , LAST_UPDATE_LOGIN )
1706 SELECT p_ledger_id
1707 , p_legal_entity_ID
1708 , jcc.company_segment
1709 , gb.period_name
1710 , l_ret_currence_code
1711 , jcc.cost_segment
1712 , NULL
1713 , NULL
1714 , NULL
1715 , NULL
1716 , NULL
1717 , 'COA'
1718 , jcc.account_segment
1719 , NULL
1720 , NULL
1721 , SUM(nvl(gb.begin_balance_dr_beq,0))
1722 , SUM(nvl(gb.begin_balance_cr_beq,0))
1723 , SUM(nvl(gb.begin_balance_dr,0))
1724 , SUM(nvl(gb.begin_balance_cr,0))
1725 , SUM(nvl(gb.period_net_dr_beq,0))
1726 , SUM(nvl(gb.period_net_cr_beq,0))
1727 , SUM(nvl(gb.period_net_dr,0))
1728 , SUM(nvl(gb.period_net_cr,0))
1729 , gb.period_num
1730 , substr(ffv.COMPILED_VALUE_ATTRIBUTES,5,1)
1731 , fnd_global.USER_ID
1732 , SYSDATE
1733 , fnd_global.USER_ID
1734 , SYSDATE
1735 , fnd_global.LOGIN_ID
1736 FROM ja_cn_periods jcp
1737 , ja_cn_account_balances jca
1738 , FND_FLEX_VALUE_CHILDREN_V ffvc
1739 , fnd_flex_values ffv
1740 WHERE jca.account_segment = ffvc.flex_value*/
1741
1742 IF( G_PROC_LEVEL >= g_debug_devel )
1743 THEN
1744 FND_LOG.STRING(G_PROC_LEVEL
1745 ,G_MODULE_PREFIX||l_procedure_name||'.end'
1746 ,'End procedure');
1747 END IF; --( G_PROC_LEVEL >= g_debug_devel)
1748 EXCEPTION
1749 WHEN OTHERS THEN
1750 IF(FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel)
1751 THEN
1752 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1753 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
1754 , SQLCODE||SQLERRM);
1755 END IF;
1756 RAISE;
1757 END update_retained_parent_account;
1758
1759 --==========================================================================
1760 -- PROCEDURE NAME:
1761 -- post_journal_itemized Public
1762 --
1763 -- DESCRIPTION:
1764 -- This procedure is used to open a period which had never post
1765 -- journal from "Itemized journal table" to "Itemized balance table"
1766 --
1767 -- PARAMETERS:
1768 -- In: p_period_name the end period name in which
1769 -- the CNAO journal should be processed
1770 -- p_ledger_id Ledger ID
1771 -- p_legal_entity_ID Legal entity id
1772
1773 --
1774 -- DESIGN REFERENCES:
1775 -- None
1776 --
1777 -- CHANGE HISTORY:
1778 -- 02/21/2006 Jogen Hu Created
1779 -- 04/28/2007 Qingjun Zhao Change SOB to Ledger for upgrade
1780 -- from 11i to R12
1781 --===========================================================================
1782 PROCEDURE post_journal_itemized
1783 ( p_period_name IN VARCHAR2
1784 , p_ledger_id IN NUMBER
1785 , p_legal_entity_ID IN NUMBER
1786 )
1787 IS
1788 l_procedure_name VARCHAR2(30):='post_journal_itemized';
1789 --l_last_open_period_year ja_cn_periods.period_name%TYPE;
1790 --l_last_open_period ja_cn_periods.period_name%TYPE;
1791 l_period_year ja_cn_periods.period_year%TYPE;
1792 l_period_num ja_cn_periods.period_num%TYPE;
1793 l_flex_value_set_id FND_FLEX_VALUES.Flex_Value_Set_Id%TYPE;
1794 l_row_count NUMBER;
1795 l_balance_rec cnao_balance_rec;
1796
1797 l_earliest_changed_year ja_cn_periods.period_year%TYPE;
1798 l_earliest_changed_period ja_cn_periods.period_name%TYPE;
1799
1800 CURSOR c_periods(pc_year IN NUMBER
1801 ,pc_num IN NUMBER) IS
1802 SELECT DISTINCT gp.period_name,gp.period_year,gp.period_num
1803 FROM /*JA_CN_JOURNAL_LINES jjl
1804 , */gl_periods gp
1805 , gl_ledgers led
1806 WHERE /*jjl.ledger_id = p_ledger_id
1807 AND jjl.legal_entity_id = p_legal_entity_ID
1808 AND jjl.status = 'U'
1809 AND */led.ledger_id = p_ledger_id
1810 --AND jjl.period_name = gp.period_name
1811 AND gp.period_set_name = led.period_set_name
1812 AND gp.period_type = led.accounted_period_type
1813 AND (gp.period_year<pc_year
1814 OR (gp.period_year=pc_year AND gp.period_num<=pc_num)
1815 )
1816 ORDER BY gp.period_year,gp.period_num;
1817
1818 CURSOR c_journal_lines(pc_flex_value_set_id IN NUMBER
1819 ,pc_period_name IN VARCHAR2) IS
1820 SELECT jjl.journal_number
1821 , SUM(nvl(jjl.ENTERED_DR,0)) entered_dr
1822 , SUM(nvl(jjl.ENTERED_CR,0)) entered_cr
1823 , SUM(nvl(jjl.ACCOUNTED_DR,0)) accounted_dr
1824 , SUM(nvl(jjl.ACCOUNTED_CR,0)) accounted_cr
1825 , jjl.CURRENCY_CODE
1826 , jjl.CURRENCY_CONVERSION_RATE
1827 , jjl.COMPANY_SEGMENT
1828 , jjl.CODE_COMBINATION_ID
1829 , jjl.COST_CENTER
1830 , jjl.THIRD_PARTY_ID
1831 , jjl.THIRD_PARTY_NUMBER
1832 , jjl.third_party_type
1833 , jjl.PERSONNEL_ID
1834 , jjl.PERSONNEL_NUMBER
1835 , jjl.PROJECT_ID
1836 , jjl.PROJECT_NUMBER
1837 , jjl.project_source
1838 , jjl.ACCOUNT_SEGMENT
1839 , jjl.period_name
1840 , substr(COMPILED_VALUE_ATTRIBUTES,5,1) account_type
1841 FROM JA_CN_JOURNAL_LINES jjl
1842 , FND_FLEX_VALUES ffv
1843 WHERE jjl.ledger_id = p_ledger_id
1844 AND jjl.legal_entity_id = p_legal_entity_ID
1845 AND jjl.status = 'U'
1846 AND ffv.PARENT_FLEX_VALUE_LOW IS NULL
1847 AND ffv.FLEX_VALUE_SET_ID=pc_flex_value_set_id
1848 AND ffv.flex_value =jjl.account_segment
1849 AND jjl.period_name =pc_period_name
1850 GROUP BY jjl.journal_number
1851 , jjl.CURRENCY_CODE
1852 , jjl.CURRENCY_CONVERSION_RATE
1853 , jjl.COMPANY_SEGMENT
1854 , jjl.CODE_COMBINATION_ID
1855 , jjl.COST_CENTER
1856 , jjl.THIRD_PARTY_ID
1857 , jjl.THIRD_PARTY_NUMBER
1858 , jjl.third_party_type
1859 , jjl.PERSONNEL_ID
1860 , jjl.PERSONNEL_NUMBER
1861 , jjl.PROJECT_ID
1862 , jjl.PROJECT_NUMBER
1863 , jjl.project_source
1864 , jjl.ACCOUNT_SEGMENT
1865 , jjl.period_name
1866 , COMPILED_VALUE_ATTRIBUTES;
1867
1868 CURSOR c_after_periods(pc_period_num IN NUMBER) IS
1869 SELECT period_name
1870 , (period_num - period_year*1000) period_month
1871 FROM ja_cn_periods
1872 WHERE period_num>pc_period_num
1873 AND ledger_id=p_ledger_id
1874 AND status='O';
1875
1876 CURSOR c_after_periods_in_year( pc_period_num IN NUMBER
1877 , pc_period_year IN NUMBER) IS
1878 SELECT period_name
1879 , (period_num - period_year*1000) period_month
1880 FROM ja_cn_periods
1881 WHERE period_num>pc_period_num
1882 AND status='O'
1883 AND period_year=pc_period_year
1884 AND ledger_id=p_ledger_id;
1885
1886 BEGIN
1887 IF( G_PROC_LEVEL >= g_debug_devel )
1888 THEN
1889 FND_LOG.STRING(G_PROC_LEVEL
1890 ,G_MODULE_PREFIX||l_procedure_name||'.begin'
1891 ,'Begin procedure');
1892 END IF; --( G_PROC_LEVEL >= g_debug_devel)
1893
1894 --get period year and num
1895 SELECT gp.period_year
1896 , gp.period_num
1897 INTO l_period_year
1898 , l_period_num
1899 FROM gl_periods gp
1900 , gl_ledgers led
1901 WHERE gp.period_name=p_period_name
1902 AND gp.period_set_name=led.period_set_name
1903 AND led.ledger_id=p_ledger_id;
1904
1905 l_earliest_changed_year:=l_period_year;
1906 l_earliest_changed_period:=p_period_name;
1907
1908 IF( G_STATEMENT_LEVEL >= g_debug_devel )
1909 THEN
1910 put_log(G_MODULE_PREFIX||l_procedure_name||'.period year and num'
1911 ,l_period_year||':'||l_period_num);
1912 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
1913
1914 --get the flexfield set ID for chart of account
1915 SELECT ifs.flex_value_set_id
1916 INTO l_flex_value_set_id
1917 FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
1918 , gl_ledgers led
1919 , Fnd_Id_Flex_Segments ifs
1920 WHERE sav.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
1921 AND sav.ID_FLEX_CODE = 'GL#'
1922 AND sav.APPLICATION_ID = 101
1923 AND sav.attribute_value = 'Y'
1924 AND sav.ID_FLEX_NUM = led.chart_of_accounts_id
1925 AND ifs.application_id = 101
1926 AND ifs.Id_Flex_Code = 'GL#'
1927 AND ifs.id_flex_num = led.chart_of_accounts_id
1928 AND ifs.APPLICATION_COLUMN_NAME= sav.APPLICATION_COLUMN_NAME
1929 AND led.ledger_id = p_ledger_id;
1930
1931 IF( G_STATEMENT_LEVEL >= g_debug_devel )
1932 THEN
1933 put_log(G_MODULE_PREFIX||l_procedure_name||'.l_flex_value_set_id'
1934 ,l_flex_value_set_id);
1935 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
1936
1937 FOR rec_period IN c_periods(l_period_year,l_period_num)
1938 LOOP
1939
1940 SAVEPOINT each_periods;
1941 put_log(G_MODULE_PREFIX||l_procedure_name||'.period name'
1942 ,rec_period.period_name||':'||to_char(SYSDATE,'hh:mi:ss'));
1943 SELECT COUNT(*)
1944 INTO l_row_count
1945 FROM ja_cn_periods
1946 WHERE period_name=rec_period.period_name
1947 AND ledger_id=p_ledger_id;
1948
1949 IF( G_STATEMENT_LEVEL >= g_debug_devel )
1950 THEN
1951 put_log(G_MODULE_PREFIX||l_procedure_name||'.period rowcount'
1952 ,l_row_count);
1953 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
1954
1955 IF l_row_count=0 --current period is processed first time
1956 THEN
1957 open_period( p_period_name => rec_period.period_name
1958 , p_ledger_id => p_ledger_id
1959 , p_legal_entity_ID => p_legal_entity_ID
1960 );
1961 END IF;--l_row_count=0
1962
1963 FOR rec_line IN c_journal_lines(l_flex_value_set_id,rec_period.period_name)
1964 LOOP
1965
1966 IF l_earliest_changed_year > rec_period.period_year
1967 THEN
1968 l_earliest_changed_year:=rec_period.period_year;
1969 l_earliest_changed_period:=rec_period.period_name;
1970 END IF;
1971
1972 l_balance_rec.ledger_id := p_ledger_id ;
1973 l_balance_rec.LEGAL_ENTITY_ID := p_legal_entity_ID ;
1974 l_balance_rec.COMPANY_SEGMENT := rec_line.company_segment ;
1975 l_balance_rec.PERIOD_NAME := rec_line.period_name ;
1976 l_balance_rec.CURRENCY_CODE := rec_line.currency_code ;
1977 l_balance_rec.COST_CENTER := rec_line.COST_CENTER ;
1978 l_balance_rec.THIRD_PARTY_TYPE := rec_line.third_party_type ;
1979 l_balance_rec.THIRD_PARTY_ID := rec_line.third_party_id ;
1980 l_balance_rec.THIRD_PARTY_NUMBER := rec_line.third_party_number;
1981 l_balance_rec.PROJECT_ID := rec_line.project_id ;
1982 l_balance_rec.PROJECT_NUMBER := rec_line.project_number ;
1983 l_balance_rec.PROJECT_SOURCE := rec_line.project_source ;
1984 l_balance_rec.ACCOUNT_SEGMENT := rec_line.account_segment ;
1985 l_balance_rec.account_type := rec_line.account_type ;
1986 l_balance_rec.PERSONNEL_ID := rec_line.personnel_id ;
1987 l_balance_rec.PERSONNEL_NUMBER := rec_line.personnel_number ;
1988 l_balance_rec.FUNC_PERIOD_NET_DR := rec_line.accounted_dr ;
1989 l_balance_rec.FUNC_PERIOD_NET_CR := rec_line.accounted_cr ;
1990 l_balance_rec.ORIG_PERIOD_NET_DR := rec_line.entered_dr ;
1991 l_balance_rec.ORIG_PERIOD_NET_CR := rec_line.entered_cr ;
1992 l_balance_rec.PERIOD_MON := rec_period.period_num ;
1993
1994 -- the account is retained account
1995 IF rec_line.account_type IN ('A','L','O')
1996 THEN
1997 update_balance( p_balance_rec => l_balance_rec
1998 , p_current_period_flag => 'Y'
1999 );
2000
2001 IF( G_STATEMENT_LEVEL >= g_debug_devel )
2002 THEN
2003 put_log( G_MODULE_PREFIX||l_procedure_name||'.after period'
2004 , l_period_year*1000+l_period_num);
2005 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
2006
2007 FOR rec_after_period IN
2008 c_after_periods(rec_period.period_year*1000+
2009 rec_period.period_num)
2010 LOOP
2011 l_balance_rec.PERIOD_NAME := rec_after_period.period_name ;
2012 l_balance_rec.PERIOD_MON := rec_after_period.period_month;
2013 update_balance( p_balance_rec => l_balance_rec
2014 , p_current_period_flag => 'N'
2015 );
2016
2017 END LOOP;
2018
2019 ELSE --the account is not-retained earning account
2020 IF( G_STATEMENT_LEVEL >= g_debug_devel )
2021 THEN
2022 put_log( G_MODULE_PREFIX||l_procedure_name||'.after period in the same year'
2023 , l_period_year*1000+l_period_num);
2024 END IF; --( G_STATEMENT_LEVEL >= g_debug_devel)
2025
2026 update_balance( p_balance_rec => l_balance_rec
2027 , p_current_period_flag => 'Y'
2028 );
2029
2030 FOR rec_after_period_in_year IN
2031 c_after_periods_in_year(
2032 rec_period.period_year*1000+rec_period.period_num
2033 ,rec_period.period_year)
2034 LOOP
2035 l_balance_rec.PERIOD_NAME := rec_after_period_in_year.period_name;
2036 l_balance_rec.PERIOD_MON := rec_after_period_in_year.period_month;
2037 update_balance( p_balance_rec => l_balance_rec
2038 , p_current_period_flag => 'N'
2039 );
2040
2041 END LOOP;
2042 END IF;--rec_line.account_type IN ('R','E')
2043
2044 END LOOP;
2045
2046 --update the journal status
2047 UPDATE ja_cn_journal_lines jl
2048 SET jl.status='P'
2049 WHERE jl.ledger_id = p_ledger_id
2050 AND jl.LEGAL_ENTITY_ID = p_legal_entity_ID
2051 AND jl.PERIOD_NAME = rec_period.period_name
2052 AND jl.status = 'U';
2053
2054 COMMIT;
2055 -- AND jl.PERSONNEL_NUMBER = rec_line.personnel_number
2056 END LOOP;
2057
2058 update_retained_parent_account
2059 ( p_period_name => l_earliest_changed_period
2060 , p_ledger_id => p_ledger_id
2061 , p_legal_entity_ID => p_legal_entity_ID
2062 );
2063
2064 IF( G_PROC_LEVEL >= g_debug_devel )
2065 THEN
2066 FND_LOG.STRING(G_PROC_LEVEL
2067 ,G_MODULE_PREFIX||l_procedure_name||'.end'
2068 ,'End procedure');
2069 END IF; --( G_PROC_LEVEL >= g_debug_devel)
2070 EXCEPTION
2071 WHEN OTHERS THEN
2072 IF(FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel)
2073 THEN
2074 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
2075 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
2076 , SQLCODE||SQLERRM);
2077 END IF;
2078 RAISE;
2079 END post_journal_itemized;
2080
2081 BEGIN
2082 g_debug_devel:=fnd_log.G_CURRENT_RUNTIME_LEVEL;
2083
2084 END JA_CN_POST_UTILITY_PKG;