SELECT
10 as report_period
,sap.dfu
,sap.material
,sap.materialdesc
,sap.billto
--,stp_mapping.distributor
,stp_mapping.market
,sap.transittime
,CASE WHEN (sap.source='ORDERS' and (UNIX_TIMESTAMP(CONCAT(sap.year,'-',sap.arrperiod,'-','01'),'yyyy-mm-dd')) <= (UNIX_TIMESTAMP(CONCAT(YEAR(CURRENT_DATE),'-',10,'-','01'),'yyyy-mm-dd'))) THEN 10
WHEN (sap.source='INVOICE' and (UNIX_TIMESTAMP(CONCAT(sap.year,'-',sap.arrperiod,'-','01'),'yyyy-mm-dd')) = (UNIX_TIMESTAMP(CONCAT(YEAR(CURRENT_DATE),'-',10,'-','01'),'yyyy-mm-dd')))  THEN 10
else sap.arrperiod END as arrperiod
,sap.year
,SUM(sap.cases)  as cases
,CASE
WHEN (sap.source='INVOICE' and (UNIX_TIMESTAMP(CONCAT(sap.year,'-',sap.arrperiod,'-','01'),'yyyy-mm-dd')) < (UNIX_TIMESTAMP(CONCAT(YEAR(CURRENT_DATE),'-',10,'-','01'),'yyyy-mm-dd'))) THEN 1
WHEN (sap.source='ORDERS' and (UNIX_TIMESTAMP(CONCAT(sap.year,'-',sap.arrperiod,'-','01'),'yyyy-mm-dd')) <= (UNIX_TIMESTAMP(CONCAT(YEAR(CURRENT_DATE),'-',10,'-','01'),'yyyy-mm-dd'))) THEN 2
WHEN (sap.source='INVOICE' and (UNIX_TIMESTAMP(CONCAT(sap.year,'-',sap.arrperiod,'-','01'),'yyyy-mm-dd')) = (UNIX_TIMESTAMP(CONCAT(YEAR(CURRENT_DATE),'-',10,'-','01'),'yyyy-mm-dd')))  THEN 2
WHEN (sap.source='ORDERS' and (UNIX_TIMESTAMP(CONCAT(sap.year,'-',sap.arrperiod,'-','01'),'yyyy-mm-dd')) > (UNIX_TIMESTAMP(CONCAT(YEAR(CURRENT_DATE),'-',10,'-','01'),'yyyy-mm-dd'))) THEN 3
ELSE '' END as timeframe
,current_timestamp

FROM amea.src_ext_txt_menat_sap_exf sap
LEFT JOIN (select distinct stp_map.market, stp_map.stp,stp_map.distributor, transit_map.plant
from amea.src_ext_txt_menat_pathways_stp_mapping as stp_map
INNER join amea.src_ext_txt_menat_pathways_arrivals_transit_mapping as transit_map
ON stp_map.distributor = transit_map.sold_to_name) stp_mapping
ON INT(sap.billto)=INT(stp_mapping.stp)
AND INT(sap.plantcode)=INT(stp_mapping.plant)

group by
10
,sap.dfu
,sap.material
,sap.materialdesc
,sap.billto
--,stp_mapping.distributor
,stp_mapping.market
,sap.transittime
,CASE WHEN (sap.source='ORDERS' and (UNIX_TIMESTAMP(CONCAT(sap.year,'-',sap.arrperiod,'-','01'),'yyyy-mm-dd')) <= (UNIX_TIMESTAMP(CONCAT(YEAR(CURRENT_DATE),'-',10,'-','01'),'yyyy-mm-dd'))) THEN 10
WHEN (sap.source='INVOICE' and (UNIX_TIMESTAMP(CONCAT(sap.year,'-',sap.arrperiod,'-','01'),'yyyy-mm-dd')) = (UNIX_TIMESTAMP(CONCAT(YEAR(CURRENT_DATE),'-',10,'-','01'),'yyyy-mm-dd')))  THEN 10
else sap.arrperiod END
,sap.year
,CASE
WHEN (sap.source='INVOICE' and (UNIX_TIMESTAMP(CONCAT(sap.year,'-',sap.arrperiod,'-','01'),'yyyy-mm-dd')) < (UNIX_TIMESTAMP(CONCAT(YEAR(CURRENT_DATE),'-',10,'-','01'),'yyyy-mm-dd'))) THEN 1
WHEN (sap.source='ORDERS' and (UNIX_TIMESTAMP(CONCAT(sap.year,'-',sap.arrperiod,'-','01'),'yyyy-mm-dd')) <= (UNIX_TIMESTAMP(CONCAT(YEAR(CURRENT_DATE),'-',10,'-','01'),'yyyy-mm-dd'))) THEN 2
WHEN (sap.source='INVOICE' and (UNIX_TIMESTAMP(CONCAT(sap.year,'-',sap.arrperiod,'-','01'),'yyyy-mm-dd')) = (UNIX_TIMESTAMP(CONCAT(YEAR(CURRENT_DATE),'-',10,'-','01'),'yyyy-mm-dd')))  THEN 2
WHEN (sap.source='ORDERS' and (UNIX_TIMESTAMP(CONCAT(sap.year,'-',sap.arrperiod,'-','01'),'yyyy-mm-dd')) > (UNIX_TIMESTAMP(CONCAT(YEAR(CURRENT_DATE),'-',10,'-','01'),'yyyy-mm-dd'))) THEN 3
ELSE '' END