create or replace package FS_ALTRIA AUTHID CURRENT_USER is

  -- Author  : Vesel
  -- Created : 26/03/2018 5:13:07 PM
  -- Purpose : this is export process for Altria - Tobacco program.
  --         Current Tobacoo program collect data for all Tobaco sales, 
  --         the organization that recieved this data as CSV file is named IRI
  --         As reward of collected data, Altria organization gives money to the merchant, thats why this process is so important for us, 
  --         our customers recieved some monay
  --         
  --         Simple description of the proces:
  --          
  --           Fase 1 - simple collection of the tobacco sales in file
  --           Fase 2 - this is extension of F1, and we add discounts in the file that merchant gaves to the customer at the moment of the sale.
  --         
  --         Midax has two types of processes:
  --           - extracting data from scratch - currenty this generate file in F1 and our current merchant who did that is Premiere Station. 
  --             The process is prepared to generate F2 file, but unfortunately, Premiere Station doesn't use our Loyalty Program and the final 
  --             result is F1 file
  --           - enriching data which is already in F1 - the current merchant that do that is GPM, we recieve file from GPM which is already 
  --             in F1 state, and enriched this file using Midax promotion system. The end result is file in F2 state
  --
  --         Description of Midax process:
  --           - To generate F1 we just need to extract our Sales table - easy
  --           - To generate F2, we do a little more, which is written bellow
  --         Because Midax doesn't kept the sales with discount for each product, (discount is attached to transaction, but not to the right saled product in the sale)
  --         the process is looping through Midax sales table and simulate each tobacoo sale again as it happens again.
  --         If we have promotion - discount on that transaction we are trying to apply it on tobacoo sale, using the Promotion rules that are 
  --         defined for Tobacoo sales (Thats why in Tobacoo promotion should contain in description the string  'Altria')
  --         Promotion business logic, is very important part of the process.
  --

  procedure trigger_proc(pChain varchar2, pWeek number default 0);
  procedure trigger_proc(pChain varchar2, pDate date default sysdate);
  procedure enrichment_proc(pChain varchar2);
  procedure import_external_file;
  procedure set_week_days(p_week_num number);
  procedure set_week_days(p_date date := sysdate);
  function set_sscs_store(p_store_no varchar2) return varchar2 deterministic;
  function set_loyalty_id(p_card_id number) return varchar2 deterministic;
 

end FS_ALTRIA;
/
create or replace package body FS_ALTRIA is

  g_start_date  date;
  g_end_date    date;
