[Home] [Help]
99: h_sql := 'DELETE FROM '||x_table_name||
100: ' WHERE '||h_key_column_name||' NOT IN ('||
101: ' SELECT CODE FROM '||h_view_name||
102: ')';
103: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
104:
105: FETCH c_filters INTO h_key_column_name, h_view_name;
106: END LOOP;
107: CLOSE c_filters;
110: RETURN TRUE;
111: EXCEPTION
112: WHEN e_unexpected_error THEN
113: BSC_MESSAGE.Add(
114: X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_APPLY_FILTER_FAILED'),
115: X_Source => 'BSC_UPDATE_CALC.Apply_Filters');
116: RETURN FALSE;
117:
118: WHEN OTHERS THEN
127: | FUNCTION Calculate_Profit
128: +============================================================================*/
129: FUNCTION Calculate_Profit(
130: x_table_name IN VARCHAR2,
131: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
132: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
133: x_num_key_columns IN NUMBER,
134: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
135: x_num_data_columns IN NUMBER,
128: +============================================================================*/
129: FUNCTION Calculate_Profit(
130: x_table_name IN VARCHAR2,
131: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
132: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
133: x_num_key_columns IN NUMBER,
134: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
135: x_num_data_columns IN NUMBER,
136: x_aw_flag IN BOOLEAN,
130: x_table_name IN VARCHAR2,
131: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
132: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
133: x_num_key_columns IN NUMBER,
134: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
135: x_num_data_columns IN NUMBER,
136: x_aw_flag IN BOOLEAN,
137: x_change_vector_value IN NUMBER
138: ) RETURN BOOLEAN IS
167:
168: h_profit_account NUMBER;
169:
170: h_lst_keys_no_account VARCHAR2(32700);
171: h_arr_keys_no_account BSC_UPDATE_UTIL.t_array_of_varchar2;
172: h_num_keys_no_account NUMBER;
173:
174: h_lst_data_columns VARCHAR2(32700);
175: h_lst_sum_profit VARCHAR2(32700);
173:
174: h_lst_data_columns VARCHAR2(32700);
175: h_lst_sum_profit VARCHAR2(32700);
176:
177: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
178:
179: cursor c_dim_table_name(pLevelPkCol varchar2) is
180: SELECT level_view_name
181: FROM bsc_sys_dim_levels_b
313: l_bind_vars_values.delete;
314: h_sql := 'DELETE FROM '||x_table_name||
315: ' WHERE '||h_account_key||' = :1';
316: l_bind_vars_values(1) := h_profit_account ;
317: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
318: --Fix bug#4116490 Need commit
319: commit;
320:
321: -- Calculates the profit and insert it with the profit code
320:
321: -- Calculates the profit and insert it with the profit code
322:
323: -- Initialize a list with the data columns
324: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
325:
326: -- Get a list with the calculation of the profit for all data fields
327: FOR h_i IN 1 .. x_num_data_columns LOOP
328: IF h_lst_sum_profit IS NULL THEN
349: END LOOP;
350:
351: -- Built and execute the query to calculate the profit
352: h_sql := 'INSERT /*+ append ';
353: if BSC_UPDATE_UTIL.is_parallel then
354: h_sql:=h_sql||'parallel ('||x_table_name||') ';
355: end if;
356: h_sql:=h_sql||' */';
357: h_sql:=h_sql||'INTO '||x_table_name||
359: IF x_aw_flag THEN
360: h_sql:=h_sql||', PROJECTION, CHANGE_VECTOR';
361: END IF;
362: h_sql:=h_sql||') SELECT ';
363: if BSC_UPDATE_UTIL.is_parallel then
364: h_sql:=h_sql||'/*+ parallel ('||x_table_name||') parallel ('||h_account_dim_table||')*/ ';
365: end if;
366: h_sql:=h_sql||h_profit_account||', '||
367: BSC_UPDATE_UTIL.Make_Lst_Table_Column(x_table_name, h_arr_keys_no_account, h_num_keys_no_account)||', '||
363: if BSC_UPDATE_UTIL.is_parallel then
364: h_sql:=h_sql||'/*+ parallel ('||x_table_name||') parallel ('||h_account_dim_table||')*/ ';
365: end if;
366: h_sql:=h_sql||h_profit_account||', '||
367: BSC_UPDATE_UTIL.Make_Lst_Table_Column(x_table_name, h_arr_keys_no_account, h_num_keys_no_account)||', '||
368: h_lst_sum_profit;
369: IF x_aw_flag THEN
370: h_sql:=h_sql||', '||x_table_name||'.PROJECTION, '||x_change_vector_value;
371: END IF;
377: IF BSC_APPS.bsc_mv THEN
378: h_sql := h_sql||' AND '||h_account_dim_table||'.'||h_acc_key||' = '||h_acc_table||'.CODE';
379: END IF;
380: h_sql := h_sql||' GROUP BY '||
381: BSC_UPDATE_UTIL.Make_Lst_Table_Column(x_table_name, h_arr_keys_no_account, h_num_keys_no_account);
382: --Fix bug#4593671: add projection to the group by in aw architecture
383: IF x_aw_flag THEN
384: h_sql := h_sql||', '||x_table_name||'.PROJECTION';
385: END IF;
382: --Fix bug#4593671: add projection to the group by in aw architecture
383: IF x_aw_flag THEN
384: h_sql := h_sql||', '||x_table_name||'.PROJECTION';
385: END IF;
386: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
387: commit;
388: RETURN TRUE;
389:
390: EXCEPTION
389:
390: EXCEPTION
391: WHEN e_unexpected_error THEN
392: BSC_MESSAGE.Add(
393: X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROFIT_CALC_FAILED'),
394: X_Source => 'BSC_UPDATE_CALC.Calculate_Profit');
395: RETURN FALSE;
396:
397: WHEN OTHERS THEN
409: FUNCTION Calculate_Proj_Avg_Last_Year(
410: x_table_name IN VARCHAR2,
411: x_periodicity IN NUMBER,
412: x_period IN NUMBER,
413: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
414: x_num_key_columns IN NUMBER,
415: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
416: x_num_data_columns IN NUMBER,
417: x_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
411: x_periodicity IN NUMBER,
412: x_period IN NUMBER,
413: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
414: x_num_key_columns IN NUMBER,
415: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
416: x_num_data_columns IN NUMBER,
417: x_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
418: x_current_fy IN NUMBER,
419: x_num_of_years IN NUMBER,
445: h_yearly_flag NUMBER;
446:
447: h_uni_table VARCHAR2(32000);
448:
449: l_bind_vars_union BSC_UPDATE_UTIL.t_array_of_number;
450: l_num_bind_vars_union NUMBER;
451: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
452: l_num_bind_vars NUMBER;
453: l_bind_vars_post BSC_UPDATE_UTIL.t_array_of_number;
447: h_uni_table VARCHAR2(32000);
448:
449: l_bind_vars_union BSC_UPDATE_UTIL.t_array_of_number;
450: l_num_bind_vars_union NUMBER;
451: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
452: l_num_bind_vars NUMBER;
453: l_bind_vars_post BSC_UPDATE_UTIL.t_array_of_number;
454: l_num_bind_vars_post NUMBER;
455:
449: l_bind_vars_union BSC_UPDATE_UTIL.t_array_of_number;
450: l_num_bind_vars_union NUMBER;
451: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
452: l_num_bind_vars NUMBER;
453: l_bind_vars_post BSC_UPDATE_UTIL.t_array_of_number;
454: l_num_bind_vars_post NUMBER;
455:
456: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
457: h_lst_select VARCHAR2(32000);
452: l_num_bind_vars NUMBER;
453: l_bind_vars_post BSC_UPDATE_UTIL.t_array_of_number;
454: l_num_bind_vars_post NUMBER;
455:
456: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
457: h_lst_select VARCHAR2(32000);
458: h_mv_name VARCHAR2(30);
459: h_ref_table VARCHAR2(30);
460:
494: h_lst_keys_temp := h_lst_keys_temp||'KEY'||h_i||' '||x_key_columns(h_i)||', ';
495: h_lst_keys_tochar := h_lst_keys_tochar||'TO_CHAR('||x_key_columns(h_i)||') '||x_key_columns(h_i)||', ';
496: END LOOP;
497:
498: h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
499:
500: h_lst_keys := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
501: h_lst_keys_nc := h_lst_keys;
502: IF h_lst_keys IS NOT NULL THEN
496: END LOOP;
497:
498: h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
499:
500: h_lst_keys := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
501: h_lst_keys_nc := h_lst_keys;
502: IF h_lst_keys IS NOT NULL THEN
503: h_lst_keys := h_lst_keys||', ';
504: h_lst_select := h_lst_select||', ';
503: h_lst_keys := h_lst_keys||', ';
504: h_lst_select := h_lst_select||', ';
505: END IF;
506:
507: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
508: h_lst_xmed_columns := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('XMED', x_num_data_columns);
509:
510: FOR h_i IN 1..x_num_data_columns LOOP
511: IF h_i > 1 THEN
504: h_lst_select := h_lst_select||', ';
505: END IF;
506:
507: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
508: h_lst_xmed_columns := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('XMED', x_num_data_columns);
509:
510: FOR h_i IN 1..x_num_data_columns LOOP
511: IF h_i > 1 THEN
512: h_lst_avg_columns := h_lst_avg_columns||', ';
518: h_lst_xmed_columns_p := h_lst_xmed_columns_p||'P.XMED'||h_i;
519: h_lst_data_columns_temp := h_lst_data_columns_temp||'XMED'||h_i||' '||x_data_columns(h_i);
520: END LOOP;
521:
522: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
523:
524: --BSC-MV Note: In this architecture if the table is not a base table
525: -- the projected data is in the summary table and the other data including
526: -- targets are in the MV
577: h_num_periods := x_period + (x_num_of_years - x_previous_years) - 1;
578: h_num_previous_periods := x_previous_years;
579: ELSE
580: -- For other periodicities this method looks one year back
581: h_num_periods := BSC_UPDATE_UTIL.Get_Num_Periods_Periodicity(x_periodicity, x_current_fy);
582: IF h_num_periods IS NULL THEN
583: RAISE e_unexpected_error;
584: END IF;
585:
591: --Fix bug#3875046: We are going to insert all the records in bsc_tmp_xmd for all the periods
592: --and at the end we update the projection table BSC_TMP_PROJ_CALC only one time.
593: --For this reason I need to take out from the loop this truncate stmt
594: -- Delete all data from temporary table
595: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD');
596: commit;
597:
598: FOR h_i IN h_init_per .. h_num_periods LOOP
599: l_bind_vars_values.delete;
614:
615: -- Insert
616: l_bind_vars_values.delete ;
617: h_sql := 'INSERT /*+ append ';
618: if BSC_UPDATE_UTIL.is_parallel then
619: h_sql:=h_sql||'parallel (bsc_tmp_xmd) ';
620: end if;
621: h_sql:=h_sql||' */';
622: h_sql:=h_sql||'INTO bsc_tmp_xmd ('||h_lst_select||'YEAR, TYPE, PERIOD, ';
686: l_num_bind_vars := l_num_bind_vars+1;
687: l_bind_vars_values(l_num_bind_vars) := l_bind_vars_post(h_j);
688: END LOOP;
689:
690: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
691: commit;
692:
693: l_bind_vars_values.delete;
694:
700: -- Update the projection table table BSC_TMP_PROJ_CALC
701: -- Fix performance bug#3665014. Instead of update the base table with a complex query
702: -- we are going to insert the records in BSC_TMP_XMD_Y with row_id
703: -- and then update the base table.
704: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD_Y');
705: commit;
706:
707: h_sql := 'INSERT /*+ append ';
708: IF BSC_UPDATE_UTIL.is_parallel THEN
704: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD_Y');
705: commit;
706:
707: h_sql := 'INSERT /*+ append ';
708: IF BSC_UPDATE_UTIL.is_parallel THEN
709: h_sql := h_sql||'parallel (BSC_TMP_XMD_Y) ';
710: END IF;
711: h_sql := h_sql||' */';
712: h_sql := h_sql||' INTO BSC_TMP_XMD_Y (ROW_ID, '||h_lst_xmed_columns||')'||
714: ' FROM BSC_TMP_PROJ_CALC B, BSC_TMP_XMD P'||
715: ' WHERE ';
716: IF x_num_key_columns > 0 THEN
717: h_sql := h_sql||
718: BSC_UPDATE_UTIL.Make_Lst_Cond_Join('B',
719: h_key_columns_temp,
720: 'P',
721: h_key_columns_temp,
722: x_num_key_columns,
727: --AW_INTEGRATION: Base table does not have periodicity_id
728: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
729: h_sql := h_sql||' AND B.PERIODICITY_ID = P.PERIODICITY_ID';
730: END IF;
731: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
732: commit;
733:
734: -- Analyze the BSC_TMP_XMD_Y
735: -- Bug#3740230: We cannot analyze. It is causing Loader hangs truncating this table
746: h_sql := h_sql||
747: ' FROM BSC_TMP_XMD_Y P'||
748: ' WHERE P.ROW_ID = B.ROWID)'||
749: ' WHERE B.ROWID IN (SELECT ROW_ID FROM BSC_TMP_XMD_Y)';
750: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
751: commit;
752:
753: -- Fix bug#4463132: Truncate temporary table after use
754: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD');
750: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
751: commit;
752:
753: -- Fix bug#4463132: Truncate temporary table after use
754: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD');
755: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD_Y');
756: commit;
757:
758: RETURN TRUE;
751: commit;
752:
753: -- Fix bug#4463132: Truncate temporary table after use
754: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD');
755: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD_Y');
756: commit;
757:
758: RETURN TRUE;
759:
759:
760: EXCEPTION
761: WHEN e_unexpected_error THEN
762: BSC_MESSAGE.Add(
763: X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_ALY_FAILED'),
764: X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_Avg_Last_Year');
765: RETURN FALSE;
766:
767: WHEN OTHERS THEN
779: FUNCTION Calculate_Proj_3_Periods_Perf(
780: x_table_name IN VARCHAR2,
781: x_periodicity IN NUMBER,
782: x_period IN NUMBER,
783: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
784: x_num_key_columns IN NUMBER,
785: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
786: x_num_data_columns IN NUMBER,
787: x_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
781: x_periodicity IN NUMBER,
782: x_period IN NUMBER,
783: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
784: x_num_key_columns IN NUMBER,
785: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
786: x_num_data_columns IN NUMBER,
787: x_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
788: x_current_fy IN NUMBER,
789: x_is_base IN BOOLEAN,
808:
809: h_uni_table VARCHAR2(30);
810:
811: -- Posco bind var fix
812: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
813: l_num_bind_vars NUMBER;
814:
815: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
816: h_lst_select VARCHAR2(32700);
811: -- Posco bind var fix
812: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
813: l_num_bind_vars NUMBER;
814:
815: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
816: h_lst_select VARCHAR2(32700);
817: h_lst_totplan VARCHAR2(32700);
818: h_lst_totreal VARCHAR2(32700);
819: h_lst_plan VARCHAR2(32700);
862: FOR h_i IN 1..x_num_key_columns LOOP
863: h_key_columns_temp(h_i) := 'KEY'||h_i;
864: END LOOP;
865:
866: h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
867:
868: h_lst_keys := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
869: h_lst_keys_p := BSC_UPDATE_UTIL.Make_Lst_Table_Column('P', h_key_columns_temp, x_num_key_columns);
870: h_lst_groupby := h_lst_keys;
864: END LOOP;
865:
866: h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
867:
868: h_lst_keys := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
869: h_lst_keys_p := BSC_UPDATE_UTIL.Make_Lst_Table_Column('P', h_key_columns_temp, x_num_key_columns);
870: h_lst_groupby := h_lst_keys;
871:
872: IF h_lst_keys IS NOT NULL THEN
865:
866: h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
867:
868: h_lst_keys := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
869: h_lst_keys_p := BSC_UPDATE_UTIL.Make_Lst_Table_Column('P', h_key_columns_temp, x_num_key_columns);
870: h_lst_groupby := h_lst_keys;
871:
872: IF h_lst_keys IS NOT NULL THEN
873: h_lst_keys := h_lst_keys||', ';
874: h_lst_keys_p := h_lst_keys_p||', ';
875: h_lst_select := h_lst_select||', ';
876: END IF;
877:
878: h_lst_totplan := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('TOTPLAN', x_num_data_columns);
879: h_lst_totreal := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('TOTREAL', x_num_data_columns);
880: h_lst_plan := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('PLAN', x_num_data_columns);
881: h_lst_data := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);
882: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
875: h_lst_select := h_lst_select||', ';
876: END IF;
877:
878: h_lst_totplan := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('TOTPLAN', x_num_data_columns);
879: h_lst_totreal := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('TOTREAL', x_num_data_columns);
880: h_lst_plan := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('PLAN', x_num_data_columns);
881: h_lst_data := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);
882: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
883:
876: END IF;
877:
878: h_lst_totplan := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('TOTPLAN', x_num_data_columns);
879: h_lst_totreal := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('TOTREAL', x_num_data_columns);
880: h_lst_plan := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('PLAN', x_num_data_columns);
881: h_lst_data := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);
882: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
883:
884: FOR h_i IN 1..x_num_data_columns LOOP
877:
878: h_lst_totplan := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('TOTPLAN', x_num_data_columns);
879: h_lst_totreal := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('TOTREAL', x_num_data_columns);
880: h_lst_plan := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('PLAN', x_num_data_columns);
881: h_lst_data := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);
882: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
883:
884: FOR h_i IN 1..x_num_data_columns LOOP
885: IF h_i > 1 THEN
878: h_lst_totplan := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('TOTPLAN', x_num_data_columns);
879: h_lst_totreal := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('TOTREAL', x_num_data_columns);
880: h_lst_plan := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('PLAN', x_num_data_columns);
881: h_lst_data := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);
882: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
883:
884: FOR h_i IN 1..x_num_data_columns LOOP
885: IF h_i > 1 THEN
886: h_lst_sumdata := h_lst_sumdata||', ';
917: h_lst_data_p := h_lst_data_p||'P.DATA'||h_i;
918:
919: END LOOP;
920:
921: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
922: IF h_yearly_flag = 1 THEN -- Annual
923: h_per_end := x_current_fy;
924: h_per_ini := x_current_fy - h_num_per_back + 1;
925: h_num_pers := h_per_end - h_per_ini + 1;
936:
937: -- Make a temporal table with the total of plan data of the last n-periods,
938: -- where n is the value of the variable h_num_per_back
939: --h_sql := 'DELETE FROM BSC_TMP_TOT_PLAN';
940: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
941: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_PLAN');
942: commit;
943:
944: l_bind_vars_values.delete ;
937: -- Make a temporal table with the total of plan data of the last n-periods,
938: -- where n is the value of the variable h_num_per_back
939: --h_sql := 'DELETE FROM BSC_TMP_TOT_PLAN';
940: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
941: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_PLAN');
942: commit;
943:
944: l_bind_vars_values.delete ;
945: h_sql := 'INSERT /*+ append ';
942: commit;
943:
944: l_bind_vars_values.delete ;
945: h_sql := 'INSERT /*+ append ';
946: if BSC_UPDATE_UTIL.is_parallel then
947: h_sql:=h_sql||'parallel (bsc_tmp_tot_plan) ';
948: end if;
949: h_sql:=h_sql||' */';
950: h_sql:=h_sql||'INTO BSC_TMP_TOT_PLAN ('||h_lst_select||'TYPE, '||h_lst_totplan||')'||
948: end if;
949: h_sql:=h_sql||' */';
950: h_sql:=h_sql||'INTO BSC_TMP_TOT_PLAN ('||h_lst_select||'TYPE, '||h_lst_totplan||')'||
951: ' SELECT ';
952: if BSC_UPDATE_UTIL.is_parallel then
953: h_sql:=h_sql||'/*+ parallel ('||h_uni_table||')*/ ';
954: end if;
955: h_sql:=h_sql||h_lst_keys||'0, '||h_lst_sumdata||
956: ' FROM '||h_uni_table;
983: END IF;
984: IF h_lst_groupby IS NOT NULL THEN
985: h_sql := h_sql||' GROUP BY '||h_lst_groupby;
986: END IF;
987: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
988: commit;
989:
990: -- Make a temporal table with the total of real data of the last n-periods,
991: -- where n is the value of the variable h_num_per_back
989:
990: -- Make a temporal table with the total of real data of the last n-periods,
991: -- where n is the value of the variable h_num_per_back
992: --h_sql := 'DELETE FROM BSC_TMP_TOT_REAL';
993: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
994: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_REAL');
995: commit;
996:
997: l_bind_vars_values.delete ;
990: -- Make a temporal table with the total of real data of the last n-periods,
991: -- where n is the value of the variable h_num_per_back
992: --h_sql := 'DELETE FROM BSC_TMP_TOT_REAL';
993: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
994: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_REAL');
995: commit;
996:
997: l_bind_vars_values.delete ;
998: h_sql := 'INSERT /*+ append ';
995: commit;
996:
997: l_bind_vars_values.delete ;
998: h_sql := 'INSERT /*+ append ';
999: if BSC_UPDATE_UTIL.is_parallel then
1000: h_sql:=h_sql||'parallel (bsc_tmp_tot_real) ';
1001: end if;
1002: h_sql:=h_sql||' */';
1003: h_sql:=h_sql||'INTO BSC_TMP_TOT_REAL('||h_lst_select||'TYPE, '||h_lst_totreal||')'||
1001: end if;
1002: h_sql:=h_sql||' */';
1003: h_sql:=h_sql||'INTO BSC_TMP_TOT_REAL('||h_lst_select||'TYPE, '||h_lst_totreal||')'||
1004: ' SELECT ';
1005: if BSC_UPDATE_UTIL.is_parallel then
1006: h_sql:=h_sql||'/*+ parallel ('||h_uni_table||')*/ ';
1007: end if;
1008: h_sql:=h_sql||h_lst_keys||'0, '||h_lst_sumdata||
1009: ' FROM '||h_uni_table;
1036: END IF;
1037: IF h_lst_groupby IS NOT NULL THEN
1038: h_sql := h_sql||' GROUP BY '||h_lst_groupby;
1039: END IF;
1040: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars );
1041: commit;
1042:
1043: -- Make a temporal table with the plan of the projected periods
1044: --h_sql := 'DELETE FROM BSC_TMP_PLAN_PROJECTIONS';
1041: commit;
1042:
1043: -- Make a temporal table with the plan of the projected periods
1044: --h_sql := 'DELETE FROM BSC_TMP_PLAN_PROJECTIONS';
1045: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1046: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PLAN_PROJECTIONS');
1047: commit;
1048:
1049: l_bind_vars_values.delete;
1042:
1043: -- Make a temporal table with the plan of the projected periods
1044: --h_sql := 'DELETE FROM BSC_TMP_PLAN_PROJECTIONS';
1045: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1046: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PLAN_PROJECTIONS');
1047: commit;
1048:
1049: l_bind_vars_values.delete;
1050: h_sql := 'INSERT /*+ append ';
1047: commit;
1048:
1049: l_bind_vars_values.delete;
1050: h_sql := 'INSERT /*+ append ';
1051: if BSC_UPDATE_UTIL.is_parallel then
1052: h_sql:=h_sql||'parallel (BSC_TMP_PLAN_PROJECTIONS) ';
1053: end if;
1054: h_sql:=h_sql||' */';
1055: h_sql:=h_sql||'INTO BSC_TMP_PLAN_PROJECTIONS ('||h_lst_select||'YEAR, TYPE, PERIOD, '||h_lst_plan||')'||
1053: end if;
1054: h_sql:=h_sql||' */';
1055: h_sql:=h_sql||'INTO BSC_TMP_PLAN_PROJECTIONS ('||h_lst_select||'YEAR, TYPE, PERIOD, '||h_lst_plan||')'||
1056: ' SELECT ';
1057: if BSC_UPDATE_UTIL.is_parallel then
1058: h_sql:=h_sql||'/*+ parallel ('||h_uni_table||')*/ ';
1059: end if;
1060: h_sql:=h_sql||h_lst_keys||'YEAR, 0, PERIOD, '||h_lst_data_columns||
1061: ' FROM '||h_uni_table;
1082: h_sql := h_sql||' AND PERIODICITY_ID = :4';
1083: l_bind_vars_values(4) := (x_periodicity);
1084: l_num_bind_vars := 4;
1085: END IF;
1086: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
1087: commit;
1088:
1089: -- Calculate the projection in temporal table BSC_TMP_PROJECTIONS
1090: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS');
1086: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
1087: commit;
1088:
1089: -- Calculate the projection in temporal table BSC_TMP_PROJECTIONS
1090: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS');
1091: commit;
1092:
1093: l_bind_vars_values.delete;
1094: h_sql := 'INSERT /*+ append ';
1091: commit;
1092:
1093: l_bind_vars_values.delete;
1094: h_sql := 'INSERT /*+ append ';
1095: if BSC_UPDATE_UTIL.is_parallel then
1096: h_sql:=h_sql||'parallel (BSC_TMP_PROJECTIONS) ';
1097: end if;
1098: h_sql:=h_sql||' */';
1099: h_sql:=h_sql||'INTO BSC_TMP_PROJECTIONS ('||h_lst_select||'YEAR, TYPE, PERIOD, ';
1111: ' FROM BSC_TMP_PROJ_CALC P, BSC_TMP_TOT_PLAN TP, BSC_TMP_TOT_REAL TR, BSC_TMP_PLAN_PROJECTIONS PP'||
1112: ' WHERE ';
1113: IF x_num_key_columns > 0 THEN
1114: h_sql := h_sql||
1115: BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('P',
1116: h_key_columns_temp,
1117: 'TP',
1118: h_key_columns_temp,
1119: x_num_key_columns,
1122: END IF;
1123: h_sql := h_sql||'P.TYPE = TP.TYPE (+) AND ';
1124: IF x_num_key_columns > 0 THEN
1125: h_sql := h_sql||
1126: BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('P',
1127: h_key_columns_temp,
1128: 'TR',
1129: h_key_columns_temp,
1130: x_num_key_columns,
1133: END IF;
1134: h_sql := h_sql||'P.TYPE = TR.TYPE (+) AND ';
1135: IF x_num_key_columns > 0 THEN
1136: h_sql := h_sql||
1137: BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('P',
1138: h_key_columns_temp,
1139: 'PP',
1140: h_key_columns_temp,
1141: x_num_key_columns,
1162: h_sql := h_sql||' AND P.PERIODICITY_ID = :4';
1163: l_bind_vars_values(4) := (x_periodicity);
1164: l_num_bind_vars := 4;
1165: END IF;
1166: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
1167: commit;
1168:
1169: l_bind_vars_values.delete;
1170:
1172: -- Fix performance bug#3665014. Instead of update the base table with a complex query
1173: -- we are going to insert the records in BSC_TMP_PROJECTIONS_Y with row_id
1174: -- and then update the base table.
1175:
1176: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS_Y');
1177: commit;
1178:
1179: h_sql := 'INSERT /*+ append ';
1180: IF BSC_UPDATE_UTIL.is_parallel THEN
1176: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS_Y');
1177: commit;
1178:
1179: h_sql := 'INSERT /*+ append ';
1180: IF BSC_UPDATE_UTIL.is_parallel THEN
1181: h_sql := h_sql||'parallel (BSC_TMP_PROJECTIONS_Y) ';
1182: END IF;
1183: h_sql := h_sql||' */';
1184: h_sql := h_sql||' INTO BSC_TMP_PROJECTIONS_Y (ROW_ID, '||h_lst_data||')'||
1186: ' FROM BSC_TMP_PROJ_CALC B, BSC_TMP_PROJECTIONS P'||
1187: ' WHERE ';
1188: IF x_num_key_columns > 0 THEN
1189: h_sql := h_sql||
1190: BSC_UPDATE_UTIL.Make_Lst_Cond_Join('B',
1191: h_key_columns_temp,
1192: 'P',
1193: h_key_columns_temp,
1194: x_num_key_columns,
1199: -- AW_INTEGRATION: Base table does not have periodicity_id
1200: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1201: h_sql := h_sql||' AND B.PERIODICITY_ID = P.PERIODICITY_ID';
1202: END IF;
1203: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1204: commit;
1205:
1206: -- Analyze the BSC_TMP_PROJECTIONS_Y
1207: -- Bug#3740230: We cannot analyze. It is causing Loader hangs truncating this table
1218: h_sql := h_sql||
1219: ' FROM BSC_TMP_PROJECTIONS_Y P'||
1220: ' WHERE P.ROW_ID = B.ROWID)'||
1221: ' WHERE B.ROWID IN (SELECT ROW_ID FROM BSC_TMP_PROJECTIONS_Y)';
1222: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1223: commit;
1224:
1225: -- Fix bug#4463132: Truncate temporary table after use
1226: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_PLAN');
1222: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1223: commit;
1224:
1225: -- Fix bug#4463132: Truncate temporary table after use
1226: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_PLAN');
1227: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_REAL');
1228: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PLAN_PROJECTIONS');
1229: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS');
1230: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS_Y');
1223: commit;
1224:
1225: -- Fix bug#4463132: Truncate temporary table after use
1226: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_PLAN');
1227: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_REAL');
1228: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PLAN_PROJECTIONS');
1229: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS');
1230: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS_Y');
1231: commit;
1224:
1225: -- Fix bug#4463132: Truncate temporary table after use
1226: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_PLAN');
1227: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_REAL');
1228: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PLAN_PROJECTIONS');
1229: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS');
1230: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS_Y');
1231: commit;
1232:
1225: -- Fix bug#4463132: Truncate temporary table after use
1226: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_PLAN');
1227: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_REAL');
1228: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PLAN_PROJECTIONS');
1229: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS');
1230: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS_Y');
1231: commit;
1232:
1233: RETURN TRUE;
1226: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_PLAN');
1227: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_REAL');
1228: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PLAN_PROJECTIONS');
1229: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS');
1230: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS_Y');
1231: commit;
1232:
1233: RETURN TRUE;
1234:
1234:
1235: EXCEPTION
1236: WHEN e_unexpected_error THEN
1237: BSC_MESSAGE.Add(
1238: X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_THREEMONTH_FAILED'),
1239: X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_3_Periods_Perf');
1240: RETURN FALSE;
1241:
1242: WHEN OTHERS THEN
1254: FUNCTION Calculate_Proj_User_Defined(
1255: x_table_name IN VARCHAR2,
1256: x_periodicity IN NUMBER,
1257: x_period IN NUMBER,
1258: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1259: x_num_key_columns IN NUMBER,
1260: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1261: x_num_data_columns IN NUMBER,
1262: x_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
1256: x_periodicity IN NUMBER,
1257: x_period IN NUMBER,
1258: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1259: x_num_key_columns IN NUMBER,
1260: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1261: x_num_data_columns IN NUMBER,
1262: x_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
1263: x_current_fy IN NUMBER,
1264: x_is_base IN BOOLEAN,
1272: h_yearly_flag NUMBER;
1273:
1274: h_src_table VARCHAR2(30);
1275:
1276: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
1277: l_num_bind_vars NUMBER;
1278:
1279: h_lst_data_columns VARCHAR2(32700);
1280:
1277: l_num_bind_vars NUMBER;
1278:
1279: h_lst_data_columns VARCHAR2(32700);
1280:
1281: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
1282: h_i NUMBER;
1283:
1284: BEGIN
1285: h_yearly_flag := 0;
1285: h_yearly_flag := 0;
1286: l_num_bind_vars := 0;
1287: h_lst_data_columns := NULL;
1288:
1289: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
1290:
1291: FOR h_i IN 1..x_num_key_columns LOOP
1292: h_key_columns_temp(h_i) := 'KEY'||h_i;
1293: END LOOP;
1312: ' FROM '||h_src_table||' B'||
1313: ' WHERE ';
1314: IF x_num_key_columns > 0 THEN
1315: h_sql := h_sql||
1316: BSC_UPDATE_UTIL.Make_Lst_Cond_Join('T',
1317: h_key_columns_temp,
1318: 'B',
1319: x_key_columns,
1320: x_num_key_columns,
1328: h_sql := h_sql||'T.YEAR = B.YEAR'||
1329: ' AND T.PERIOD = B.PERIOD'||
1330: ' AND B.TYPE = 90'||
1331: ')';
1332: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
1333: IF h_yearly_flag = 1 THEN -- Annual
1334: h_sql := h_sql||' WHERE T.YEAR > :1'||
1335: ' AND T.TYPE = :2'||
1336: ' AND T.PERIOD = :3';
1353: l_bind_vars_values(4) := x_periodicity;
1354: l_num_bind_vars := 4;
1355: END IF;
1356:
1357: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
1358: commit;
1359:
1360: RETURN TRUE;
1361:
1361:
1362: EXCEPTION
1363: WHEN e_unexpected_error THEN
1364: BSC_MESSAGE.Add(
1365: X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_UD_FAILED'),
1366: X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_User_Defined');
1367: RETURN FALSE;
1368:
1369: WHEN OTHERS THEN
1381: FUNCTION Calculate_Projection(
1382: x_table_name IN VARCHAR2,
1383: x_periodicity IN NUMBER,
1384: x_period IN NUMBER,
1385: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1386: x_num_key_columns IN NUMBER,
1387: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1388: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
1389: x_num_data_columns IN NUMBER,
1383: x_periodicity IN NUMBER,
1384: x_period IN NUMBER,
1385: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1386: x_num_key_columns IN NUMBER,
1387: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1388: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
1389: x_num_data_columns IN NUMBER,
1390: x_current_fy IN NUMBER,
1391: x_num_of_years IN NUMBER,
1384: x_period IN NUMBER,
1385: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1386: x_num_key_columns IN NUMBER,
1387: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1388: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
1389: x_num_data_columns IN NUMBER,
1390: x_current_fy IN NUMBER,
1391: x_num_of_years IN NUMBER,
1392: x_previous_years IN NUMBER,
1404: h_lst_data_columns VARCHAR2(32000);
1405: h_mv_name VARCHAR2(30);
1406: h_yearly_flag NUMBER;
1407:
1408: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
1409: h_num_bind_vars NUMBER;
1410:
1411: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
1412: h_num_data_columns NUMBER;
1407:
1408: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
1409: h_num_bind_vars NUMBER;
1410:
1411: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
1412: h_num_data_columns NUMBER;
1413:
1414: h_lst_data_temp VARCHAR2(32000);
1415:
1572: RAISE e_unexpected_error;
1573: END IF;
1574:
1575: -- Fix bug#4463132: Truncate temporary table after use
1576: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJ_CALC');
1577: commit;
1578: END IF;
1579:
1580: --BSC_UPDATE_LOG.Write_Line_Log('End Calculating projection in '||x_table_name, BSC_UPDATE_LOG.LOG);
1586: EXCEPTION
1587: WHEN e_unexpected_error THEN
1588: ROLLBACK;
1589: BSC_MESSAGE.Add(
1590: X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_FAILED'),
1591: X_Source => 'BSC_UPDATE_CALC.Calculate_Projection');
1592: RETURN FALSE;
1593:
1594: WHEN OTHERS THEN
1606: +============================================================================*/
1607: FUNCTION Calculate_Zero_Code(
1608: x_table_name IN VARCHAR2,
1609: x_zero_code_calc_method IN NUMBER,
1610: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1611: x_num_key_columns IN NUMBER,
1612: x_src_table IN VARCHAR2
1613: ) RETURN BOOLEAN IS
1614:
1683: WHERE table_name = p_sum_table;
1684:
1685: h_ref_table VARCHAR2(30);
1686:
1687: h_zero_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
1688: h_num_zero_key_columns NUMBER;
1689:
1690: h_column_type VARCHAR2(10);
1691:
1746: LOOP
1747: FETCH c_keys_needing_zero_code INTO h_key;
1748: EXIT WHEN c_keys_needing_zero_code%NOTFOUND;
1749:
1750: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_key, x_key_columns, x_num_key_columns) THEN
1751: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_key, h_zero_key_columns, h_num_zero_key_columns) THEN
1752: h_num_zero_key_columns := h_num_zero_key_columns + 1;
1753: h_zero_key_columns(h_num_zero_key_columns) := h_key;
1754: END IF;
1747: FETCH c_keys_needing_zero_code INTO h_key;
1748: EXIT WHEN c_keys_needing_zero_code%NOTFOUND;
1749:
1750: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_key, x_key_columns, x_num_key_columns) THEN
1751: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_key, h_zero_key_columns, h_num_zero_key_columns) THEN
1752: h_num_zero_key_columns := h_num_zero_key_columns + 1;
1753: h_zero_key_columns(h_num_zero_key_columns) := h_key;
1754: END IF;
1755: END IF;
1777: END LOOP;
1778:
1779: h_sql := 'DELETE FROM '||h_ref_table||
1780: ' WHERE '||h_lst_where;
1781: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1782: -- Fix bug#4116490 need commit
1783: commit;
1784:
1785: -- Data columns
1855: END IF;
1856:
1857: -- Insert the zero code for the key column, h_zero_key_columns(h_j)
1858: h_sql := 'INSERT /*+ append ';
1859: if BSC_UPDATE_UTIL.is_parallel then
1860: h_sql:=h_sql||'parallel ('||h_ref_table||') ';
1861: end if;
1862: h_sql:=h_sql||' */';
1863: h_sql:=h_sql||'INTO '||h_ref_table||
1862: h_sql:=h_sql||' */';
1863: h_sql:=h_sql||'INTO '||h_ref_table||
1864: ' ('||h_lst_keys||', '||h_lst_data_columns||')'||
1865: ' SELECT ';
1866: if BSC_UPDATE_UTIL.is_parallel then
1867: h_sql:=h_sql||'/*+ parallel ('||h_ref_table||')*/ ';
1868: end if;
1869: h_sql:=h_sql||h_lst_select||', '||h_lst_expressions||
1870: ' FROM '||h_ref_table;
1868: end if;
1869: h_sql:=h_sql||h_lst_select||', '||h_lst_expressions||
1870: ' FROM '||h_ref_table;
1871: h_sql := h_sql||' GROUP BY '||h_lst_groupby;
1872: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1873: commit;
1874: END LOOP;
1875: COMMIT;
1876:
1879: EXCEPTION
1880: WHEN e_unexpected_error THEN
1881: ROLLBACK;
1882: BSC_MESSAGE.Add(
1883: X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_ZEROCODE_CALC_FAILED'),
1884: X_Source => 'BSC_UPDATE_CALC.Calculate_Zero_Code');
1885: RETURN FALSE;
1886:
1887: WHEN OTHERS THEN
1918: h_calendar_id NUMBER;
1919: h_yearly_flag NUMBER;
1920: h_edw_flag NUMBER;
1921:
1922: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
1923:
1924: BEGIN
1925:
1926: h_yearly_flag := 0;
1925:
1926: h_yearly_flag := 0;
1927: h_edw_flag := 0;
1928:
1929: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
1930: h_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_periodicity);
1931: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
1932:
1933: -- BSC_TMP_ALL_PERIODS
1926: h_yearly_flag := 0;
1927: h_edw_flag := 0;
1928:
1929: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
1930: h_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_periodicity);
1931: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
1932:
1933: -- BSC_TMP_ALL_PERIODS
1934: --h_sql := 'DELETE FROM BSC_TMP_ALL_PERIODS';
1927: h_edw_flag := 0;
1928:
1929: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
1930: h_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_periodicity);
1931: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
1932:
1933: -- BSC_TMP_ALL_PERIODS
1934: --h_sql := 'DELETE FROM BSC_TMP_ALL_PERIODS';
1935: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1931: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
1932:
1933: -- BSC_TMP_ALL_PERIODS
1934: --h_sql := 'DELETE FROM BSC_TMP_ALL_PERIODS';
1935: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1936: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_ALL_PERIODS');
1937: commit;
1938:
1939: IF h_yearly_flag = 1 THEN -- Annual
1932:
1933: -- BSC_TMP_ALL_PERIODS
1934: --h_sql := 'DELETE FROM BSC_TMP_ALL_PERIODS';
1935: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1936: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_ALL_PERIODS');
1937: commit;
1938:
1939: IF h_yearly_flag = 1 THEN -- Annual
1940: h_init_period := x_current_fy - x_previous_years;
1944: l_bind_vars_values.delete ;
1945: h_sql := 'INSERT INTO BSC_TMP_ALL_PERIODS (PERIOD)'||
1946: ' VALUES (:1)';
1947: l_bind_vars_values(1) := (h_i);
1948: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
1949: END LOOP;
1950: ELSE
1951: -- Periodicity different to Annual
1952: IF h_edw_flag = 0 THEN
1950: ELSE
1951: -- Periodicity different to Annual
1952: IF h_edw_flag = 0 THEN
1953: -- BSC periodicity
1954: h_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
1955:
1956: l_bind_vars_values.delete ;
1957: h_sql := 'INSERT INTO BSC_TMP_ALL_PERIODS'||
1958: ' SELECT DISTINCT '||h_calendar_col_name||
1960: ' WHERE YEAR = :1'||' AND CALENDAR_ID = :2'||
1961: ' GROUP BY '||h_calendar_col_name;
1962: l_bind_vars_values(1) := (x_current_fy);
1963: l_bind_vars_values(2) := (h_calendar_id) ;
1964: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1965: ELSE
1966: -- EDW periodicity
1967: h_init_period := 1;
1968: h_end_period := BSC_INTEGRATION_APIS.Get_Number_Of_Periods(x_current_fy, x_periodicity, h_calendar_id);
1974: l_bind_vars_values.delete ;
1975: h_sql := 'INSERT INTO BSC_TMP_ALL_PERIODS (PERIOD)'||
1976: ' VALUES (:1)';
1977: l_bind_vars_values(1) := (h_i) ;
1978: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
1979: END LOOP;
1980: END IF;
1981: END IF;
1982:
1981: END IF;
1982:
1983: --ENH_PROJECTION_4235711: truncate projectio table
1984: IF x_trunc_proj_table THEN
1985: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJ_CALC');
1986: END IF;
1987:
1988: RETURN TRUE;
1989:
1989:
1990: EXCEPTION
1991: WHEN e_unexpected_error THEN
1992: BSC_MESSAGE.Add(
1993: X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_TTABLES_FAILED'),
1994: X_Source => 'BSC_UPDATE_CALC.Create_Proj_Temps');
1995: RETURN FALSE;
1996:
1997: WHEN OTHERS THEN
2008: +============================================================================*/
2009: FUNCTION Init_Projection_Table(
2010: x_table_name IN VARCHAR2,
2011: x_periodicity IN NUMBER,
2012: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2013: x_num_key_columns IN NUMBER,
2014: x_current_fy IN NUMBER,
2015: x_current_period IN NUMBER,
2016: x_is_base IN BOOLEAN,
2029: h_lst_keys_nc VARCHAR2(32700);
2030:
2031: h_yearly_flag NUMBER;
2032:
2033: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2034: l_num_bind_vars NUMBER;
2035:
2036: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
2037: h_lst_select VARCHAR2(32000);
2032:
2033: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2034: l_num_bind_vars NUMBER;
2035:
2036: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
2037: h_lst_select VARCHAR2(32000);
2038:
2039: h_old_current_period NUMBER;
2040:
2062: -- New current period, then we need to recalculate projection for all
2063: -- dimension combinations existing in the base table
2064: h_ref_table := x_table_name;
2065:
2066: h_lst_table_keys := BSC_UPDATE_UTIL.Make_Lst_Table_Column(h_ref_table,
2067: x_key_columns,
2068: x_num_key_columns);
2069:
2070: h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
2066: h_lst_table_keys := BSC_UPDATE_UTIL.Make_Lst_Table_Column(h_ref_table,
2067: x_key_columns,
2068: x_num_key_columns);
2069:
2070: h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
2071: x_key_columns,
2072: x_num_key_columns);
2073:
2074: h_lst_keys_nc := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
2070: h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
2071: x_key_columns,
2072: x_num_key_columns);
2073:
2074: h_lst_keys_nc := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
2075: ELSE
2076: -- There is no change in the current period, then we just need to
2077: -- recalculate projection for dimension combinations coming from
2078: -- the input table
2077: -- recalculate projection for dimension combinations coming from
2078: -- the input table
2079: h_ref_table := 'BSC_TMP_BASE';
2080:
2081: h_lst_table_keys := BSC_UPDATE_UTIL.Make_Lst_Table_Column(h_ref_table,
2082: h_key_columns_temp,
2083: x_num_key_columns);
2084:
2085: h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
2081: h_lst_table_keys := BSC_UPDATE_UTIL.Make_Lst_Table_Column(h_ref_table,
2082: h_key_columns_temp,
2083: x_num_key_columns);
2084:
2085: h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
2086: h_key_columns_temp,
2087: x_num_key_columns);
2088:
2089: h_lst_keys_nc := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
2085: h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
2086: h_key_columns_temp,
2087: x_num_key_columns);
2088:
2089: h_lst_keys_nc := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
2090: END IF;
2091: ELSE
2092: --AW_INTEGRATION: In this architecture we do not calculate projections in PT tables
2093: -- only on base tables. So we know that the code does not get here. No changes here.
2096: ELSE
2097: h_ref_table := x_table_name;
2098: END IF;
2099:
2100: h_lst_table_keys := BSC_UPDATE_UTIL.Make_Lst_Table_Column(h_ref_table,
2101: x_key_columns,
2102: x_num_key_columns);
2103:
2104: h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
2100: h_lst_table_keys := BSC_UPDATE_UTIL.Make_Lst_Table_Column(h_ref_table,
2101: x_key_columns,
2102: x_num_key_columns);
2103:
2104: h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
2105: x_key_columns,
2106: x_num_key_columns);
2107:
2108: h_lst_keys_nc := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
2104: h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
2105: x_key_columns,
2106: x_num_key_columns);
2107:
2108: h_lst_keys_nc := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
2109: END IF;
2110:
2111: h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
2112:
2107:
2108: h_lst_keys_nc := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
2109: END IF;
2110:
2111: h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
2112:
2113: IF h_lst_table_keys IS NOT NULL THEN
2114: h_lst_table_keys := h_lst_table_keys||', ';
2115: h_lst_table_keys_a := h_lst_table_keys_a||', ';
2137: END IF;
2138:
2139: -- BSC-MV Note: Add condition on periodicity_id for new architecture
2140: -- Insert in the projection table BSC_TMP_PROJ_CALC the rows for the projected periods.
2141: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2142: l_bind_vars_values.delete ;
2143: IF h_yearly_flag = 1 THEN -- Annual
2144: h_sql := 'INSERT /*+ append ';
2145: IF BSC_UPDATE_UTIL.is_parallel THEN
2141: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2142: l_bind_vars_values.delete ;
2143: IF h_yearly_flag = 1 THEN -- Annual
2144: h_sql := 'INSERT /*+ append ';
2145: IF BSC_UPDATE_UTIL.is_parallel THEN
2146: h_sql := h_sql||'parallel (BSC_TMP_PROJ_CALC) ';
2147: END IF;
2148: h_sql := h_sql||' */'||
2149: ' INTO BSC_TMP_PROJ_CALC ('||h_lst_select||'YEAR, TYPE, PERIOD';
2180: l_num_bind_vars := 1;
2181: END IF;
2182: ELSE
2183: h_sql := 'INSERT /*+ append ';
2184: IF BSC_UPDATE_UTIL.is_parallel THEN
2185: h_sql := h_sql||'parallel (BSC_TMP_PROJ_CALC) ';
2186: END IF;
2187: h_sql := h_sql||' */'||
2188: ' INTO BSC_TMP_PROJ_CALC ('||h_lst_select||'YEAR, TYPE, PERIOD';
2221: l_num_bind_vars := 2;
2222: END IF;
2223: END IF;
2224: commit;
2225: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
2226: commit;
2227:
2228: RETURN TRUE;
2229:
2243: FUNCTION Delete_Projection(
2244: x_table_name IN VARCHAR2,
2245: x_periodicity IN NUMBER,
2246: x_period IN NUMBER,
2247: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2248: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
2249: x_num_data_columns IN NUMBER,
2250: x_current_fy IN NUMBER,
2251: x_is_base IN BOOLEAN
2244: x_table_name IN VARCHAR2,
2245: x_periodicity IN NUMBER,
2246: x_period IN NUMBER,
2247: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2248: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
2249: x_num_data_columns IN NUMBER,
2250: x_current_fy IN NUMBER,
2251: x_is_base IN BOOLEAN
2252: ) RETURN BOOLEAN IS
2257: h_lst_set VARCHAR2(32700);
2258:
2259: h_yearly_flag NUMBER;
2260:
2261: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2262: l_num_bind_vars NUMBER;
2263:
2264: BEGIN
2265: h_lst_set := NULL;
2279: END IF;
2280: END LOOP;
2281:
2282: IF h_lst_set IS NOT NULL THEN
2283: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2284: l_bind_vars_values.delete ;
2285: IF h_yearly_flag = 1 THEN
2286: h_sql := 'UPDATE '||x_table_name||
2287: ' SET '||h_lst_set||
2312: l_num_bind_vars := 4;
2313: END IF;
2314: END IF;
2315:
2316: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
2317: END IF;
2318:
2319: RETURN TRUE;
2320:
2335: x_table_name IN VARCHAR2,
2336: x_periodicity IN NUMBER,
2337: x_current_period IN NUMBER,
2338: x_new_current_period IN NUMBER,
2339: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2340: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
2341: x_num_data_columns IN NUMBER,
2342: x_current_fy IN NUMBER,
2343: x_aw_flag IN BOOLEAN,
2336: x_periodicity IN NUMBER,
2337: x_current_period IN NUMBER,
2338: x_new_current_period IN NUMBER,
2339: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2340: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
2341: x_num_data_columns IN NUMBER,
2342: x_current_fy IN NUMBER,
2343: x_aw_flag IN BOOLEAN,
2344: x_change_vector_value IN NUMBER
2351: h_lst_where VARCHAR2(32700);
2352:
2353: h_yearly_flag NUMBER;
2354:
2355: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2356: l_num_bind_vars NUMBER;
2357:
2358: BEGIN
2359: h_lst_set := NULL;
2360: h_lst_where := NULL;
2361: h_yearly_flag := 0;
2362: l_num_bind_vars := 0;
2363:
2364: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2365:
2366: --ENH_PROJECTION_4235711: Do this only if new current period > current period
2367: IF x_new_current_period > x_current_period THEN
2368: FOR h_i IN 1 .. x_num_data_columns LOOP
2411: -- columns are already null, since we have to update the projection flag to N
2412: IF NOT x_aw_flag THEN
2413: h_sql := h_sql||' AND ('||h_lst_where||')';
2414: END IF;
2415: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
2416: END IF;
2417: END IF;
2418: END IF;
2419:
2473: l_num_bind_vars := 4;
2474: END IF;
2475: END IF;
2476: h_sql := h_sql||' AND ('||h_lst_where||')';
2477: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
2478: END IF;
2479:
2480: RETURN TRUE;
2481:
2570: | FUNCTION Merge_Data_From_Tables
2571: +============================================================================*/
2572: FUNCTION Merge_Data_From_Tables(
2573: x_table_name IN VARCHAR2,
2574: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2575: x_num_key_columns IN NUMBER
2576: ) RETURN BOOLEAN IS
2577:
2578: TYPE t_cursor IS REF CURSOR;
2613: h_lst_data_columns := NULL;
2614: h_lst_key_columns := NULL;
2615:
2616: -- Get the list of key columns
2617: h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
2618: IF h_lst_key_columns IS NOT NULL THEN
2619: h_lst_key_columns := h_lst_key_columns||', ';
2620: END IF;
2621:
2644: ' FROM '||h_source_table||' S'||
2645: ' WHERE ';
2646: IF x_num_key_columns > 0 THEN
2647: h_sql := h_sql||
2648: BSC_UPDATE_UTIL.Make_Lst_Cond_Join('T', x_key_columns,
2649: 'S', x_key_columns,
2650: x_num_key_columns, 'AND')||
2651: ' AND ';
2652: END IF;
2656: ' WHERE ('||h_lst_key_columns||'YEAR, TYPE, PERIOD) IN ('||
2657: ' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD'||
2658: ' FROM '||h_source_table||
2659: ' )';
2660: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2661: --Fix bug#4116490 need commit
2662: commit;
2663:
2664: -- Insert new rows
2662: commit;
2663:
2664: -- Insert new rows
2665: h_sql := 'INSERT /*+ append ';
2666: if BSC_UPDATE_UTIL.is_parallel then
2667: h_sql:=h_sql||'parallel ('||x_table_name||') ';
2668: end if;
2669: h_sql:=h_sql||' */';
2670: h_sql:=h_sql||'INTO '||x_table_name||
2669: h_sql:=h_sql||' */';
2670: h_sql:=h_sql||'INTO '||x_table_name||
2671: ' ('||h_lst_key_columns||'YEAR, TYPE, PERIOD,'||h_lst_data_columns||')'||
2672: ' SELECT ';
2673: if BSC_UPDATE_UTIL.is_parallel then
2674: h_sql:=h_sql||'/*+ parallel ('||h_source_table||')*/ ';
2675: end if;
2676: h_sql:=h_sql||h_lst_key_columns||'YEAR, TYPE, PERIOD,'||h_lst_data_columns||
2677: ' FROM '||h_source_table||
2678: ' WHERE ('||h_lst_key_columns||'YEAR, TYPE, PERIOD) NOT IN ('||
2679: ' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD'||
2680: ' FROM '||x_table_name||
2681: ' )';
2682: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2683: commit;
2684:
2685: FETCH c_source_tables INTO h_source_table;
2686: END LOOP;
2703: | FUNCTION Merge_Projection |
2704: +============================================================================*/
2705: FUNCTION Merge_Projection(
2706: x_table_name VARCHAR2,
2707: x_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
2708: x_num_key_columns NUMBER,
2709: x_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
2710: x_num_data_columns NUMBER,
2711: x_is_base BOOLEAN,
2705: FUNCTION Merge_Projection(
2706: x_table_name VARCHAR2,
2707: x_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
2708: x_num_key_columns NUMBER,
2709: x_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
2710: x_num_data_columns NUMBER,
2711: x_is_base BOOLEAN,
2712: x_aw_flag BOOLEAN
2713: ) RETURN BOOLEAN IS
2714:
2715: h_sql VARCHAR2(32700);
2716: h_i NUMBER;
2717:
2718: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
2719: h_lst_keys_temp VARCHAR2(32000);
2720: h_cond_join VARCHAR2(32000);
2721: h_lst_keys_a VARCHAR2(32000);
2722: h_lst_keys_b VARCHAR2(32000);
2738: FOR h_i IN 1..x_num_key_columns LOOP
2739: h_key_columns_temp(h_i) := 'KEY'||h_i;
2740: END LOOP;
2741:
2742: h_lst_keys_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
2743: h_cond_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Join('A', x_key_columns,
2744: 'B', h_key_columns_temp,
2745: x_num_key_columns, 'AND');
2746: h_lst_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A', x_key_columns, x_num_key_columns);
2739: h_key_columns_temp(h_i) := 'KEY'||h_i;
2740: END LOOP;
2741:
2742: h_lst_keys_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
2743: h_cond_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Join('A', x_key_columns,
2744: 'B', h_key_columns_temp,
2745: x_num_key_columns, 'AND');
2746: h_lst_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A', x_key_columns, x_num_key_columns);
2747: h_lst_keys_b := BSC_UPDATE_UTIL.Make_Lst_Table_Column('B', h_key_columns_temp, x_num_key_columns);
2742: h_lst_keys_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
2743: h_cond_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Join('A', x_key_columns,
2744: 'B', h_key_columns_temp,
2745: x_num_key_columns, 'AND');
2746: h_lst_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A', x_key_columns, x_num_key_columns);
2747: h_lst_keys_b := BSC_UPDATE_UTIL.Make_Lst_Table_Column('B', h_key_columns_temp, x_num_key_columns);
2748:
2749: IF h_lst_keys_temp IS NOT NULL THEN
2750: h_lst_keys_temp := h_lst_keys_temp||', ';
2743: h_cond_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Join('A', x_key_columns,
2744: 'B', h_key_columns_temp,
2745: x_num_key_columns, 'AND');
2746: h_lst_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A', x_key_columns, x_num_key_columns);
2747: h_lst_keys_b := BSC_UPDATE_UTIL.Make_Lst_Table_Column('B', h_key_columns_temp, x_num_key_columns);
2748:
2749: IF h_lst_keys_temp IS NOT NULL THEN
2750: h_lst_keys_temp := h_lst_keys_temp||', ';
2751: h_cond_join := h_cond_join||' AND ';
2770: h_cond_join := h_cond_join||'A.YEAR = B.YEAR AND A.TYPE = B.TYPE AND A.PERIOD = B.PERIOD';
2771: h_lst_keys_a := h_lst_keys_a||'A.YEAR, A.TYPE, A.PERIOD';
2772: h_lst_keys_b := h_lst_keys_b||'B.YEAR, B.TYPE, B.PERIOD';
2773:
2774: h_lst_data_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);
2775:
2776: FOR h_i IN 1..x_num_data_columns LOOP
2777: IF h_i = 1 THEN
2778: h_lst_set_data := 'A.'||x_data_columns(h_i)||' = B.DATA'||h_i;
2798: ' WHEN MATCHED THEN UPDATE SET '||h_lst_set_data||
2799: ' WHEN NOT MATCHED THEN'||
2800: ' INSERT ('||h_lst_keys_a||', '||h_lst_data_a||')'||
2801: ' VALUES ('||h_lst_keys_b||', '||h_lst_data_b||')';
2802: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2803: commit;
2804:
2805: RETURN TRUE;
2806:
2819: | FUNCTION Refresh_EDW_Views
2820: +============================================================================*/
2821: FUNCTION Refresh_EDW_Views(
2822: x_table_name IN VARCHAR2,
2823: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2 ,
2824: x_num_key_columns IN NUMBER ,
2825: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2826: x_num_data_columns IN NUMBER,
2827: x_current_fy IN NUMBER,
2821: FUNCTION Refresh_EDW_Views(
2822: x_table_name IN VARCHAR2,
2823: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2 ,
2824: x_num_key_columns IN NUMBER ,
2825: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2826: x_num_data_columns IN NUMBER,
2827: x_current_fy IN NUMBER,
2828: x_periodicity IN NUMBER,
2829: x_current_period OUT NOCOPY NUMBER
2854: h_yearly_flag := 0;
2855:
2856: -- Refresh materialized view.
2857: -- Get the number of years and previous years of the table
2858: IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_table_name, h_num_of_years, h_previous_years) THEN
2859: RAISE e_unexpected_error;
2860: END IF;
2861:
2862: h_start_year := x_current_fy - h_previous_years;
2867: RAISE e_unexpected_error;
2868: END IF;
2869:
2870: -- Delete from x_table all rows existing in the materialized view
2871: h_edw_mv_name := BSC_UPDATE_UTIL.Get_EDW_Materialized_View_Name(x_table_name);
2872:
2873: h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
2874: IF h_lst_key_columns IS NOT NULL THEN
2875: h_lst_key_columns := h_lst_key_columns||', ';
2869:
2870: -- Delete from x_table all rows existing in the materialized view
2871: h_edw_mv_name := BSC_UPDATE_UTIL.Get_EDW_Materialized_View_Name(x_table_name);
2872:
2873: h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
2874: IF h_lst_key_columns IS NOT NULL THEN
2875: h_lst_key_columns := h_lst_key_columns||', ';
2876: END IF;
2877:
2878: h_sql := 'DELETE FROM '||x_table_name||
2879: ' WHERE ('||h_lst_key_columns||'YEAR, TYPE, PERIOD) IN ('||
2880: ' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD '||
2881: ' FROM '||h_edw_mv_name||')';
2882: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2883:
2884: -- Create union view
2885: h_edw_uv_name := BSC_UPDATE_UTIL.Get_EDW_Union_View_Name(x_table_name);
2886: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
2881: ' FROM '||h_edw_mv_name||')';
2882: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2883:
2884: -- Create union view
2885: h_edw_uv_name := BSC_UPDATE_UTIL.Get_EDW_Union_View_Name(x_table_name);
2886: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
2887: h_sql := 'CREATE OR REPLACE VIEW '||h_edw_uv_name||' AS ('||
2888: ' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD, '||h_lst_data_columns||
2889: ' FROM '||x_table_name||
2882: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2883:
2884: -- Create union view
2885: h_edw_uv_name := BSC_UPDATE_UTIL.Get_EDW_Union_View_Name(x_table_name);
2886: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
2887: h_sql := 'CREATE OR REPLACE VIEW '||h_edw_uv_name||' AS ('||
2888: ' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD, '||h_lst_data_columns||
2889: ' FROM '||x_table_name||
2890: ' UNION '||
2894: --AD_DDL has some issues creating the view. It could be because the materialized views
2895: --were not created using AD_DDL?
2896: --So, we create the view directly. This is not so bad because the views are created on APPS schema
2897: --BSC_APPS.Do_DDL(h_sql, AD_DDL.CREATE_VIEW, h_edw_uv_name);
2898: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2899:
2900: -- Get current period for the table.
2901: -- This is the maximun period reported in the materialized view
2902: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2898: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2899:
2900: -- Get current period for the table.
2901: -- This is the maximun period reported in the materialized view
2902: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2903: IF h_yearly_flag = 1 THEN
2904: -- Annual periodicity --> current period is the current fiscal year
2905: x_current_period := x_current_fy;
2906: ELSE
2923:
2924: EXCEPTION
2925: WHEN e_unexpected_error THEN
2926: BSC_MESSAGE.Add(
2927: X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_REFRESH_EDW_VIEWS_FAILED'),
2928: X_Source => 'BSC_UPDATE_CALC.Refresh_EDW_Views');
2929: RETURN FALSE;
2930:
2931: WHEN OTHERS THEN
2944: x_periodicity IN NUMBER,
2945: x_period IN NUMBER,
2946: x_base_periodicity IN NUMBER,
2947: x_base_period IN NUMBER,
2948: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2949: x_num_key_columns IN NUMBER,
2950: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2951: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2952: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
2946: x_base_periodicity IN NUMBER,
2947: x_base_period IN NUMBER,
2948: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2949: x_num_key_columns IN NUMBER,
2950: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2951: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2952: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
2953: x_num_data_columns IN NUMBER,
2954: x_current_fy IN NUMBER,
2947: x_base_period IN NUMBER,
2948: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2949: x_num_key_columns IN NUMBER,
2950: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2951: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2952: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
2953: x_num_data_columns IN NUMBER,
2954: x_current_fy IN NUMBER,
2955: x_is_base IN BOOLEAN
2948: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2949: x_num_key_columns IN NUMBER,
2950: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2951: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2952: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
2953: x_num_data_columns IN NUMBER,
2954: x_current_fy IN NUMBER,
2955: x_is_base IN BOOLEAN
2956: ) RETURN BOOLEAN IS
2991:
2992: h_calendar_id NUMBER;
2993:
2994: -- Bind var fix for Posco
2995: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2996: l_num_bind_vars NUMBER;
2997:
2998: l_parallel_hint varchar2(20000);
2999: l_parallel_hint1 varchar2(20000);
2998: l_parallel_hint varchar2(20000);
2999: l_parallel_hint1 varchar2(20000);
3000: l_parallel_hint2 varchar2(20000);
3001:
3002: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
3003: h_lst_key_columns_temp VARCHAR2(32000);
3004:
3005: h_lst_tot_data_columns_temp VARCHAR2(32000);
3006: h_lst_bal_data_columns_temp VARCHAR2(32000);
3064: h_key_columns_temp(h_i) := 'KEY'||h_i;
3065: END LOOP;
3066:
3067: -- Some information about the periodicity
3068: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
3069:
3070: -- Initialize some variables required for change of periodicity
3071: -- to handle balance and total data columns
3072:
3110: END IF;
3111: END LOOP;
3112:
3113: -- Create a temporal table to make the change of periodicity
3114: h_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
3115: h_origin_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_base_periodicity);
3116:
3117: IF h_num_tot_data_columns > 0 THEN
3118: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE');
3111: END LOOP;
3112:
3113: -- Create a temporal table to make the change of periodicity
3114: h_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
3115: h_origin_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_base_periodicity);
3116:
3117: IF h_num_tot_data_columns > 0 THEN
3118: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE');
3119:
3114: h_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
3115: h_origin_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_base_periodicity);
3116:
3117: IF h_num_tot_data_columns > 0 THEN
3118: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE');
3119:
3120: h_sql := 'INSERT /*+ append ';
3121: if BSC_UPDATE_UTIL.is_parallel then
3122: h_sql:=h_sql||'parallel (bsc_tmp_per_change) ';
3117: IF h_num_tot_data_columns > 0 THEN
3118: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE');
3119:
3120: h_sql := 'INSERT /*+ append ';
3121: if BSC_UPDATE_UTIL.is_parallel then
3122: h_sql:=h_sql||'parallel (bsc_tmp_per_change) ';
3123: end if;
3124: h_sql:=h_sql||' */';
3125: h_sql:=h_sql||'INTO bsc_tmp_per_change (year, src_per, trg_per)'||
3123: end if;
3124: h_sql:=h_sql||' */';
3125: h_sql:=h_sql||'INTO bsc_tmp_per_change (year, src_per, trg_per)'||
3126: ' SELECT ';
3127: if BSC_UPDATE_UTIL.is_parallel then
3128: h_sql:=h_sql||'/*+ parallel (bsc_db_calendar)*/ ';
3129: end if;
3130: h_sql:=h_sql||
3131: 'DISTINCT year, '||h_origin_period_col_name||' AS src_per, '||
3135: l_bind_vars_values.delete;
3136: l_bind_vars_values(1) := h_calendar_id;
3137: l_bind_vars_values(2) := x_current_fy;
3138: l_num_bind_vars := 2;
3139: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
3140: commit;
3141: END IF;
3142:
3143: IF h_num_bal_data_columns > 0 THEN
3140: commit;
3141: END IF;
3142:
3143: IF h_num_bal_data_columns > 0 THEN
3144: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
3145:
3146: h_sql := 'INSERT /*+ append ';
3147: if BSC_UPDATE_UTIL.is_parallel then
3148: h_sql:=h_sql||'parallel (bsc_tmp_per_change_bal) ';
3143: IF h_num_bal_data_columns > 0 THEN
3144: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
3145:
3146: h_sql := 'INSERT /*+ append ';
3147: if BSC_UPDATE_UTIL.is_parallel then
3148: h_sql:=h_sql||'parallel (bsc_tmp_per_change_bal) ';
3149: end if;
3150: h_sql:=h_sql||' */';
3151: h_sql:=h_sql||'INTO bsc_tmp_per_change_bal (year, src_per, trg_per)'||
3149: end if;
3150: h_sql:=h_sql||' */';
3151: h_sql:=h_sql||'INTO bsc_tmp_per_change_bal (year, src_per, trg_per)'||
3152: ' SELECT ';
3153: if BSC_UPDATE_UTIL.is_parallel then
3154: h_sql:=h_sql||'/*+ parallel (bsc_db_calendar)*/ ';
3155: end if;
3156: h_sql:=h_sql||'year, MAX('||h_origin_period_col_name||') AS src_per, '||
3157: h_period_col_name||' AS trg_per'||
3161: l_bind_vars_values.delete;
3162: l_bind_vars_values(1) := h_calendar_id;
3163: l_bind_vars_values(2) := x_current_fy;
3164: l_num_bind_vars := 2;
3165: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
3166: commit;
3167: END IF;
3168:
3169: -- Create temporal tables to calculate total data columns and balance data columns separately
3169: -- Create temporal tables to calculate total data columns and balance data columns separately
3170: -- and then merge them into the target summary table
3171: -- If all data columns are total or balance we dont need those temporal tables
3172: IF (h_num_tot_data_columns > 0) AND (h_num_bal_data_columns > 0) THEN
3173: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_DATA');
3174: h_target_table_tot := 'BSC_TMP_TOT_DATA';
3175:
3176: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BAL_DATA');
3177: h_target_table_bal := 'BSC_TMP_BAL_DATA';
3172: IF (h_num_tot_data_columns > 0) AND (h_num_bal_data_columns > 0) THEN
3173: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_DATA');
3174: h_target_table_tot := 'BSC_TMP_TOT_DATA';
3175:
3176: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BAL_DATA');
3177: h_target_table_bal := 'BSC_TMP_BAL_DATA';
3178: ELSE
3179: h_target_table_tot := 'BSC_TMP_PROJ_CALC';
3180: h_target_table_bal := 'BSC_TMP_PROJ_CALC';
3184: h_lst_from := 'BSC_TMP_PROJ_CALC';
3185: l_parallel_hint:=l_parallel_hint||' parallel (BSC_TMP_PROJ_CALC)';
3186:
3187: -- Initialize some lists that will be part of the query to generate the summary table
3188: h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_key_columns_temp, x_num_key_columns);
3189: h_lst_key_columns_temp := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_key_columns_temp, x_num_key_columns);
3190:
3191: IF h_lst_key_columns IS NOT NULL THEN
3192: h_lst_key_columns := h_lst_key_columns||', ';
3185: l_parallel_hint:=l_parallel_hint||' parallel (BSC_TMP_PROJ_CALC)';
3186:
3187: -- Initialize some lists that will be part of the query to generate the summary table
3188: h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_key_columns_temp, x_num_key_columns);
3189: h_lst_key_columns_temp := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_key_columns_temp, x_num_key_columns);
3190:
3191: IF h_lst_key_columns IS NOT NULL THEN
3192: h_lst_key_columns := h_lst_key_columns||', ';
3193: h_lst_key_columns_temp := h_lst_key_columns_temp||', ';
3208: ' AND BSC_TMP_PROJ_CALC.PERIOD = BSC_TMP_PER_CHANGE.SRC_PER';
3209: h_lst_select_per := 'BSC_TMP_PROJ_CALC.YEAR, BSC_TMP_PROJ_CALC.TYPE, BSC_TMP_PER_CHANGE.TRG_PER';
3210:
3211: h_sql := 'INSERT /*+ append ';
3212: if BSC_UPDATE_UTIL.is_parallel then
3213: h_sql:=h_sql||'parallel ('||h_target_table_tot||') ';
3214: end if;
3215: h_sql:=h_sql||' */';
3216: h_sql:=h_sql||'INTO '||h_target_table_tot;
3219: h_sql:=h_sql||'PERIOD_TYPE_ID, ';
3220: END IF;
3221: h_sql:=h_sql||h_lst_tot_data_columns_temp||')';
3222: h_sql:=h_sql||' SELECT ';
3223: if BSC_UPDATE_UTIL.is_parallel then
3224: h_sql:=h_sql||'/*+'||l_parallel_hint1||'*/ ';
3225: end if;
3226: h_sql:=h_sql||h_lst_select_disag||h_lst_select_per||', :1, ';
3227: IF NOT x_is_base THEN
3245: l_bind_vars_values(2) := x_base_periodicity;
3246: l_bind_vars_values(3) := x_period;
3247: l_num_bind_vars := 3;
3248: END IF;
3249: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
3250: commit;
3251: END IF;
3252:
3253: IF h_num_bal_data_columns > 0 THEN
3257: ' AND BSC_TMP_PROJ_CALC.PERIOD = BSC_TMP_PER_CHANGE_BAL.SRC_PER';
3258: h_lst_select_per := 'BSC_TMP_PROJ_CALC.YEAR, BSC_TMP_PROJ_CALC.TYPE, BSC_TMP_PER_CHANGE_BAL.TRG_PER';
3259:
3260: h_sql := 'INSERT /*+ append ';
3261: if BSC_UPDATE_UTIL.is_parallel then
3262: h_sql:=h_sql||'parallel ('||h_target_table_bal||') ';
3263: end if;
3264: h_sql:=h_sql||' */';
3265: h_sql:=h_sql||'INTO '||h_target_table_bal;
3268: h_sql:=h_sql||'PERIOD_TYPE_ID, ';
3269: END IF;
3270: h_sql:=h_sql||h_lst_bal_data_columns_temp||')';
3271: h_sql := h_sql||' SELECT ';
3272: if BSC_UPDATE_UTIL.is_parallel then
3273: h_sql:=h_sql||'/*+'||l_parallel_hint2||'*/ ';
3274: end if;
3275: h_sql:=h_sql||h_lst_select_disag||h_lst_select_per||', :1, ';
3276: IF NOT x_is_base THEN
3294: l_bind_vars_values(2) := x_base_periodicity;
3295: l_bind_vars_values(3) := x_period;
3296: l_num_bind_vars := 3;
3297: END IF;
3298: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
3299: commit;
3300: END IF;
3301:
3302: IF (h_num_tot_data_columns > 0) AND (h_num_bal_data_columns > 0) THEN
3301:
3302: IF (h_num_tot_data_columns > 0) AND (h_num_bal_data_columns > 0) THEN
3303: -- We need to merge BSC_TMP_TOT_DATA and BSC_TMP_BAL_DATA into BSC_TMP_PROJ_CALC
3304: -- Fix Bug#3131339 Do left join
3305: h_lst_on := BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('T', h_key_columns_temp, 'B', h_key_columns_temp,
3306: x_num_key_columns, 'AND');
3307: IF h_lst_on IS NOT NULL THEN
3308: h_lst_on := h_lst_on||' AND ';
3309: END IF;
3307: IF h_lst_on IS NOT NULL THEN
3308: h_lst_on := h_lst_on||' AND ';
3309: END IF;
3310:
3311: h_lst_select_disag := BSC_UPDATE_UTIL.Make_Lst_Table_Column('T', h_key_columns_temp, x_num_key_columns);
3312: IF h_lst_select_disag IS NOT NULL THEN
3313: h_lst_select_disag := h_lst_select_disag||', ';
3314: END IF;
3315:
3313: h_lst_select_disag := h_lst_select_disag||', ';
3314: END IF;
3315:
3316: h_sql := 'INSERT /*+ append ';
3317: if BSC_UPDATE_UTIL.is_parallel then
3318: h_sql:=h_sql||'parallel (BSC_TMP_PROJ_CALC) ';
3319: end if;
3320: h_sql:=h_sql||' */';
3321: h_sql:=h_sql||'INTO BSC_TMP_PROJ_CALC'||
3326: h_sql:=h_sql||h_lst_tot_data_columns_temp||', '||h_lst_bal_data_columns_temp||')'||
3327: ' SELECT ';
3328: --Fix bug#3875046: Use hash hint
3329: h_sql:=h_sql||'/*+use_hash(T) use_hash(B)*/ ';
3330: if BSC_UPDATE_UTIL.is_parallel then
3331: h_sql:=h_sql||'/*+ parallel (T) parallel (B)*/ ';
3332: end if;
3333: h_sql:=h_sql||h_lst_select_disag||'T.YEAR, T.TYPE, T.PERIOD, T.PERIODICITY_ID, ';
3334: IF NOT x_is_base THEN
3338: ' FROM BSC_TMP_TOT_DATA T, BSC_TMP_BAL_DATA B'||
3339: ' WHERE '||h_lst_on;
3340: h_sql := h_sql||'T.YEAR = B.YEAR (+) AND T.TYPE = B.TYPE (+)'||
3341: ' AND T.PERIOD = B.PERIOD (+) AND T.PERIODICITY_ID = B.PERIODICITY_ID (+)';
3342: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3343: commit;
3344: END IF;
3345: commit;
3346:
3344: END IF;
3345: commit;
3346:
3347: -- Fix bug#4463132: Truncate temporary table after use
3348: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE');
3349: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
3350: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_DATA');
3351: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BAL_DATA');
3352: commit;
3345: commit;
3346:
3347: -- Fix bug#4463132: Truncate temporary table after use
3348: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE');
3349: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
3350: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_DATA');
3351: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BAL_DATA');
3352: commit;
3353:
3346:
3347: -- Fix bug#4463132: Truncate temporary table after use
3348: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE');
3349: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
3350: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_DATA');
3351: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BAL_DATA');
3352: commit;
3353:
3354: RETURN TRUE;
3347: -- Fix bug#4463132: Truncate temporary table after use
3348: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE');
3349: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
3350: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_DATA');
3351: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BAL_DATA');
3352: commit;
3353:
3354: RETURN TRUE;
3355:
3356: EXCEPTION
3357: WHEN e_unexpected_error THEN
3358: ROLLBACK;
3359: BSC_MESSAGE.Add(
3360: X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_FAILED'),
3361: X_Source => 'BSC_UPDATE_CALC.Rollup_Projection');
3362: RETURN FALSE;
3363:
3364: WHEN OTHERS THEN