查看SL中客户订单信息

shijian.zhou

查看SL中客户订单信息
客户订单行对应的数据库表为coitem
客户订单对应的数据库表为co
客户信息对应的数据库表为custaddr仓库库存对应的数据库表为itemwhse
下面为SQL代码:
select ltrim(custaddr.cust_num) as 客户编号,custaddr.name as 客户名称,custaddr.addr##1 as 地址,co.cust_po as  客户订单
,itemwhse.qty_on_hand as N'在库量',itemwhse.qty_wip as N'在制品',
coitem.co_num as 订单号,coitem.co_line as 订单行,coitem.item as 物料,coitem.description as 名称,
coitem.qty_ordered as 合同数,coitem.qty_shipped as 发出数量,case when coitem.stat='P' then N'计划'
when coitem.stat='O' then N'订购'
when coitem.stat='F' then N'发完'
when coitem.stat='C' then N'完成'
else coitem.stat
end
as 状态,
co.contact as 联系人,co.phone as 电话,custaddr.fax_num as 传真,coitem.due_date as 到货日期
from Appdb.db_app.dbo.coitem coitem
left join Appdb.db_app.dbo.co co on co.co_num=coitem.co_num
left join Appdb.db_app.dbo.custaddr custaddr on custaddr.cust_num=co.cust_num and custaddr.cust_seq=co.cust_seq
left join Appdb.db_app.dbo.itemwhse itemwhse on coitem.item=itemwhse.item and itemwhse.whse = 'MAIN'
order by convert(int,coitem.co_line) asc