--  g_delta       number := 9 * (1 /24 /60); /* 2 minutes */
  g_chain       varchar2(40);

  type product_rec 
    is record ( original_price   number,  -- retailer price
                quantity         number,  -- quantity sold for current upc
                loyalty_discount number,  -- loyalty discount applied in sale
                upc              varchar2(20), -- upc_no - similar to Midax UPC_NO
                all_quantity     number); -- quantity for all products in current sale
   

  type coupon_rec 
    is record ( sale_id                 number,      -- sale_id for coupon
                prom_id                 number,      -- promotion id for coupon
                prom_no                 varchar2(50),-- specific altria promotin num
                card_id                 number,
                cpn_trnsctn_amnt        number,      -- discount for promotion - can be sum of many coupons from the same type
                cpn_prise               number,      -- prise value for one coupon
                cpn_plu                 varchar2(20),-- upc_no - similar to Midax UPC_NO
                cpn_rqrd_qty            number,      -- required upc quantity necessary to satisfy promotion requirements
                upc_group_id            number,      -- elidgiable upc group for promotion
                cpn_count               number,      -- how many times this promotion was applied in the current sale
                cpn_type                varchar2(20),-- altria promotion types only
                cnt                     number);     -- how many different promotions were applied in the current sale (distinct  prom_id)
  
  -- -----------------------------------------------------------------
  -- Procedure :    set_query_variable
  -- Description :  
  -- Params :       see below.
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure  set_contex_variable(pVar in varchar2, pVal in varchar2) is
  begin
    DBMS_SESSION.set_context('ALTRIA', pVar, pVal);
  end set_contex_variable;
  
  -- -----------------------------------------------------------------
  -- Procedure :    get_contex_variable
  -- Description :  
  -- Params :       see below.
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  function get_contex_variable(pVar in varchar2)
    return varchar2 is
  begin
    return sys_context('ALTRIA', upper(pVar));
  end get_contex_variable;
  -- -----------------------------------------------------------------
  -- Procedure :    reset_query_variables
  -- Description :  This should be at the end when you set some query variables
  -- Params :       see below.
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure  reset_contex_variables is
  begin
    DBMS_SESSION.CLEAR_CONTEXT('ALTRIA');
  end reset_contex_variables;    

  -- -----------------------------------------------------------------
  -- Procedure :    Just simple sleep - stealed from the internet
  -- Description :  Sleep N seconds
  -- Params :       see below.
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure sleep(pSecs integer) is
    start_dts DATE := sysdate;
    curr_dts  DATE;
    eye       NUMBER := 0;
    l_one_sec constant number := 1 / 24 / 60 / 60;
  BEGIN
    LOOP
      eye      := eye + 1;
      curr_dts := sysdate;
      EXIT WHEN curr_dts > start_dts + l_one_sec * pSecs;
    END LOOP;
  END;

  -- -----------------------------------------------------------------
  -- Procedure :    get_week_days
  -- Description :  Get the first/last date in the week as global variables
  -- Params :       week number in the current year.
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure set_week_days(p_week_num number) is
  begin
    -- do some changes here - start / end should be from Sunday till Saturday - to be clear change the func
    with tgt as
     (select to_date(p_week_num * 7, 'DDD') as dt from dual)
    select dt as start_date,
           dt + 6  as end_date into g_start_date, g_end_date
      from tgt;
      
      -- bug fix - year changed and the Start Date and the End Date are going into next year:
      if to_date(p_week_num * 7, 'DDD') - trunc(sysdate, 'YY') > 7 then
        -- move the days into previous year -> to_char(sysdate, 'YYYY') - 1
        g_start_date := to_date(to_char(g_start_date, 'ddmm')||to_char(sysdate, 'YYYY') - 1, 'ddmmyyyy');
        -- end date should be allways in the next year
        g_end_date := to_date(to_char(g_end_date, 'ddmm')||to_char(sysdate, 'YYYY'), 'ddmmyyyy');
      end if;

    end;
    
  -- -----------------------------------------------------------------
  -- Procedure :    get_week_days
  -- Description :  Get the first/last date in the week as global variables 
  -- Params :       date.
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure set_week_days(p_date date := sysdate) is
  begin
    -- select trunc(p_date, 'WW') - 9, trunc(p_date, 'WW') - 9 + 6 into g_start_date, g_end_date from DUAL;
    select trunc(p_date) - 6, trunc(p_date)
      into g_start_date, g_end_date
      from DUAL;
    if g_end_date > sysdate then
      RAISE_APPLICATION_ERROR (-20000,'This is not prediction program, Enter correct date plese.'); 
    end if;
    if to_char(g_end_date,'d') <> 7 then
      RAISE_APPLICATION_ERROR (-20000,'You should enter only date that is Saturday.'); 
    end if;
  end;
  
  -- -----------------------------------------------------------------
  -- Procedure :    get_upc
  -- Description :  Get upc info for given upc_no 
  -- Params :       upc_no
  -- Author  :      Vesel
  -- -----------------------------------------------------------------  
  function get_reg_expr(pFLAG varchar2) return varchar2 is
    l_reg_expr ALTRIA_CONFIG_MAIN.Left_Reg_Expr%TYPE;
  begin
    if upper(pFLAG) = 'LEFT' then
      
      select C.Left_Reg_Expr
        into l_reg_expr
        from altria_config_main C
       where C.ENRICHMENT_FLAG = 1
         and C.Chain = g_chain;
         
    elsif upper(pFLAG) = 'RIGHT' then
    
      select C.Right_Reg_Expr
          into l_reg_expr
          from altria_config_main C
         where C.ENRICHMENT_FLAG = 1
           and C.Chain = g_chain;
    else
      -- unknown
      l_reg_expr := '';
    end if;
           
    return l_reg_expr;
  exception when OTHERS then 
    return '';
  end;  
  
  -- -----------------------------------------------------------------
  -- Procedure :    get_upc
  -- Description :  Get upc info for given upc_no 
  -- Params :       upc_no
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  function get_upc(p_upc_id number) return ot_upc is
    o ot_upc := ot_upc();
  begin 
    
    begin 
      select id, upc_no, description, department_id, nvl(measure_id, 'PACK'), nvl(unisize, 1)
        into o.upc_id, o.upc_no, o.upc_description, o.upc_department_id, o.upc_measure_id, o.upc_in_case
        from upc U
       where U.id = p_upc_id
         and U.Chain = g_chain
         and rownum = 1;

     exception
      when no_data_found then
        return ot_upc;
     end;
     
    begin
      
      select V.FIELD_VALUE into o.upc_mfg_name
        from ext_param_names N, ext_param_values V
       where N.chain = g_chain
         and N.Param_Name = 'MERCHANT'
         and V.Table_Id = 1 /* table_id = 1 -> upc table */
         and V.Param_Id = N.Param_Id
         and V.RECORD_ID = o.upc_id;
         
      exception 
        when no_data_found then
        o.upc_mfg_name := 'N/A';
      end;


     return o;
     
  exception when others then 
    raise;     
  end;

  -- -----------------------------------------------------------------
  -- Procedure :    get_upc
  -- Description :  Get upc info for given upc_no 
  -- Params :       upc_no
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  function get_store(p_store_id number) return ot_store is
    o ot_store := ot_store();
  begin 
    select id,
           store_no, description, address1, address2, city, state, zip /*,  contact1, contact2, phone1,
           phone2, fax, store_type, latitude, longitude*/
      into o.store_id, o.store_no, o.store_description, o.store_address1, o.store_address2, o.store_city, o.store_state, o.store_zip /*, o.store_contact1,
           o.store_contact2, o.store_phone1, o.store_phone2, o.store_fax, o.store_type, o.store_latitude, o.store_longitude*/
      from store
     where chain = g_chain
       and id = p_store_id;

    return o;
    
  exception
      when no_data_found then return o;
      when others then raise;
  end;
  
  -- -----------------------------------------------------------------
  -- Procedure :    get_midax_store
  -- Description :  if we have different stores in Midax and SSCS we are using mapping table, this table contains translation - on import
  -- Params :       sscs_store_no
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  function get_midax_store(p_store_no varchar2) return varchar2 deterministic is
    l_store_no varchar2(6);
  begin 

    select midax_store_no
      into l_store_no
      from altria_config_main C, altria_store s
     where chain = g_chain
       and C.account_id = s.account_id
       and sscs_store_no = p_store_no;
       
    return l_store_no;
    
  exception
      when no_data_found then return p_store_no;
      when others then raise;
  end;
  
  
  -- -----------------------------------------------------------------
  -- Procedure :    set_sscs_store
  -- Description :  if we have different stores in Midax and SSCS we are using mapping table, this table contains translation - on export
  -- Params :       midax_store_no
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  function set_sscs_store(p_store_no varchar2) return varchar2 deterministic is
    l_store_no varchar2(6);
  begin 

    select s.sscs_store_no
      into l_store_no
      from altria_config_main C, altria_store s
     where chain = g_chain
       and C.account_id = s.account_id
       and s.midax_store_no = p_store_no;
       
    return l_store_no;
    
  exception
      when no_data_found then return p_store_no;
      when others then raise;
  end;
    
  -- -----------------------------------------------------------------
  -- Procedure :    set_loyalty_id
  -- Description :  we set Midax PAN here, its defined as loyalty ID in IRI
  -- Params :       midax_store_no
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  function set_loyalty_id(p_card_id number) return varchar2 deterministic is
    l_pan varchar2(25);
  begin 

    select pan into l_pan from card where id = p_card_id;
    
    -- sometimes non loyalty customers in midax are identifyed by 0
    -- translate that to NONE or NULL
    if l_pan = 0 then
      return null;
    end if;
       
    return l_pan;
    
  exception
      when no_data_found then return null;
      when others then raise;
  end;
  
  -- -----------------------------------------------------------------
  -- Procedure :    get_account
  -- Description :  return account id
  -- Params :       account_id
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  function get_account return number is
    l_account_id number;
  begin 
    
    select account_id 
      into l_account_id 
      from ALTRIA_CONFIG_MAIN T 
     where chain = g_chain;

    return l_account_id;
  exception
      when no_data_found then return 0;
      when others then raise;
  end;  
    
  -- -----------------------------------------------------------------
  -- Procedure :    get_trips_array
  -- Description :  return array with all sales as sale_id  
  -- Params :       
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  function get_trips_array return t_sale as
    l_year_1 integer;
    l_year_2 integer;
    l_sql varchar2(4000);
    l_arr t_sale := t_sale();
  begin
    l_year_1 :=  EXTRACT(year from g_start_date);
    l_year_2 :=  EXTRACT(year from g_end_date);    

    -- The week belongs into one year
    if l_year_1 = l_year_2 then
      -- somthing like select sale_id from trips_2018 where time_id >=  sysdate - 1 and time_id <= sysdate + 1
      l_sql := 'select ot_sale(T1.time_id, T1.sale_id, T1.store_id, T1.terminal_id, T1.card_id, null, T1.amount, null, null) 
                  from trips_'|| l_year_1 ||' T1
                 where T1.time_id >= to_date('''|| g_start_date || ''') 
                   and T1.time_id < to_date(''' || g_end_date ||''') + 1';
     -- the week belongs to two years
    else
      l_sql := 'select ot_sale(T1.time_id, T1.sale_id, T1.store_id, T1.terminal_id, T1.card_id, null, T1.amount, null, null) 
                  from trips_'|| l_year_1 ||' T1
                 where T1.time_id >= to_date('''|| g_start_date || ''') 
                   and T1.time_id < to_date(''' || g_end_date || ''') + 1
                union all
                select ot_sale(T1.time_id, T1.sale_id, T1.store_id, T1.terminal_id, T1.card_id, null, T1.amount, null, null) 
                  from trips_'|| l_year_2 ||' T1
                 where time_id >= to_date('''|| g_start_date || ''') 
                   and time_id < to_date(''' || g_end_date ||''') + 1';
    end if;
    
    EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_arr;
    return l_arr;
  end;
    
