[Home] [Help]
24: | Public Function |
25: | 1. before_report |
26: | This procedure is called from Import Control account initial |
27: | balance program to populate initial balance in |
28: | xla_control_balances table |
29: | |
30: | 2. after_report |
31: | This procedure is called from Import Control account initial |
32: | balance program to purge interface table |
160: --
161: -- Locks the table
162: --
163:
164: LOCK TABLE xla_control_balances IN EXCLUSIVE MODE;
165:
166:
167: -- Set run date which will be used by report
168:
232: -- set the initial_balance_flag='Y' for the minimum period rows for the account groups
233: -- which are being initialized
234: --
235:
236: update /*+ index(xcb1,xla_control_balances_N99) */ xla_control_balances xcb1 set initial_balance_flag='Y'
237: where initial_balance_flag<> 'Y'
238: and (xcb1.application_id , xcb1.ledger_id , xcb1.code_combination_id , xcb1.party_id
239: , NVL (xcb1.party_site_id, -9999) , NVL (xcb1.party_type_code, ' ') , xcb1.effective_period_num)
240: in
241: (select xcb.application_id, xcb.ledger_id, xcb.code_combination_id, xcb.party_id
242: , NVL (xcb.party_site_id, -9999)
243: , NVL (xcb.party_type_code, ' ')
244: ,min(xcb.effective_period_num)
245: from xla_control_balances xcb,
246: (select application_id, ledger_id, code_combination_id, party_id, party_site_id, party_type_code
247: from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL) stmp
248: where stmp.application_id = xcb.application_id
249: AND stmp.ledger_id = xcb.ledger_id
264: --
265: -- reset the initial_balance_flag='N' for the previously minimum period rows
266: --
267:
268: update /*+ index(b,xla_control_balances_N99) */ xla_control_balances b set b.initial_balance_flag ='N' where b.initial_balance_flag ='Y'
269: and ( b.application_id , b.ledger_id , b.code_combination_id , b.party_id, NVL (b.party_site_id, -9999) , NVL (b.party_type_code, ' ') )
270: in (select application_id, ledger_id, code_combination_id, party_id , NVL (party_site_id, -9999) , NVL (party_type_code, ' ')
271: from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL)
272: and effective_period_num
270: in (select application_id, ledger_id, code_combination_id, party_id , NVL (party_site_id, -9999) , NVL (party_type_code, ' ')
271: from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL)
272: and effective_period_num
273: >
274: (select min(effective_period_num) from xla_control_balances xcb
275: where b.application_id = xcb.application_id
276: AND b.ledger_id = xcb.ledger_id
277: AND b.code_combination_id = xcb.code_combination_id
278: AND b.party_id = xcb.party_id
289:
290: --
291: -- Fix any worngly flagged first_period_flag
292: --
293: update /*+ index(b,xla_control_balances_N99) */ xla_control_balances b set b.first_period_flag='N'
294: where ( b.application_id , b.ledger_id , b.code_combination_id , b.party_id, NVL (b.party_site_id, -9999) , NVL (b.party_type_code, ' ') ,b.effective_period_num )
295: in (select xcb.application_id , xcb.ledger_id , xcb.code_combination_id , xcb.party_id, NVL (xcb.party_site_id, -9999)
296: , NVL (xcb.party_type_code, ' '),xcb.effective_period_num
297: from
294: where ( b.application_id , b.ledger_id , b.code_combination_id , b.party_id, NVL (b.party_site_id, -9999) , NVL (b.party_type_code, ' ') ,b.effective_period_num )
295: in (select xcb.application_id , xcb.ledger_id , xcb.code_combination_id , xcb.party_id, NVL (xcb.party_site_id, -9999)
296: , NVL (xcb.party_type_code, ' '),xcb.effective_period_num
297: from
298: xla_control_balances xcb
299: , gl_period_statuses gps
300: where
301: gps.effective_period_num = xcb.effective_period_num
302: and gps.application_id = 101
311: --
312: -- set the fist_period_flag='Y' for rows whose period_num=1
313: --
314:
315: update /*+ index(b,xla_control_balances_N99) */ xla_control_balances b set b.first_period_flag='Y'
316: where ( b.application_id , b.ledger_id , b.code_combination_id , b.party_id, NVL (b.party_site_id, -9999) , NVL (b.party_type_code, ' ') ,b.effective_period_num )
317: in (select xcb.application_id , xcb.ledger_id , xcb.code_combination_id , xcb.party_id, NVL (xcb.party_site_id, -9999)
318: , NVL (xcb.party_type_code, ' '),xcb.effective_period_num
319: from
316: where ( b.application_id , b.ledger_id , b.code_combination_id , b.party_id, NVL (b.party_site_id, -9999) , NVL (b.party_type_code, ' ') ,b.effective_period_num )
317: in (select xcb.application_id , xcb.ledger_id , xcb.code_combination_id , xcb.party_id, NVL (xcb.party_site_id, -9999)
318: , NVL (xcb.party_type_code, ' '),xcb.effective_period_num
319: from
320: xla_control_balances xcb
321: , gl_period_statuses gps
322: where
323: gps.effective_period_num = xcb.effective_period_num
324: and gps.application_id = 101
349: --
350: --
351:
352: --
353: -- Populate existing initial balance (if interface period_name data exists in xla_control_balances for the account group)
354: --
355:
356: UPDATE xla_ctrl_balances_int xib
357: SET(existing_init_balance_dr
359: ,existing_effective_period_num) =
360: (SELECT beginning_balance_dr
361: ,beginning_balance_cr
362: ,glp.effective_period_num
363: FROM xla_control_balances xcb
364: ,gl_period_statuses glp
365: WHERE xcb.period_name = glp.period_name
366: AND xcb.ledger_id = glp.ledger_id
367: AND glp.application_id = 101
385: END IF;
386:
387:
388: --
389: -- Get miniMum effective period num existing in xla_control_balances (if initializing row period_name does not exists
390: -- but periods > initializing row period_name exists in xla_control_balance table then get the min of those periods)
391: --
392:
393: UPDATE xla_ctrl_balances_int xib
392:
393: UPDATE xla_ctrl_balances_int xib
394: SET existing_effective_period_num =
395: ( SELECT min(glp.effective_period_num) effective_period_num
396: FROM xla_control_balances xcb
397: ,gl_period_statuses glp
398: WHERE xcb.period_name = glp.period_name
399: AND xcb.ledger_id = glp.set_of_books_id
400: AND glp.application_id = 101
423: END IF;
424:
425: -- Begin Bug 12673025
426: --
427: -- Populate effective_period_num for new and valid account groups which are not in xla_control_balances
428: --
429:
430:
431: UPDATE xla_ctrl_balances_int xib
463: ,p_msg => 'Number of Rows update for existing balance:'||SQL%ROWCOUNT
464: ,p_level => C_LEVEL_STATEMENT );
465: END IF;
466: --
467: -- Populate row for Initial Balance in XLA_CONTROL_BALANCES
468: --
469:
470: IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
471: trace
476:
477: /* MERGE statement will first check for existing initial balance, in case
478: initial balance exist then difference of existing initial balance and new
479: initial balannce is added to Begninning Balance amount. Otherwise a new line
480: with amount equal to new initial balances is inserted into xla_control_balances */
481:
482: MERGE INTO xla_control_balances xba
483: USING (SELECT xin.application_id
484: ,xin.ledger_id
478: initial balance exist then difference of existing initial balance and new
479: initial balannce is added to Begninning Balance amount. Otherwise a new line
480: with amount equal to new initial balances is inserted into xla_control_balances */
481:
482: MERGE INTO xla_control_balances xba
483: USING (SELECT xin.application_id
484: ,xin.ledger_id
485: ,xin.code_combination_id
486: ,xin.party_type_code
573:
574: IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
575: trace
576: (p_module => l_log_module
577: ,p_msg => '# rows merged with xla_control_balances : '||SQL%ROWCOUNT
578: ,p_level => C_LEVEL_PROCEDURE);
579: END IF;
580:
581: --
581: --
582: -- reset the initial_balance_flag='N' for the previously minimum period rows
583: --
584:
585: update /*+ index(b,xla_control_balances_N99) */ xla_control_balances b set b.initial_balance_flag ='N' where b.initial_balance_flag ='Y'
586: and ( b.application_id , b.ledger_id , b.code_combination_id , b.party_id, NVL (b.party_site_id, -9999) , NVL (b.party_type_code, ' ') )
587: in (select application_id, ledger_id, code_combination_id, party_id , NVL (party_site_id, -9999) , NVL (party_type_code, ' ')
588: from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL)
589: and effective_period_num
587: in (select application_id, ledger_id, code_combination_id, party_id , NVL (party_site_id, -9999) , NVL (party_type_code, ' ')
588: from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL)
589: and effective_period_num
590: >
591: (select min(effective_period_num) from xla_control_balances xcb
592: where b.application_id = xcb.application_id
593: AND b.ledger_id = xcb.ledger_id
594: AND b.code_combination_id = xcb.code_combination_id
595: AND b.party_id = xcb.party_id
705: ,l_array_party_site_id
706: ,l_array_period_year
707: ,l_array_new_dr
708: ,l_array_new_cr
709: FROM xla_control_balances xcb
710: ,xla_ctrl_balances_int xib
711: WHERE xib.application_id = xcb.application_id
712: AND xib.ledger_id = xcb.ledger_id
713: AND xib.code_combination_id = xcb.code_combination_id
718: AND xib.status IS NULL
719: AND (xcb.first_period_flag = 'Y' OR xcb.initial_balance_flag = 'Y');
720:
721: FORALL i IN 1..l_array_appl_id.count
722: UPDATE xla_control_balances
723: SET beginning_balance_dr = NVL(beginning_balance_dr,0)+ l_array_new_dr(i)
724: ,beginning_balance_cr = NVL(beginning_balance_cr,0) + l_array_new_cr(i)
725: WHERE application_id = l_array_appl_id(i)
726: AND ledger_id = l_array_ledger_id(i)
741: --
742: -- Insert rows for carry forwarding Initial balance from Initial period to
743: -- existing first period (for already existing account groups)
744: --
745: INSERT INTO xla_control_balances
746: (application_id
747: ,ledger_id
748: ,code_combination_id
749: ,party_type_code
807: ,glp1.period_year
808: ,glp1.effective_period_num
809: FROM xla_ctrl_balances_int xin
810: ,gl_period_statuses glp1
811: ,xla_control_balances xcb
812: where
813: xcb.application_id = xin.application_id
814: AND xcb.ledger_id = xin.ledger_id
815: AND xcb.code_combination_id = xin.code_combination_id
826: ) b
827: -- begin Bug12655377
828: where not exists
829: (
830: select 'x' from xla_control_balances xcb1 where
831: xcb1.application_id = b.application_id
832: AND xcb1.ledger_id = b.ledger_id
833: AND xcb1.code_combination_id = b.code_combination_id
834: AND nvl(xcb1.party_type_code,' ') = nvl(b.party_type_code,' ')
863: ,l_array_party_id
864: ,l_array_party_site_id
865: ,l_array_period_num
866: FROM xla_ctrl_balances_int xib
867: ,xla_control_balances xcb
868: ,gl_period_statuses glp
869: WHERE xib.application_id = xcb.application_id
870: AND xib.ledger_id = xcb.ledger_id
871: AND xib.code_combination_id = xcb.code_combination_id
886: ,xcb.party_id
887: ,nvl(xcb.party_site_id,-999);
888:
889: FORALL i in 1..l_array_appl_id.count
890: DELETE FROM xla_control_balances
891: WHERE application_id = l_array_appl_id(i)
892: AND ledger_id = l_array_ledger_id(i)
893: AND code_combination_id = l_array_ccid(i)
894: AND party_type_code = l_array_party_type_code(i)
908: ,p_level => C_LEVEL_PROCEDURE);
909: END IF;
910:
911: FORALL i in 1..l_array_appl_id.count
912: UPDATE xla_control_balances
913: SET first_period_flag = 'Y'
914: WHERE application_id = l_array_appl_id(i)
915: AND ledger_id = l_array_ledger_id(i)
916: AND code_combination_id = l_array_ccid(i)
1109: ,xcb.party_type_code
1110: ,xcb.party_id
1111: ,xcb.party_site_id
1112: ,MIN(xcb.effective_period_num) effective_period_num
1113: FROM xla_control_balances xcb
1114: ,xla_ctrl_balances_int xib
1115: WHERE xcb.application_id = xib.application_id
1116: AND xcb.ledger_id = xib.ledger_id
1117: AND xcb.code_combination_id = xib.code_combination_id