mipastemagico

-- CAR GENERATION

declare
aniomesdia_ini VARCHAR2(8);
aniomesdia_fin VARCHAR2(8);
aniomesdia_ini_3M VARCHAR2(8);
aniomesdia_ini_6M VARCHAR2(8);
aniomesdia_ini_12M VARCHAR2(8);
aniomesdia_ini_po VARCHAR2(8);
aniomesdia_fin_po VARCHAR2(8);
aniomes VARCHAR2(6);

begin
aniomes := '201610';
aniomesdia_ini := '20151101';
aniomesdia_fin := '20161031';
aniomesdia_ini_3M := TO_CHAR(ADD_MONTHS(TO_DATE(aniomesdia_ini,'YYYYMMDD'),-2),'YYYYMMDD');
aniomesdia_ini_6M := TO_CHAR(ADD_MONTHS(TO_DATE(aniomesdia_ini,'YYYYMMDD'),-5),'YYYYMMDD');
aniomesdia_ini_12M := TO_CHAR(ADD_MONTHS(TO_DATE(aniomesdia_ini,'YYYYMMDD'),-11),'YYYYMMDD');
aniomesdia_ini_po := '20150101';
aniomesdia_fin_po := '20161031';


/******************************************************************************************/
/**************************** INFO LIFE MILES AND PO DEFINITION****************************/
/******************************************************************************************/

execute immediate
'create table lm_freq_flyers_po as
select frequent_flyer_code from
(select distinct frequent_flyer_code from  fl.dw_sbx_flown_acnt  where od_flight_date>='||aniomesdia_ini_po|| 'and od_flight_date<='||aniomesdia_fin_po|| 'and frequent_flyer_code<>''NOT APPLICABLE'') a
inner join
cl.dw_sbx_lmclient_acnt b
on a.frequent_flyer_code=b.freq_flyer_number';
end;