-- -----------------------------------------------------------------
-- Procedure :    get_promotions
-- Description :  returns altria promotions defined in Midax - we recognize it by 'Altria' in description
-- Params :       
-- Author  :      Vesel
-- -----------------------------------------------------------------
  function get_promotions return t_promotion as
    l_arr t_promotion := t_promotion();
  begin

    select ot_promotion(prom_id,
                        description,
                        active,
                        date_begin,
                        date_end,
                        altria_type, -- external_type
                        altria_nom) -- external_no
           bulk collect
      into l_arr
      from (select prom_id,
                   description,
                   active,
                   date_begin,
                   date_end,
                   replace(REGEXP_SUBSTR(description, '-[^-]+-'), '-', '') altria_type,
                   replace(REGEXP_SUBSTR(description, '#[^#]+#'), '#', '') altria_nom
              from promotion_header H
             where lower(H.description) like '%altria%'
               and H.chain = g_chain
               and H.active = 1);

    return l_arr;
  end;

  -- -----------------------------------------------------------------
  -- Procedure :    get_rewards
  -- Description :  get all coupons for given promotions  
  -- Params :       
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  function get_rewards RETURN SYS_REFCURSOR is
    rewards_refcur SYS_REFCURSOR;
    l_promo        t_promotion;
    l_trips        t_sale;
  begin
    l_promo := get_promotions();
    l_trips := get_trips_array();
    --    coupon_rec is used with that func
    open rewards_refcur for
      select sale_id,
             prom_id,
             min(external_no) prom_no,
             min(card_id) card_id,
             sum(received_amount) cpn_trnsctn_amnt,
             sum(received_amount) / count(*) cpn_prise,
             min(plu) cpn_plu,
             min(required_qty) cpn_rqrd_qty,
             min(UPC_GROUP_ID) upc_group_id, 
             count(*) cpn_count,
             min(external_type) cpn_type,
             count(distinct prom_id) over(partition by sale_id) cnt
        from (select (select sale_id
                        from TABLE(l_trips) T
--                        from trips_2018 t                        
                       where t.time_id = ct.date_posted
                         and t.store_id = ct.store_id
                         and t.card_id = ct.card_id
                         and t.terminal_id = ct.pos_number
                         and rownum = 1) sale_id,
                     ct.card_id,
                     ct.prom_id,
                     ct.received_amount,
                     ct.kind,
                     cti.plu,
                     (select CMPR_VALUE1 * R.PTS_VAL_MIN -- different types of promootion can be aplied - this fix when we gave pnt for each box
                        from promotion_premises P, promotion_rewards R
                       where P.prom_id = CT.prom_id
                         and check_object = 1
                         and cmp_attr = 2
                         and P.prom_id = R.prom_id
                         and rownum = 1) REQUIRED_QTY,
                     (select CHECK_VALUE
                        from promotion_premises P, promotion_rewards R
                       where P.prom_id = CT.prom_id
                         and check_object = 1
                         and cmp_attr = 2
                         and P.prom_id = R.prom_id
                         and rownum = 1) UPC_GROUP_ID,
                     P.external_type,
                     P.external_no
                from coupons_trace CT,
                     coupon_trace_items CTI,
                     TABLE(l_promo) P
/*                 -- for debug - instead of TABLE(l_promo) P
                   (select prom_id,
                           description,
                           active,
                           date_begin,
                           date_end,
                           replace(REGEXP_SUBSTR(description, '-[^-]+-'), '-', '') external_type,
                           replace(REGEXP_SUBSTR(description, '#[^#]+#'), '#', '') external_no
                      from promotion_header H
                     where lower(H.description) like '%altria%'
                       and H.chain = 'PREMST'
                       and H.active = 1) P */                     
               where CT.prom_id = P.prom_id
                 and CT.date_posted >= g_start_date
                 and CT.date_posted < g_end_date + 1
                 and CT.Coupon_Id = CTI.Coupon_Id) B
       group by sale_id, prom_id
       order by sale_id;

    return rewards_refcur;

  end;

