站点信息
select * from DL_Station

材料明细
select ALL_MATERIEL_SORT.NAME wldl,wlmx.wlxl,wlmx.wlmc,wlmx.wldw from 
(select t1.NAME wlmc,t2.NAME wldw,t3.NAME wlxl,SUBSTRING(t1.SORT_CODE,1,5)big_sortid from 
ALL_MATERIEL t1 inner join ALL_UNIT t2 
on t1.MAIN_UNIT_CODE=t2.CODE
inner join ALL_MATERIEL_SORT t3 on t1.SORT_CODE=t3.CODE
where t1.IS_USE=0)wlmx inner join ALL_MATERIEL_SORT on wlmx.big_sortid=ALL_MATERIEL_SORT.CODE

采购入库明细
SELECT 
    a.data_area,
	a.order_code,			
	a.order_time,
	b.NAME as base_name,
	c.SHORT_NAME AS supplier_name,
	a.materiel_code,
	a.NAME,
	m.name as sort_name,
	
	a.unit_name,				
	(case when unit_type=1 then NUMBER else MIN_NUMBER/unit_cf end) as number,				
	(case when MIN_NUMBER=0 then 0.00 else (case when unit_type=1 then total_money/NUMBER else total_money/(MIN_NUMBER/unit_cf) end) end) as price,
	total_money AS total_money
FROM VKC_CG_LIST a INNER JOIN
	KC_BASE b ON a.BASE_CODE = b.CODE INNER JOIN
	KC_SUPPLIER c ON a.supplier_code = c.CODE 
	INNER JOIN ALL_MATERIEL_SORT m on a.small_sort=m.code 
where a.is_check=1 and a.data_area like '00106' and 
((CONVERT(char(10),a.order_time, 120)>='2018-01-01') and 
(CONVERT(char(10),a.order_time, 120)<='2018-01-01'))
order by a.data_area,a.small_sort,a.sort_no,a.name,a.order_time




配送入库单
SELECT 
        a.data_area,
		a.order_code,
		a.order_time,
		
		isnull(b.name,'') as base_name,
		'总仓配送' supplier_name,
		a.materiel_code,				
		m.name as materiel_name,
		a.cllbname,
		u.name as unit_name,	
		a.crk_number/m.stock_unit_cf as crk_number, 
		case when a.crk_number<>0 then a.crk_money*m.stock_unit_cf/a.crk_number else 0.0 end as crk_price,
		a.crk_money 				
	FROM (
			SELECT 	
			a.DATA_AREA,
			materiel_code,
			src_order_code as order_code,
			cllb.NAME cllbname,	
			convert(char(18),check_time,120) as check_time,
			convert(char(10),order_time,120) as order_time,
			src_order_type as crk_type,
			is_rk,
			base_code,
			case when is_rk=1 then number else -number end as crk_number,
			case when is_rk=1 then total_cost else -total_cost end as crk_cost, 
			case when is_rk=1 then total_money else -total_money end as crk_money 
		FROM VKC_CRK_LISTS a 
		inner join all_materiel m on a.materiel_code=m.code 
		inner join ALL_MATERIEL_SORT cllb on m.SORT_CODE=cllb.CODE 
		WHERE (1=1)  and a.data_area like '00102' and 
		(order_day between 20180101 and 20181231 and  
		(src_order_type in (31)) and (is_rk in (1)) 
		) 						
	) as a 				
		INNER JOIN ALL_MATERIEL m on a.materiel_code=m.code 
		INNER JOIN ALL_UNIT u on m.stock_unit_code=u.code 			
		LEFT OUTER JOIN KC_BASE b on a.base_code=b.code  
		LEFT OUTER JOIN VKC_CRK_TYPE t on a.crk_type=t.code
	WHERE (1 = 1)  				
	ORDER BY a.order_time,a.check_time


内部调拨单
SELECT 
	a.data_area,
	a.src_base_name,
	a.obj_base_name,
	a.materiel_code,
	a.materiel_name,
	a.sort_name, 
	a.unit_name,
	a.number,
	a.price,
	a.total_money
FROM (
			SELECT
			a.data_area, 
            a.small_sort,
			a.sort_no,
			s.name as sort_name,
			a.src_base_code,
			a.obj_base_code,
			b.name as src_base_name,
			c.name as obj_base_name,
			a.materiel_code,
			a.NAME as materiel_name, 
			a.unit_name,
			(case when a.unit_type=1 then sum(a.NUMBER) else sum(a.MIN_NUMBER)/a.unit_cf end) as number,
			(case when sum(a.MIN_NUMBER)=0 then 0.00 else (case when unit_type=1 then sum(a.total_money)/sum(a.NUMBER) else sum(a.total_money)/(sum(a.MIN_NUMBER)/a.unit_cf) end) end) as price,
			sum(a.total_money) AS total_money
		FROM VKC_DB_LIST a INNER JOIN      		
  			KC_BASE b ON a.SRC_BASE_CODE = b.CODE INNER JOIN
  			KC_BASE c ON a.OBJ_BASE_CODE = c.CODE	
  		INNER JOIN all_MATERIEL_sort s on a.small_sort=s.code
		WHERE (a.IS_CHECK = 1)  and a.data_area like '00107' and 
		((CONVERT(char(10),a.order_time, 120)>='2018-01-01') and (CONVERT(char(10),a.order_time, 120)<='2018-01-01'))
		GROUP BY a.data_area,a.src_base_code,s.name,a.obj_base_code,b.name,c.name,a.small_sort,a.sort_no,
		a.materiel_code,a.name,a.unit_name,a.min_unit_name,a.unit_type,a.unit_cf			      				      									
	) as a
			INNER JOIN all_MATERIEL_sort sn on a.small_sort=sn.code
			inner join all_materiel_sort s on left(a.small_sort,5)=s.code
			WHERE (1=1) 
			ORDER BY a.src_base_name,a.obj_base_name,a.small_sort,a.sort_no