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;
/