-- -----------------------------------------------------------------
-- Procedure :    get_rewarded_products
-- Description :  get products in given sale that belongs to upc_group_id
-- Params :       sale_id
-- Author  :      Vesel
-- -----------------------------------------------------------------
  function get_rewarded_products(p_sale_id number, p_upc_group_id number)
    return t_upc is
    l_arr t_upc;
  begin
    -- collect this cursor in T_UPC nested table
    -- make this simple
    -- all_quantity, upc_no, null , null, null, null, quantity, loyalty_discount, original_price, null
    select ot_upc(all_quantity,
                  upc,
                  null,
                  null,
                  null,
                  null,
                  quantity,
                  loyalty_discount,
                  original_price,
                  null) bulk collect
      into l_arr
      from (select A.*, sum(quantity) over() all_quantity
              from (select (sum(R.Final_Price) +
                           sum(nvl(R.loyalty_discount, 0))) / sum(R.Quantity) original_price,
                           sum(R.Quantity) Quantity,
                           sum(nvl(R.loyalty_discount, 0)) loyalty_discount,
                           R.upc
                      from altria_reports R,
                           (select distinct plu_number
                              from sales_extra_info I
                             where sale_id = p_sale_id
                               and item_type = 0
                               and exists
                             (select *
                                      from upc_in_group U
                                     where U.UPC_ID = I.upc_id
                                       and U.GROUP_ID = p_upc_group_id)) EI
                     where R.midax_sale_id = p_sale_id
                       and (R.upc like
                           nvl(trim(both '0' from EI.Plu_Number), 'NULL') || '%' or
                           -- the next row is new one - for Nittany
                           trim(both '0' from R.upc) = nvl(trim(both '0' from EI.Plu_Number), 'NULL') or
                           R.upc = nvl(EI.Plu_Number, 'NULL'))
                     group by upc) A
             order by quantity desc, loyalty_discount desc);

    return l_arr;

  end get_rewarded_products;
  
  -- -----------------------------------------------------------------
  -- Procedure :    cast_to_obj
  -- Description :  cast rowtype to object type
  -- Params :       
  -- Author  :      Vesel
  -- -----------------------------------------------------------------

  function cast_to_obj(p_row ALTRIA_REPORTS%rowtype)
    return ot_altria_reports is
    o ot_altria_reports;
  begin
    o := ot_altria_reports(p_row.batch_id,
                           p_row.row_num,
                           p_row.midax_sale_id,
                           p_row.batch_date,
                           p_row.account_id,
                           p_row.store_no,
                           p_row.store_name,
                           p_row.store_address1,
                           p_row.store_address2,
                           p_row.store_city,
                           p_row.store_state,
                           p_row.store_zip,
                           p_row.store_phone,
                           p_row.store_contact,
                           p_row.store_email,
                           p_row.register_id,
                           p_row.category_id,
                           p_row.unit_measure,
                           p_row.unit_in_pack,
                           p_row.promotion_flag,
                           p_row.multi_pack_flag,
                           p_row.multi_pack_quantity,
                           p_row.multi_pack_discount,
                           p_row.transaction_id,
                           p_row.transaction_date,
                           p_row.mfg_name,
                           p_row.sku,
                           p_row.upc,
                           p_row.upc_description,
                           p_row.product_grp_code,
                           p_row.product_grp_name,
                           p_row.quantity,
                           p_row.mfg_deal_name1,
                           p_row.mfg_deal_discount2,
                           p_row.mfg_deal_name2,
                           p_row.mfg_deal_discount2,
                           p_row.mfg_deal_name3,
                           p_row.mfg_deal_discount3,
                           p_row.final_price,
                           p_row.loyalty_id,
                           p_row.loyalty_discount,
                           p_row.loyalty_promotion);
    return o;
  end cast_to_obj;
  
  -- -----------------------------------------------------------------
  -- Procedure :    post_process
  -- Description :  this is process keep data in history table
  -- Params :        
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure post_process is
    l_date date := sysdate;
    begin
/*      insert \*+ APPEND *\ 
        into altria_report_history
      select l_date, A.* from altria_reports A;*/
      null;
    end;
      
  -- -----------------------------------------------------------------
  -- Procedure :    split_items
  -- Description :  split all rows in the current sale to one row per each bought item
  -- Params :       sale_id, 
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure split_items(p_sale_id number,
                        p_tbl     out nocopy t_altria_reports) is
    cursor my_cur is
