insert into myTPF ( mfr_id, forecast_year, forecast_month, forecast_qty, forecast_amt, forecast_exp, skuId, tPartnerId, tPartnerType, forecast_status, qualifierId, qualifierType, rebateContractId )
select tpf.mfr_id, cast( tpf.forecast_year as int ) forecast_year, cast( tpf.forecast_month as int ) forecast_month, 
	sum( tpf.forecast_qty ) forecast_qty,
	sum( tpf.forecast_amt ) forecast_amt, 
	sum( tpf.forecast_exp ) forecast_exp, 
	tpf.skuId, tpf.tpartnerId, tpf.tpartnerType, tpf.forecast_status, coalesce( tpf.qualifierId, 0 ) qualifierId, coalesce( tpf.qualifierType, '?' ) qualifierType, coalesce( tpf.rebateContractId, 0 ) rebateContractId
from tbl_MFR_TPF_Detail_MFR1 tpf with (nolock)
where tpf.forecast_status = 'C'
and tpf.mfr_id = 1
and cast( tpf.forecast_year as int ) >= 2021
and tpf.tPartnerType = 'OPR'
group by  mfr_id, forecast_status, tpartnerType, tpartnerId, skuId, qualifierType, qualifierId, rebateContractId, forecast_year, forecast_month
order by mfr_id, forecast_status, tpartnerType, tpartnerId, skuId, qualifierType, qualifierId, rebateContractId, forecast_year, forecast_month


drop table myTPF
go


create table myTPF (
	mfr_id int not null, 
	forecast_status char(1) not null,
	tPartnerType varchar(3) not null,
	tPartnerId int not null,
	skuId int not null,
	qualifierType varchar(3) not null,
	qualifierId int not null,
	rebateContractId int not null, 
	forecast_year int not null, 
	forecast_month int not null,
	forecast_qty decimal(18,2) not null,
	forecast_amt decimal( 20,2 ),
	forecast_exp decimal( 20,2)
)
go


alter table myTPF add constraint  pk_myTPF primary key ( mfr_id, forecast_status, tpartnerType, tpartnerId, skuId, qualifierType, qualifierId, rebateContractId, forecast_year, forecast_month ) 
go