create table calc.plan_production_finished_products
(

		 row_id							INT					NOT NULL	IDENTITY(1,1)	
		,data_type						varchar(100)		NOT NULL
		,name_system					varchar(7)				NULL
		,path_file						varchar(300)			NULL
		,date_file						datetime				NULL
		,download_user					varchar(50)				NULL
		,dt_tm_insert					datetime				NULL
									
		,reason_ignore_in_calculate		VARCHAR(300)			NULL

		,sap_id							BIGINT					NULL
		,sap_id_text					as convert(varchar(24), FORMAT(sap_id, '000000000000000000000000'))
		,sap_id_original				BIGINT					NULL
		,sap_id_original_text			as convert(varchar(24), FORMAT(sap_id_original, '000000000000000000000000'))
		,sap_id_expiration_date_in_days	SMALLINT				NULL

		,stuffing_id					VARCHAR(40)				NULL
		,stuffing_id_box				VARCHAR(40)				NULL

		,position_dependent_id			INT						NULL	
		,individual_marking_id			TINYINT					NULL	
		,article_nomenclature			VARCHAR(20)				NULL	
		,article_packaging				VARCHAR(25)				NULL	
		,product_finished_id			decimal(14, 0)			NULL


		--- остатки ---
		,stock_sort_row					INT						NULL -- используется для расчетов
		,stock_warehouse_name			VARCHAR(100)			NULL	
		,stock_storage_area_name		VARCHAR(100)			NULL	
		,stock_branch_name				VARCHAR(100)			NULL
		,stock_production_date			DATETIME				NULL
		,stock_on_date					DATETIME				NULL
		,stock_expiration_date			DATETIME				NULL
		,stock_kg						dec(11,5)				NULL

		,stock_current_KOS				as case when stock_production_date >= stock_expiration_date then null  
												when stock_production_date >  stock_on_date		    then null
												when stock_expiration_date <= stock_on_date		    then 0.000000	
												when stock_production_date	= stock_on_date			then 1
												else DATEDIFF(day, stock_on_date, stock_expiration_date) * 1.0 / DATEDIFF(day, stock_production_date, stock_expiration_date) end

		,stock_KOS_in_day				as case when stock_production_date >= stock_expiration_date then null	
												when stock_production_date >  stock_on_date			then null
												else 1.0 / DATEDIFF(day, stock_production_date, stock_expiration_date) end	
													



		--- набивки ---
		,stuffing_sort_row						INT					NULL -- используется для расчетов
		,stuffing_production_name				VARCHAR(100)		NULL
		,stuffing_production_date_from			DATETIME			NULL
		,stuffing_production_date_to			DATETIME			NULL
		,stuffing_available_date				DATETIME			NULL
		,stuffing_expiration_date				DATETIME			NULL
		,stuffing_maturation					tinyint				NULL
		,stuffing_maturation_and_packaging		tinyint				NULL
		,stuffing_transit_from_production_days	tinyint				null	

		,stuffing_begin_production_date_from	DATETIME			NULL -- дата с которой можем закладывать
		,stuffing_begin_production_date_to		as stuffing_begin_production_date_from + stuffing_maturation
		,stuffing_begin_available_date			as stuffing_begin_production_date_from + stuffing_maturation_and_packaging + stuffing_transit_from_production_days

		,stuffing_kg							dec(11,5)			NULL

		,stuffing_current_KOS				as case when stuffing_production_date_to >= stuffing_expiration_date	then null  
													when stuffing_production_date_to >  stuffing_available_date		then null
													when stuffing_expiration_date	 <= stuffing_available_date		then 0.000000
													when stuffing_production_date_to  = stuffing_available_date		then 1		
													else DATEDIFF(day, stuffing_available_date, stuffing_expiration_date) * 1.0 / DATEDIFF(day, stuffing_production_date_to, stuffing_expiration_date) end

		,stuffing_KOS_in_day				as case when stuffing_production_date_to >= stuffing_expiration_date then null	
													when stuffing_production_date_to >  stuffing_available_date	 then null
													else 1.0 / DATEDIFF(day, stuffing_production_date_to, stuffing_expiration_date) end	




		-- заявки и план продаж
		,shipment_sales_channel_id		TINYINT					NULL
		,shipment_sales_channel_name	VARCHAR(25)				NULL
		,shipment_branch_id				VARCHAR(20)				NULL	
		,shipment_branch_name			VARCHAR(100)			NULL	
		,shipment_customer_id			VARCHAR(20)				NULL
		,shipment_customer_name			VARCHAR(100)			NULL

		,shipment_priority				TINYINT					NULL
		,shipment_priority_for_stuffing tinyint					NULL -- для набивок когда сортировку делаем по приоритету
		,shipment_date					DATETIME				NULL
		,shipment_min_KOS				DEC(7,6)				NULL
		,shipment_kg					dec(11,5)				NULL

		-- расчетные поля

		,stock_shipment_kg				dec(11,5)				NULL
		,stock_net_need					as case when data_type in ('ЗАЯВКИ', 'ПЛАН ПРОДАЖ') then nullif( shipment_kg - isnull(stock_shipment_kg, 0)   , 0) end

		,stuffing_marking_kg			dec(11,5)				NULL			   
		,stuffing_shipment_kg			dec(11,5)				NULL	-- кол-во которое уже отгружено из набивки включая маркировку
		,stuffing_net_need				as case when data_type in ('ЗАЯВКИ', 'ПЛАН ПРОДАЖ') then nullif(   shipment_kg - isnull(stock_shipment_kg, 0) - isnull(stuffing_shipment_kg, 0)   , 0) end

)