/*      select *
        from altria_reports R
       where midax_sale_id = p_sale_id
       order by R.Upc;*/
      -- coursor must be sorted
      select RR.*
        from altria_reports RR,
             (select R.row_num, sum(quantity) over(partition by upc) upc_qty
                from altria_reports R
               where midax_sale_id = p_sale_id) RS
       where RR.Row_Num = RS.row_num
       order by RS.upc_qty desc, RR.Upc;     
       
    l_row ALTRIA_REPORTS%rowtype;
    idx   integer;
    l_unit_price number;
  
  begin

    p_tbl := t_altria_reports();
  
    idx := 0;
    for i in my_cur loop
      l_row := i;
      
      -- collect the old row and continue;
      if i.quantity <= 1 then
        p_tbl := p_tbl MULTISET UNION ALL
                 t_altria_reports(cast_to_obj(l_row));
        continue;
      end if;
    
      for j in 1 .. i.quantity loop
        idx                   := idx + 1;        
        -- just do the balance the same as before the spliting
        -- first row
        if j = 1 then
          l_unit_price           := (l_row.final_price +
                                   nvl(i.loyalty_discount, 0)) / i.quantity;
          l_row.final_price      := l_unit_price  - nvl(i.loyalty_discount, 0);
          l_row.loyalty_discount := nvl(i.loyalty_discount, 0);
        else 
          l_row.final_price := l_unit_price;
          l_row.loyalty_discount   := 0;
          l_row.row_num            := altria_row_num.nextval;     
        end if;

        -- all rows
        l_row.quantity           := 1;

        -- insert row in collection
        p_tbl := p_tbl MULTISET UNION ALL
                 t_altria_reports(cast_to_obj(l_row));
      end loop;
    end loop;
  
  end split_items;
  

  -- -----------------------------------------------------------------
  -- Procedure :    merge_items
  -- Description :  the oposite of split process - group items into one row in the sale 
  -- Params :       sale_id, 
  -- Author  :      Vesel
  -- -----------------------------------------------------------------  
  procedure merge_items(p_report in out nocopy t_altria_reports) is
  begin
    insert into altria_reports
      select min(batch_id),
             min(row_num),
             min(midax_sale_id),
             min(batch_date),
             min(account_id),
             min(store_no),
             min(store_name),
             min(store_address1),
             min(store_address2),
             min(store_city),
             min(store_state),
             min(store_zip),
             min(store_phone),
             min(store_contact),
             min(store_email),
             min(register_id),
             min(category_id),
             min(unit_measure),
             min(unit_in_pack),
             min(promotion_flag),
             min(multi_pack_flag),
             min(multi_pack_quantity),
             min(multi_pack_discount),
             min(transaction_id),
             min(transaction_date),
             min(mfg_name),
             min(sku),
             min(upc),
             min(upc_description),
             min(product_grp_code),
             min(product_grp_name),
             sum(quantity),
             min(mfg_deal_name1),
             min(mfg_deal_discount1),
             min(mfg_deal_name2),
             min(mfg_deal_discount2),
             min(mfg_deal_name3),
             min(mfg_deal_discount3),
             sum(final_price),
             min(loyalty_id),
             decode(sum(nvl(loyalty_discount, 0)), 0, null, sum(nvl(loyalty_discount, 0))),
             decode(sum(nvl(loyalty_discount, 0)), 0, null, min(loyalty_promotion))             
        from TABLE(p_report)
       group by midax_sale_Id, register_id, upc order by register_id, upc;

  end merge_items;
  
  -- -----------------------------------------------------------------
  -- Procedure :    process_mpack
  -- Description :  Set single item to be part of multipack promotion
  -- Params :       
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure process_mpack(p_cpn coupon_rec, 
                           p_report in out nocopy t_altria_reports) is
    l_cpn_id          number := 1;
    l_bkp             t_altria_reports;
    l_upcs            t_upc;
    l_cnt             number;
    l_idx             number := 0;
    l_promoted_items_qty number; -- how many promoted items we have in the current sale;
  
  begin
    -- for debug
    if p_cpn.sale_id = 58852209 then
      null;
    end if;
        
    l_bkp := p_report;
        
    -- only promoted upc
    l_upcs := get_rewarded_products(p_cpn.sale_id, p_cpn.upc_group_id);

    
    l_promoted_items_qty := p_cpn.cpn_count * p_cpn.cpn_rqrd_qty;

      
    for i in 1..l_promoted_items_qty loop
      
      select count(*)
        into l_cnt
        from TABLE(l_upcs) T
       where T.upc_no = p_report(i).upc;

      -- if current upc is not eligiable for current promotion - continue  
      if l_cnt =  0 then continue; end if;
        
      l_idx := l_idx + 1;
      p_report(i).multi_pack_flag        := 'Y';
      p_report(i).multi_pack_quantity    := p_cpn.cpn_rqrd_qty;
      p_report(i).register_id            := l_cpn_id;
      p_report(i).multi_pack_discount    := p_cpn.cpn_prise;
      -- subtract discount from final price - specification requirement
      p_report(i).final_price := p_report(i).final_price - 
                                 nvl(p_report(i).multi_pack_discount / p_cpn.cpn_rqrd_qty , 0);      
