[Home] [Help]
17: | 23-Sep-2008 rajose bug#7364921 Upgraded invoices not appearing|
18: | in the TB report for a given date range. |
19: | 5-Mar-2009 ssawhney BUG 8222265 mainline perf changes |
20: | 20-Oct-2010 nksurana bug#10135907 Removed the parallel hint and |
21: | join with xla_trial_balances to improve |
22: | performance and avoid the error |
23: | XLA_TB_USER_TRANS_VIEWS_U1 violated. |
24: | 25-Nov-2011 nmikkili Bug 13390676 adding full(xah) and full(xal)|
25: | hints in insert to xla_trial_balances in |
21: | join with xla_trial_balances to improve |
22: | performance and avoid the error |
23: | XLA_TB_USER_TRANS_VIEWS_U1 violated. |
24: | 25-Nov-2011 nmikkili Bug 13390676 adding full(xah) and full(xal)|
25: | hints in insert to xla_trial_balances in |
26: | create_ap_balances |
27: | 22-Mar-2012 vgopiset Bug13864126 changes in create_defn_in_batch|
28: | to insert details only when the Definition |
29: | is not there in TB Set Up Tables to avoid |
1826: ,l_Log_module);
1827: END IF;
1828:
1829: --add parallel hint per bug 8222265
1830: --bug#10135907 Removed the parallel hint and join with xla_trial_balances
1831:
1832: INSERT INTO xla_tb_user_trans_views
1833: (definition_code
1834: ,application_id
2025: -- range for the next day ie '01-MAY-2008' to '01-JUN-2008'
2026:
2027: --BUG 8222265 added a swap_join_inputs(gcc) hint and flipped first 2 tables in FROM clause
2028:
2029: INSERT /*+ parallel(xtb) append */ INTO xla_trial_balances xtb(
2030: record_type_code
2031: ,source_entity_id
2032: ,event_class_code
2033: ,source_application_id
2748: l_schema := NULL;
2749: END IF;
2750:
2751: --
2752: -- Add partitions to xla_trial_balances
2753: --
2754: -- commented and rewrote to make the program re-runnable
2755: /*
2756: IF (l_array_defn_code.COUNT > 0 ) THEN
2757: FOR i in l_array_defn_code.FIRST .. l_array_defn_code.LAST LOOP
2758: BEGIN
2759: IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2760: trace
2761: (p_msg => 'ALTER TABLE '||l_schema||'.xla_trial_balances'
2762: ||' ADD PARTITION '||l_array_defn_code(i)
2763: || ' VALUES ('''||l_array_defn_code(i)||''' ) executing'
2764: ,p_level => C_LEVEL_STATEMENT
2765: ,p_module => l_log_module);
2770: ,p_module => l_log_module);
2771: END IF;
2772:
2773: EXECUTE IMMEDIATE
2774: 'ALTER TABLE '||l_schema||'.xla_trial_balances'||' ADD PARTITION '||l_array_defn_code(i)||
2775: ' VALUES ('''||l_array_defn_code(i)||''' )';
2776: EXCEPTION
2777: WHEN OTHERS THEN
2778: IF(SQLCODE = -14312) THEN
2795: FROM XLA_TB_DEFINITIONS_B TB
2796: WHERE NOT EXISTS
2797: ( SELECT 1
2798: FROM DBA_TAB_PARTITIONS
2799: WHERE TABLE_NAME = 'XLA_TRIAL_BALANCES'
2800: AND TABLE_OWNER = UPPER(l_schema)
2801: AND PARTITION_NAME = UPPER(TB.DEFINITION_CODE) ) ) LOOP
2802: BEGIN
2803: IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2801: AND PARTITION_NAME = UPPER(TB.DEFINITION_CODE) ) ) LOOP
2802: BEGIN
2803: IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2804: trace
2805: (p_msg => 'ALTER TABLE '||l_schema||'.xla_trial_balances'
2806: ||' ADD PARTITION '|| i.DEFINITION_CODE
2807: || ' VALUES ('''||i.DEFINITION_CODE||''' ) executing'
2808: ,p_level => C_LEVEL_STATEMENT
2809: ,p_module => l_log_module);
2814: ,p_module => l_log_module);
2815: END IF;
2816:
2817: EXECUTE IMMEDIATE
2818: 'ALTER TABLE '||l_schema||'.xla_trial_balances'||' ADD PARTITION '||i.DEFINITION_CODE ||
2819: ' VALUES ('''||i.DEFINITION_CODE||''' )';
2820: EXCEPTION
2821: WHEN OTHERS THEN
2822: IF(SQLCODE = -14312) THEN