CREATE OR REPLACE PACKAGE BODY THANNAN.LERNER_METRIC_OLD_TOOL
AS 
        
    procedure pMain 
    
    is
    
        cursor cur is select shop_nbr from igor_elig_stores;
      
    
    begin 
    
        execute immediate 'truncate table old_igor_tool_result';
        commit; 

    
        for rec in cur loop
            -- Spring forecast error in igor_final_result_2 table
            lerner_metric_old_tool.pResult(to_char(rec.shop_nbr), 
            'thannan.igor_styles_hist_spr23',
            'thannan.igor_act_spr03_chain',
            'thannan.igor_tst_spr03',
            'thannan.igor_act_spr02',
            'thannan.igor_tst_spr02',
            'thannan.igor_dept_store_avg_spr02',
            'thannan.igor_dept_avg_spr02',
            'igor_dept_class');
            
           execute immediate 'truncate table igor_bf_si_tool';
           commit;
           
        insert into igor_bf_si_tool (style_nbr, bf, si)
        select /*+ index(c) index(d)*/
            a.style_nbr,
            avg(c.sales_units)/(0.0000001 + avg(d.sales_units)), 
            avg(e.sales_units)/(0.00000001 + avg(f.sales_units))
        from thannan.igor_act_spr03 c, 
            thannan.igor_tst_spr03 d,
            thannan.igor_styles_hist_spr23 a, 
            igor_dept_class h, 
            thannan.igor_dept_store_avg_spr03 e,
            thannan.igor_dept_avg_spr03 f
        where c.shop_nbr = rec.shop_nbr
            and d.shop_nbr = c.shop_nbr
            and d.style_nbr = c.style_nbr
            and a.style_nbr = c.style_nbr
            and h.style_nbr = d.style_nbr
            and e.shop_nbr = d.shop_nbr
            and h.dept_nbr = e.dept_nbr
            and e.dept_nbr = f.dept_nbr
         group by a.style_nbr;
         commit;
         
         insert into old_igor_tool_result (shop_nbr, class_nbr, season, bf_cur, si_cur, fst_accuracy)
         select rec.shop_nbr, 
            a.style_nbr,
            'SPRING',
            a.bf,
            a.si,
            b.sales_units
          from igor_bf_si_tool a,
             igor_final_result_2 b
          where a.style_nbr = b.style_nbr;
          commit;
          
          ---------------------------------------------------
          /* SUMMER SEASON */
          ----------------------------------------------------
       lerner_metric_old_tool.pResult(to_char(rec.shop_nbr), 
            'thannan.igor_styles_hist_sum23',
            'thannan.igor_act_sum03_chain',
            'thannan.igor_tst_sum03',
            'thannan.igor_act_sum02',
            'thannan.igor_tst_sum02',
            'thannan.igor_dept_store_avg_sum02',
            'thannan.igor_dept_avg_sum02',
            'igor_dept_class');
            
        execute immediate 'truncate table igor_bf_si_tool';
        commit;
           
        insert into igor_bf_si_tool (style_nbr, bf, si)
        select /*+ index(c) index(d)*/
            a.style_nbr,
            avg(c.sales_units)/(0.0000001 + avg(d.sales_units)), 
            avg(e.sales_units)/(0.00000001 + avg(f.sales_units))
        from thannan.igor_act_sum03 c, 
            thannan.igor_tst_sum03 d,
            thannan.igor_styles_hist_sum23 a, 
            igor_dept_class h, 
            thannan.igor_dept_store_avg_sum03 e,
            thannan.igor_dept_avg_sum03 f
        where c.shop_nbr = rec.shop_nbr
            and d.shop_nbr = c.shop_nbr
            and d.style_nbr = c.style_nbr
            and a.style_nbr = c.style_nbr
            and h.style_nbr = d.style_nbr
            and e.shop_nbr = d.shop_nbr
            and h.dept_nbr = e.dept_nbr
            and e.dept_nbr = f.dept_nbr
         group by a.style_nbr;
         commit;
         
         insert into old_igor_tool_result (shop_nbr, class_nbr, season, bf_cur, si_cur, fst_accuracy)
         select rec.shop_nbr, 
            a.style_nbr,
            'SUMMER',
            a.bf,
            a.si,
            b.sales_units
          from igor_bf_si_tool a,
             igor_final_result_2 b
          where a.style_nbr = b.style_nbr;
          commit;
          
          -------------------------------------------------------------------------
          /* FALL SEASON */
          -------------------------------------------------------------------------
          pResult(to_char(rec.shop_nbr), 
            'thannan.igor_styles_hist_fal23',
            'thannan.igor_act_fal03_chain',
            'thannan.igor_tst_fal03',
            'thannan.igor_act_fal02',
            'thannan.igor_tst_fal02',
            'thannan.igor_dept_store_avg_fal02',
            'thannan.igor_dept_avg_fal02',
            'igor_dept_class');
            
        execute immediate 'truncate table igor_bf_si_tool';
        commit;
           
        insert into igor_bf_si_tool (style_nbr, bf, si)
        select /*+ index(c) index(d)*/
            a.style_nbr,
            avg(c.sales_units)/(0.0000001 + avg(d.sales_units)), 
            avg(e.sales_units)/(0.00000001 + avg(f.sales_units))
        from thannan.igor_act_fal03 c, 
            thannan.igor_tst_fal03 d,
            thannan.igor_styles_hist_fal23 a, 
            igor_dept_class h, 
            thannan.igor_dept_store_avg_fal03 e,
            thannan.igor_dept_avg_fal03 f
        where c.shop_nbr = rec.shop_nbr
            and d.shop_nbr = c.shop_nbr
            and d.style_nbr = c.style_nbr
            and a.style_nbr = c.style_nbr
            and h.style_nbr = d.style_nbr
            and e.shop_nbr = d.shop_nbr
            and h.dept_nbr = e.dept_nbr
            and e.dept_nbr = f.dept_nbr
         group by a.style_nbr;
         commit;
         
         insert into old_igor_tool_result (shop_nbr, class_nbr, season, bf_cur, si_cur, fst_accuracy)
         select rec.shop_nbr, 
            a.style_nbr,
            'FALL',
            a.bf,
            a.si,
            b.sales_units
          from igor_bf_si_tool a,
             igor_final_result_2 b
          where a.style_nbr = b.style_nbr;
          commit;
          
          -----------------------------------------------
          /* HOLIDAY */
          -----------------------------------------------
          pResult(to_char(rec.shop_nbr), 
            'thannan.igor_styles_hist_hol12',
            'thannan.igor_act_hol02_chain',
            'thannan.igor_tst_hol02',
            'thannan.igor_act_hol01',
            'thannan.igor_tst_hol01',
            'thannan.igor_dept_store_avg_hol01',
            'thannan.igor_dept_avg_hol01',
            'igor_dept_class');
            
        execute immediate 'truncate table igor_bf_si_tool';
        commit;
           
        insert into igor_bf_si_tool (style_nbr, bf, si)
        select /*+ index(c) index(d)*/
            a.style_nbr,
            avg(c.sales_units)/(0.0000001 + avg(d.sales_units)), 
            avg(e.sales_units)/(0.00000001 + avg(f.sales_units))
        from thannan.igor_act_hol02 c, 
            thannan.igor_tst_hol02 d,
            thannan.igor_styles_hist_hol12 a, 
            igor_dept_class h, 
            thannan.igor_dept_store_avg_hol02 e,
            thannan.igor_dept_avg_hol02 f
        where c.shop_nbr = rec.shop_nbr
            and d.shop_nbr = c.shop_nbr
            and d.style_nbr = c.style_nbr
            and a.style_nbr = c.style_nbr
            and h.style_nbr = d.style_nbr
            and e.shop_nbr = d.shop_nbr
            and h.dept_nbr = e.dept_nbr
            and e.dept_nbr = f.dept_nbr
         group by a.style_nbr;
         commit;
         
         insert into old_igor_tool_result (shop_nbr, class_nbr, season, bf_cur, si_cur, fst_accuracy)
         select rec.shop_nbr, 
            a.style_nbr,
            'HOLIDAY',
            a.bf,
            a.si,
            b.sales_units
          from igor_bf_si_tool a,
             igor_final_result_2 b
          where a.style_nbr = b.style_nbr;
          commit;
          
        
        end loop;
        
    
    
    end pMain;

    procedure pResult(strStores in varchar2, 
            strStyles_Tbl in varchar2,
            strAct_Cur  in varchar2,
            strTst_Cur  in varchar2,
            strAct_Prev in varchar2,
            strTst_Prev in varchar2,
            strDept_Store_Prev in varchar2,
            strDept_Prev       in varchar2,
            strDept_Style      in varchar2) is 
            
            
        varSQL varchar2(8000);
        
        
    begin
        
        execute immediate 'truncate table igor_styles_est';
        commit;
        
    
        varSQL:= 
        'insert into igor_styles_est 
        select /*+ index(b) index(c) index(d)*/
            a.style_nbr,
            avg(b.sales_units) * avg(c.sales_units)/(0.0000001 + avg(d.sales_units)) / 
            (avg(e.sales_units)/(0.00000001 + avg(f.sales_units))) as sales_units
        from  
            ' || strTst_Cur || ' b,
            ' || strAct_Prev ||' c, 
            ' || strTst_Prev || ' d,
            ' || strStyles_Tbl || ' a, 
            ' || strDept_Style || ' h, 
            ' || strDept_Store_Prev || ' e,
            ' || strDept_Prev || ' f
        where b.shop_nbr = ' ||  strStores || '
            and c.shop_nbr = b.shop_nbr
            and c.style_nbr = b.style_nbr
            and a.style_nbr = c.style_nbr
            and d.style_nbr = a.style_nbr
            and d.shop_nbr = c.shop_nbr
            and h.style_nbr = d.style_nbr
            and e.shop_nbr = d.shop_nbr
            and h.dept_nbr = e.dept_nbr
            and e.dept_nbr = f.dept_nbr
         group by a.style_nbr';
         
        
        execute immediate varSQL;
        commit;
        
        execute immediate 'truncate table igor_final_result_2';
        commit;
        
        varSQL := 
        'insert into igor_final_result_2 
        select a.style_nbr,
            (nvl(a.sales_units, 0) - b.sales_units)/(0.0000001 + b.sales_units) as sales_units
        from igor_styles_est a,
            ' || strAct_Cur || ' b
        where a.style_nbr = b.style_nbr';
        
               
        
        execute immediate varSQL;
        commit;
        
              
    end pResult;
    
    
    END LERNER_METRIC_OLD_TOOL;
/