--      p_report(i).register_id            := l_cpn_id;              
      
      if mod(l_idx, p_cpn.cpn_rqrd_qty) = 0 then 
        l_cpn_id := l_cpn_id + 1;
      end if;
    end loop;

  exception when others then 
    -- revert to original sale
    p_report := l_bkp;
  end;  
  -- -----------------------------------------------------------------
  -- Procedure :    process_mfg1
  -- Description :  MFG discounts can be applied only once per transaction 
  -- Params :       sale_id, coupon parameteres
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure process_mfg1(p_cpn coupon_rec, 
                         p_report in out nocopy t_altria_reports) is
    l_pan varchar2(20);
    
  
  begin
    null;
  end process_mfg1;
    
  -- -----------------------------------------------------------------
  -- Procedure :    process_mfg2
  -- Description :  
  -- Params :       sale_id, coupon parameteres
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure process_mfg2(p_cpn coupon_rec, 
                         p_report in out nocopy t_altria_reports) is
    l_cpn_id number := 1;
    l_pan varchar2(20);
    
  
  begin
    null;
  end process_mfg2;

  -- -----------------------------------------------------------------
  -- Procedure :    process_mfg3
  -- Description :  
  -- Params :       sale_id, coupon parameteres
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure process_mfg3(p_cpn    coupon_rec,
                         p_report in out nocopy t_altria_reports) is
    l_cpn_id          number := 1;
    l_rqty            number; -- how many products should be discounted - this is coming from the coupon
    l_idx             number := 1; -- just index
    l_pan             varchar2(20);
    l_cnt             number;
    l_bkp             t_altria_reports;
    l_upcs            t_upc;  

  begin
    l_bkp := p_report;
    -- for debug
    if p_cpn.sale_id = 59628799 then
      null;
    end if;
          
    -- only promoted upc
    l_upcs := get_rewarded_products(p_cpn.sale_id, p_cpn.upc_group_id);
        
    select pan into l_pan from card where id = p_cpn.card_id;

    for i in 1 .. p_cpn.cpn_rqrd_qty loop
      
      select count(*)
        into l_cnt
        from TABLE(l_upcs) T
       where T.upc_no = p_report(i).upc;

      -- if current upc is not eligiable for current promotion - continue  
      if l_cnt =  0 then continue; end if;
          
      p_report(i).register_id := p_report(i).register_id || l_cpn_id;
      p_report(i).mfg_deal_name3 := p_cpn.prom_no; -- altria prom NO (this is different from Midax prom ID)
      p_report(i).mfg_deal_discount3 := p_cpn.cpn_prise; -- MFG is applied only one time, doesn't matter how many coupons we have
      
      -- 1. specific for GPM, but I've made it common, I've chose Midax coupon instead of original discount which comming from the file.        
      if p_cpn.cpn_prise = nvl(p_report(i).loyalty_discount, 0) then
        p_report(i).final_price := p_report(i).final_price + nvl(p_report(i).loyalty_discount, 0);
        p_report(i).loyalty_discount := 0;                 -- reset file discount
      end if;
      -- 2. we set discount only to the first row of the discounted upc I've tried to make this simple, but somtimes its complex.
      -- the regulation of discount is setup in merge procedure - indeed when we group we take the sum of final prices of the products,
      -- and min(mfg_deal_discount3). This is necessary because I prcess final price with another logic in different function
      if l_idx = 1 then
        p_report(i).final_price := p_report(i).final_price - p_report(i).mfg_deal_discount3;
      end if;
      
      p_report(i).loyalty_id := l_pan;
      
      -- this type of promotions should happen only once
      if i = l_rqty then
        return;
      end if;
      l_idx := l_idx + 1;      
    end loop;

  exception
    when others then
      -- revert original sale - I don't know what to do if error exists
      p_report := l_bkp;
    
  end process_mfg3;
    
  -- -----------------------------------------------------------------
  -- Procedure :    set_all_discounts
  -- Description :  this should be the main function process
  -- Params :       message event.
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure set_all_discounts is
    l_rewards sys_refcursor;
    l_row ALTRIA_REPORTS%rowtype;
    l_rwd              coupon_rec;
    l_report          t_altria_reports; -- objects type array  
  begin
  
  -- get all rewards for the period
  l_rewards := get_rewards;
   
  loop 
    FETCH l_rewards
     into  l_rwd;
     EXIT WHEN l_rewards%NOTFOUND;
     
     split_items(l_rwd.sale_id, l_report);

     if l_rwd.cpn_type = 'MPACK' then
     null;
       process_mpack(l_rwd, l_report);
     elsif 
        l_rwd.cpn_type = 'MFG1' then   
       null;
     elsif
        l_rwd.cpn_type = 'MFG2' then   
       null;
     elsif
        l_rwd.cpn_type = 'MFG3' then
       null;
       process_mfg3(l_rwd, l_report);

     end if;
     delete from altria_reports where midax_sale_id = l_rwd.sale_id;
     merge_items(l_report);
  end loop;
  CLOSE l_rewards;

   
  exception
    when OTHERS then
      raise;
  end set_all_discounts;

  -- -----------------------------------------------------------------
  -- Procedure :    midax_tobacco
  -- Description :  not clear yet
  -- Params :       message event.
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure import_midax_tobacco is
    l_trips t_sale;
    o ot_upc;
    s ot_store;
    
  begin
    -- truncate working table and populate new data
    execute immediate 'truncate table ALTRIA_REPORTS';
    l_trips := get_trips_array;
            
    for l_row in (
      select null batch_id,
             null row_num, -- sequence here
             ei.sale_id midax_sale_id,
             null batch_date,
             null account_id,
             t.store_id store_no,
             null store_name,
             null store_address1,
             null store_address2,
             null store_city,
             null store_state,
             null store_zip,
             null store_phone,
             null store_contact,
             null store_email,
             null register_id,
             null category_id,
             null unit_measure,
             1 unit_in_pack, -- check this
             null promotion_flag,
             'N' multi_pack_flag,
             null multi_pack_quantity,
             null multi_pack_discount,
             ei.sale_id transaction_id,
             t.time_id transaction_date,
             null mfg_name,
             ei.upc_id sku,
             ei.plu_number upc,
             ei.description upc_description,
             ei.department product_grp_code,
             null product_grp_name,
             ei.quantity quantity,
             null mfg_deal_name1,
             null mfg_deal_discount1,
             null mfg_deal_name2,
             null mfg_deal_discount2,
             null mfg_deal_name3,
             null mfg_deal_discount3,
             ei.quantity * ei.original_price final_price,
             fs_altria.set_loyalty_id(T.card_id) loyalty_id,
             null loyalty_discount,
             null loyalty_promotion
        from sales_extra_info EI, TABLE(l_trips) T
       where exists (select *
                from upc U, altria_config_depart C
               where C.Department_Id = U.Department_Id
                 and c.chain = u.chain
                 and c.chain = g_chain
                 and u.id = ei.upc_id)
         and t.sale_id = ei.sale_id
       order by ei.sale_id, ei.row_id) loop
       
      o := ot_upc();    -- reset object - constructore set all to NULL
      s := ot_store();  -- reset object - constructore set all to NULL      
      -- GPM has different SKU and UPC
      begin
        o := get_upc( l_row.sku );
      exception when others then 
        o := get_upc( l_row.upc );        
      end;
      s := get_store( l_row.store_no );
      
      l_row.row_num            := altria_row_num.nextval;
      l_row.batch_id           := 1;                    -- this is batch_id - should be sequence
      l_row.batch_date         := g_end_date;           -- week end data       
      l_row.account_id         := get_account();        -- account id
      l_row.store_name         := s.store_description;  -- altria store_name
      l_row.store_no           := s.store_no;           -- altria store_number      
      l_row.store_address1     := s.store_address1;     -- altria store_address1
      l_row.store_address2     := s.store_address2;     -- altria store_address1
      l_row.store_city         := s.store_city;         -- altria store City
      l_row.store_state        := s.store_state;        -- altria store State         
      l_row.store_zip          := s.store_zip;          -- altria store ZIP Code         
      l_row.category_id        := o.upc_department_id;  -- I'm not sure for that field - to be clarify
      l_row.unit_measure       := o.upc_measure_id;     -- unit of measure
      l_row.mfg_name           := o.upc_mfg_name;  
      l_row.unit_in_pack       := o.upc_in_case;        -- how many items are in the pack - carton = 10 packs
      l_row.sku                := l_row.upc;            -- sku is midax upc_id, lets change with upc no
                 

      insert into ALTRIA_REPORTS values l_row;
    end loop;
    commit;
  
  exception
    when OTHERS then
      dbms_output.put_line('error');
      raise;
  end import_midax_tobacco;


 -- -----------------------------------------------------------------
  -- Procedure :    import_external_file
  -- Description :  Very custom proccess,
  --                we enrich third party file with trans that contains Midax Loyalty discounts
  -- Params :       message event.
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure import_external_file is

    l_row ALTRIA_REPORTS%rowtype;
    l_tmp varchar2(100);
    l_str_arr  fs_import_utils.split_row_array;
    l_batch_id number;
  
  begin
    execute immediate 'truncate table ALTRIA_REPORTS';
    l_batch_id := altria_batch_id.nextval;
    for i in (select * from ALTRIA_EXTRNL_TABLE ) loop
          l_str_arr.DELETE; -- reset object
          FS_IMPORT_UTILS.SPLIT_DELIMIT_LINE(i.txt_line, l_str_arr, '|');
          
          --lets parse 
          l_row.batch_id := l_batch_id;
          l_row.row_num := altria_row_num.nextval;
          l_row.account_id := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 0);
          l_tmp := null; -- reset          
          l_tmp := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 1);
          l_row.batch_date := to_date(l_tmp, 'YYYYMMDD');
          l_tmp := null; -- reset
