K3基础表结构培训

for update
as
begin
--if update(fauxprice)
--begin
--raiserror('22',18,18)
declare @FInterID int ,@Famount decimal(18,2),@FAmountCHr varchar(50)
select @FInterID=finterid from inserted
select @Famount=sum(Famount) from seorderentry where finterid=@FInterID
set @FAmountCHr =convert(varchar(50),@Famount)
select * from t_submestype
select * from t_SubMessage where ftypeid=21 --取表t_submestype中的ftypeid值查询
--查询k3新单据表
select * from ICClassType
--查询表字段信息
select * from ICClassTableInfo where fclasstypeid=1000003 --取表ICClassType中的FID值查询
--查询老单表信息
select * from ictransactiontype where fname='销售订单'
--查询老单表头字段信息
inner join t_icitem t3 on t3.fitemid=t2.fitemid
WHERE FBillno like '%'+@FBillNo+'%' AND t3.fnumber like '%'+@FNumber+'%'
end
select * from ICInventory --即时库存表
inner join t_icitem t3 on t2.FitemID=t3.FitemID
where fbillno='SEORD000086' and t3.fnumber='1.01.01.001'
--触发器编写
ALTER trigger [dbo].[updateEntry]
on [dbo].[seorderEntry]
select * from ICStockBill where ftrantype=1 --ftrantype值取表ictransactiontype中FID值 外购入库单
--查询
select t1.fbillno,t4.fnumber ,t4.fname,t2.fauxqty as 订单数量,t3.fqty as 发货数量
--核算项目字段信息 例如你要查询客户中【电话】在数据库后台是哪个字段，就可以查询下面这张表
select * from t_ItemPropDesc where FItemClassID=1 --FItemClassID取t_ItemClass中FItemClassID值
--辅助资料
--raiserror(@FAmountCHr,18,18)
update seorder set FHeadSelfS0157=@famount where finterid=@FInterID
--end
end
--存储过程编写
CREATE proc [dbo].[HZDW_Rpt_SaleInfo] ቤተ መጻሕፍቲ ባይዱ
from seorder t1 inner join seorderentry t2 on t1.finterid=t2.finterid
inner join t_icitem t4 on t4.fitemid=t2.fitemid
inner join seoutstockentry t3 on t3.fsourceinterid=t2.finterid and t3.fsourceentryid=t2.fentryid
@FBillNo varchar(50),
@FNumber varchar(50)
as
begin
select FBillno as '销售订单号' ,t3.FNumber 物料代码,t3.FName 物料名称,
FauxQty 数量 from seorder t1 inner join seorderentry t2 on t1.finterid=t2.finterid
--核算项目
select * from t_ItemClass
--核算项目内容
select * from t_item where fitemclassid=1
select * from t_Organization --客户信息FItemClassid=1
select * from t_Department --部门信息FItemClassid=2
select * from ictemplate where fid='s01' --FID就是表ictransactiontype中的FTemplateID
--查询老单表体字段信息
select * from ictemplateEntry where fid='s01'
select * from ICStockBill where ftrantype=21 --ftrantype值取表ictransactiontype中FID值 销售出库单
--修改，
update t1 set FCustID=386
from seorder t1 inner join t_Organization t2 on t1.FCustID=t2.fitemid
where fname='联泰'
--删除操作
delete t2 from seorder t1 inner join seorderentry t2 on t1.finterid=t2.finterid