diff --git a/FS_ALTRIA.sql b/FS_ALTRIA.sql new file mode 100644 index 0000000..558905e --- /dev/null +++ b/FS_ALTRIA.sql @@ -0,0 +1,1396 @@ +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; +/