--          l_row.transaction_date := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 3);           transaction time          
          l_tmp := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 2)||FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 3);
          l_row.transaction_date := to_date(l_tmp, 'YYYYMMDDHH24:MI:SS');
          l_row.transaction_id   := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 4);

          l_tmp                  := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 5);
          l_row.store_no         := get_midax_store(l_tmp);

          l_row.store_name       := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 6);
          l_row.store_address1   := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 7);
          l_row.store_city       := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 8);
          l_row.store_state      := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 9);
          l_row.store_zip        := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 10);
          
          l_row.category_id      := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 11);
          l_row.mfg_name         := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 12);
          l_row.sku              := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 13);
          l_row.upc              := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 14);
          l_row.upc_description  := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 15);
          
          l_row.unit_measure     := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 16);
          l_row.quantity         := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 17);
          l_row.unit_in_pack     := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 18);
          l_row.multi_pack_flag  := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 19);
          l_row.multi_pack_quantity := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 20);
          l_row.multi_pack_discount := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 21);
          
          l_row.loyalty_promotion   := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 22);
          l_row.loyalty_discount    := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 23);

          l_row.mfg_deal_name1      := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 24);
          l_row.mfg_deal_discount1  := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 25);
          l_row.mfg_deal_name2      := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 26);
          l_row.mfg_deal_discount2  := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 27);
          l_row.mfg_deal_name3      := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 28);
          l_row.mfg_deal_discount3  := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 29);
          
          l_row.final_price         := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 30);
          l_row.store_phone         := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 31);
          l_row.store_contact       := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 32);
          l_row.store_email         := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 33);
          
          l_row.product_grp_code    := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 34);
          l_row.product_grp_name    := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 35);
          l_row.loyalty_id          := FS_IMPORT_UTILS.GET_FROM_TABLE(l_str_arr, 36);

          insert into ALTRIA_REPORTS values l_row;                                                                                                                                                                          
    end loop;
    commit;
      

  exception
    when OTHERS then
      dbms_output.put_line('error');
      raise;
  end;

  -- -----------------------------------------------------------------
  -- Procedure :    set_midax_sale_id
  -- Description :  only for enrichment of external files - this means that we already have Altria file, but we want to add some customer info.
  --                The process is that we have external data in our ALTRIA_REPORTS table, then adding extra info, and the last step - export as CSV file
  -- Params :       .
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure set_midax_sale_id is

    l_trips t_sale;
    l_reg_expr1  ALTRIA_CONFIG_MAIN.Left_Reg_Expr%TYPE;
    l_reg_expr2 ALTRIA_CONFIG_MAIN.Right_Reg_Expr%TYPE; 
    -- !! Important - regular expression should contain groups - this brackets are group -> ()    
    
   
    
    cursor my_curr is 
      select B.midax_sale_id,
             B.midax_card_id,
             A.transaction_id
        from (
            select R.store_no,
                   R.transaction_id,
                   trunc(R.transaction_date) time_id,
                   -- make a good description here, we should capture a group  - substring from transaction_id, 
                   -- in such a manier we will combine transaction which will match with Midax,
                   -- the idea is we always to have 3 or 4 groups and most ot them to be NULL or empty string.
                   -- Combining '' || '' || 'abc' will give us trans_id = 'abc'
                   -- This is because in each Register (Merchant) the string thant define transaction_id can be different
                   -- in the current moment - end of 2018 we have only two different register
                   -- _(\d)-.+_(\d*)$
                   -- get the first and the fird group from regular expression - this (\d*) is a group and match only digits sign
                   -- get the desired group - 1st, 2nd or 3rd - the empty groups return empty string
                   regexp_substr(R.transaction_id, l_reg_expr1, 1,1,NULL,1) ||
                   regexp_substr(R.transaction_id, l_reg_expr1, 1,1,NULL,2) ||                   
                   regexp_substr(R.transaction_id, l_reg_expr1, 1,1,NULL,3) trans_id
              from altria_reports R) A,
                   (select time_id,
                           midax_sale_id,
                           midax_card_id,
                           store_no,
                           regexp_substr(trans_id, l_reg_expr2, 1,1,NULL,1) trans_id
                      from
                           (select trunc(T.time_id) time_id,
                                   t.sale_id midax_sale_id,
                                   t.card_id midax_card_id,
                                   (select store_no from store where id = T.store_id) store_no,
                                   pos_transaction_number,
                                   T.terminal_id || substr(pos_transaction_number, 1, 7) trans_id
                              from trips_extra_info I, TABLE(l_trips) T
                             where T.sale_id = I.sale_id)) B
             where A.time_id = B.time_id
               and A.Store_No = B.store_no
               and A.trans_id = B.trans_id; 
    
    TYPE t_id_tab IS TABLE OF my_curr%rowtype INDEX BY PLS_INTEGER;
    tab t_id_tab;
     
  begin

    l_trips := get_trips_array;
    l_reg_expr1 := get_reg_expr('LEFT');
    l_reg_expr2 := get_reg_expr('RIGHT');    
    
    open my_curr;
    loop
      fetch my_curr bulk collect
        into tab limit 3000;
      EXIT WHEN tab.COUNT = 0;
    
      FORALL indx IN 1 .. tab.COUNT
        update altria_reports S
           set S.midax_sale_id = tab(indx).midax_sale_id,
               S.Loyalty_Id = set_loyalty_id( tab(indx).midax_card_id )
         where S.transaction_id = tab(indx).transaction_id;
       commit;
    end loop;
    close my_curr;
    commit;    
    
  exception
    when OTHERS then
      raise;
  end;    
  
  -- -----------------------------------------------------------------
  -- Procedure :    set_midax_sale_id
  -- Description :  only for enrichment of external files - this means that we already have Altria file, but we want to add some customer info.
  --                The process is that we have external data in our ALTRIA_REPORTS table, then adding extra info, and the last step - export as CSV file
  -- Params :       .
  -- Author  :      Vesel
  -- -----------------------------------------------------------------
  procedure set_midax_sale_id1 is

    l_trips t_sale;
    l_reg_expr ALTRIA_CONFIG_MAIN.Left_Reg_Expr%TYPE;
    
   
    
    cursor my_curr is
    select B.*,
           replace(replace(replace(l_reg_expr, 
                   'TRANSACTION_ID', B.trans_id),
                   'TERMINAL_ID', B.terminal_id),
                   'YYYYMMDD', to_char(B.time_id, 'YYYYMMDD')) REG_EXPR_STR 
      from (
            select trunc(T.time_id) time_id,
                   t.sale_id midax_sale_id,
                   (select store_no from store where id = T.store_id) store_no,
                   T.terminal_id,
                   I.pos_transaction_number,
                   substr(pos_transaction_number, 1, 7) trans_id
              from trips_extra_info I, TABLE(l_trips) T
             where T.sale_id = I.sale_id) B;
               
                                                 
    
    TYPE t_id_tab IS TABLE OF my_curr%rowtype INDEX BY PLS_INTEGER;
    tab t_id_tab;    
  begin

    l_trips := get_trips_array;
    l_reg_expr := get_reg_expr('LEFT');
    
    open my_curr;
    loop
      fetch my_curr bulk collect
        into tab limit 6000;
      EXIT WHEN tab.COUNT = 0;
    
      FORALL indx IN 1 .. tab.COUNT
        update altria_reports S
           set S.midax_sale_id = tab(indx).midax_sale_id
         where S.transaction_id = REGEXP_SUBSTR(S.transaction_id, tab(indx).REG_EXPR_STR)
           and S.STORE_NO = tab(indx).store_no
           and trunc(S.TRANSACTION_DATE) = tab(indx).time_id;
           
    end loop;
    close my_curr;
    commit;    
    
  exception
    when OTHERS then
      raise;
  end;    
  
  -- -----------------------------------------------------------------
  -- Procedure :    trigger_proc
  -- Description :  This is not used any more - for delete
  -- Params :       .
  -- Author  :      Vesel
  -- -----------------------------------------------------------------    
  procedure trigger_proc(pChain varchar2, pWeek number default 0) is
  begin

    -- now the process is simple - we expect only week as number from the year as parameter and chain
    set_week_days(pWeek);
    g_chain := pChain;
    
    import_midax_tobacco;
    set_all_discounts;
    commit;

  exception
    when OTHERS then
      raise;
  end;

  -- -----------------------------------------------------------------
  -- Procedure :    trigger_proc
  -- Description :  
  -- Params :       .
  -- Author  :      Vesel
  -- -----------------------------------------------------------------    
  procedure trigger_proc(pChain varchar2, pDate date default sysdate) is
  begin

    -- now the process is simple - we expect only week as number from the year as parameter and chain
    set_week_days(pDate);
    g_chain := pChain;
    
    import_midax_tobacco;
    set_all_discounts;
    commit;

  exception
    when OTHERS then
      raise;
  end;  
  -- -----------------------------------------------------------------
  -- Procedure :    enrichment_proc
  -- Description :  
  -- Params :       .
  -- Author  :      Vesel
  -- -----------------------------------------------------------------  
  procedure enrichment_proc(pChain varchar2) is
 
  begin

    g_chain := pChain; 
    import_external_file;

    begin
      select trunc(min(transaction_date)), trunc(max(transaction_date))
        into g_start_date, g_end_date
        from altria_reports;
    exception when OTHERS then
      dbms_output.put_line('!!! Invalid date interval !!!');
      return;
    end;
    
    set_midax_sale_id;
    set_all_discounts;
    post_process;
    commit;
  end; 

begin
  DBMS_SESSION.set_identifier('ALTRIA_PROCESS');
end FS_ALTRIA;
/
