U8触发器
来源:互联网 发布:windows nt 4.0补丁包 编辑:程序博客网 时间:2024/05/18 04:00
USE [UFDATA_002_2016]
GO
/****** Object: Trigger [dbo].[[tri_update_SO_SOMain_extradefine]] Script Date: 01/25/2017 11:32:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[tri_update_SO_SOMain_extradefine]
on [dbo].[SO_SOMain_extradefine]
for update
as
--if UPDATE (iQuotedPrice)
GO
/****** Object: Trigger [dbo].[[tri_update_SO_SOMain_extradefine]] Script Date: 01/25/2017 11:32:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[tri_update_SO_SOMain_extradefine]
on [dbo].[SO_SOMain_extradefine]
for update
as
--if UPDATE (iQuotedPrice)
SELECT * FROM SO_SOMain_extradefine
declare @tpcDepCode varchar(20)
DECLARE @CODE varchar(20)
SELECT @CODE = cSOCode FROM inserted a inner join SO_SOMain b
on a.ID = B.ID
declare @tpcDepCode varchar(20)
DECLARE @CODE varchar(20)
SELECT @CODE = cSOCode FROM inserted a inner join SO_SOMain b
on a.ID = B.ID
select @tpcDepCode = cDepCode from SO_SOMain WHERE cSOCode = @CODE
if (@tpcDepCode like '106%')
if (@tpcDepCode like '106%')
BEGIN
SET NOCOUNT ON;
declare @iNatSum numeric(15,2) --本币价税合计
declare @iMoney numeric(15,2) --原币无税金额
declare @iSum numeric(15,2) --原币价税合计
declare @iNatMoney numeric(15,2) --本币无税金额
declare @iUnitPrice numeric(15,2) --原币无税单价
declare @iTaxUnitPrice numeric(15,2) --原币含税单价
declare @iNatUnitPrice numeric(15,2) --本币无税单价
declare @iDisCount numeric(15,2) --原币折扣额
declare @iNatDisCount numeric(15,2) --本币折扣额
declare @KL numeric(15,2) --扣率
declare @CBDEFINE6 numeric(15,2) --自定义6 折后价格
declare @CBDEFINE7 numeric(15,2) --累计折扣额
declare @CBDEFINE8 numeric(15,2) --折扣率
declare @CBDEFINE9 numeric(15,5) --折后单价
declare @isoid varchar(20) --子表与自定义表关联ID
declare @cSOCode varchar(20) --销售订单号
declare @e varchar(20)
-- select @isoid = A.iSOsID , @CBDEFINE6 = CBDEFINE6,@CBDEFINE7 = CBDEFINE7 , @CBDEFINE8 = CBDEFINE8 ,@CBDEFINE9 = CBDEFINE9 from [SO_SODetails_extradefine] a inner join SO_SODetails b
--on a.iSOsID = b.ID where B.cSOCode = @CODE
--select @isoid = iSOsID from inserted b
declare @iMoney numeric(15,2) --原币无税金额
declare @iSum numeric(15,2) --原币价税合计
declare @iNatMoney numeric(15,2) --本币无税金额
declare @iUnitPrice numeric(15,2) --原币无税单价
declare @iTaxUnitPrice numeric(15,2) --原币含税单价
declare @iNatUnitPrice numeric(15,2) --本币无税单价
declare @iDisCount numeric(15,2) --原币折扣额
declare @iNatDisCount numeric(15,2) --本币折扣额
declare @KL numeric(15,2) --扣率
declare @CBDEFINE6 numeric(15,2) --自定义6 折后价格
declare @CBDEFINE7 numeric(15,2) --累计折扣额
declare @CBDEFINE8 numeric(15,2) --折扣率
declare @CBDEFINE9 numeric(15,5) --折后单价
declare @isoid varchar(20) --子表与自定义表关联ID
declare @cSOCode varchar(20) --销售订单号
declare @e varchar(20)
-- select @isoid = A.iSOsID , @CBDEFINE6 = CBDEFINE6,@CBDEFINE7 = CBDEFINE7 , @CBDEFINE8 = CBDEFINE8 ,@CBDEFINE9 = CBDEFINE9 from [SO_SODetails_extradefine] a inner join SO_SODetails b
--on a.iSOsID = b.ID where B.cSOCode = @CODE
--select @isoid = iSOsID from inserted b
-- select @e = ippartid from SO_SODetails
-- if (@e is not null)
--PRINT @E
-- ---销售订单主表 SO_SOMain
-----销售订单子表SO_SODetails
----自定义字段表 SO_SODetails_ExtraDefine
---- select * from SO_SOMain
----select * from SO_SODetails
--ELSE
--BEGIN
--select @isoid = iSOsID , @CBDEFINE6 = CBDEFINE6 from inserted
--update SO_SODetails set iNatSum = @CBDEFINE6 ,iMoney = @CBDEFINE6,iSum = @CBDEFINE6,iNatMoney = @CBDEFINE6 where iSOsID = @isoid
-- select @CBDEFINE7 = CBDEFINE7 from inserted
-- update SO_SODetails set iDisCount = @CBDEFINE7,iNatDisCount = @CBDEFINE7 where iSOsID = @isoid
-- select @CBDEFINE8 = CBDEFINE8 from inserted
-- update SO_SODetails set KL = @CBDEFINE8 where iSOsID = @isoid
-- select @CBDEFINE9 = CBDEFINE9 from inserted
-- update SO_SODetails set iUnitPrice = @CBDEFINE9 , iTaxUnitPrice = @CBDEFINE9 , iNatUnitPrice = @CBDEFINE9 where iSOsID = @isoid
--END
DECLARE @socode varchar(20)
DECLARE @actID varchar(10)
select * from SO_SOMain_extradefine
DECLARE @actID varchar(10)
select * from SO_SOMain_extradefine
SET @socode = @CODE
--SELECT A.ID,a.cSOCode , * FROM SO_SOMain A INNER JOIN SO_SODetails B
--ON A.ID = B.ID
--WHERE A.cSOCode LIKE '234141231231'
--if( @actID = '000002' and @socode is not null)
-- begin
-- begin
--IF EXISTS(SELECT * FROM tpjjtable)
if object_id('tpjjtable','U') is not null
drop table tpjjtable
--create table #tpjjtable
--( socode varchar(20) ,
-- quantity int ,
-- price numeric(10,2),
-- total numeric(10,2)
-- )
if object_id('tpjjtable','U') is not null
drop table tpjjtable
--create table #tpjjtable
--( socode varchar(20) ,
-- quantity int ,
-- price numeric(10,2),
-- total numeric(10,2)
-- )
declare @id varchar(10)
declare @total_price numeric(10,2)
declare @allprice numeric(10,0)
declare @d2 int
declare @d3 numeric(6,2)
declare @d4 numeric(6,2)
declare @d5 numeric(6,2)
declare @d6 numeric(6,2)
declare @c numeric(6,6)
SELECT @id = A.ID FROM SO_SOMain A INNER JOIN SO_SODetails B
ON A.ID = B.ID
WHERE A.cSOCode LIKE @socode
ON A.ID = B.ID
WHERE A.cSOCode LIKE @socode
SELECT A.ID,A .cSOCode ,b.iQuantity ,b.iQuotedPrice , (b.iQuantity *b.iQuotedPrice) as total into tpjjtable FROM SO_SOMain A INNER JOIN SO_SODetails B
ON A.ID = B.ID
WHERE A.cSOCode LIKE @socode and cInvCode not like '99%'--'234141231231'
select * from tpjjtable
ON A.ID = B.ID
WHERE A.cSOCode LIKE @socode and cInvCode not like '99%'--'234141231231'
select * from tpjjtable
select @d2 = chdefine2 , @d3 = chdefine3, @d4 = chdefine4,@d5 = chdefine5, @d6 = chdefine6 from SO_SOMain_extradefine where ID = @id --1000000226
select @total_price = SUM(total) from tpjjtable
set @allprice = ( @total_price * @d3 - @d2 ) * @d4 * @d5 --- @d6
print @allprice
select @total_price = SUM(total) from tpjjtable
set @allprice = ( @total_price * @d3 - @d2 ) * @d4 * @d5 --- @d6
print @allprice
set @c = @allprice / @total_price
print @c
update SO_SODetails set KL = @c where cSOCode = @socode
--select * from #tpjjtable
--select * from #tpjjtable
update SO_SODetails set iUnitPrice = iQuotedPrice *@c ,iTaxUnitPrice = iQuotedPrice *@c ,iNatUnitPrice = iQuotedPrice *@c where cSOCode = @CODE
update SO_SODetails set iSum = iUnitPrice *iQuantity , iMoney = iUnitPrice *iQuantity ,
iNatMoney = iUnitPrice *iQuantity , iUnitPrice = iQuotedPrice *iQuantity
where cSOCode = @CODE
iNatMoney = iUnitPrice *iQuantity , iUnitPrice = iQuotedPrice *iQuantity
where cSOCode = @CODE
--数量*报价-数量*含税单价
update SO_SODetails set iNatDisCount = ((iQuantity * iQuotedPrice) - (iQuantity*iTaxUnitPrice )),
iDisCount = ((iQuantity * iQuotedPrice) - (iQuantity*iTaxUnitPrice ))
where cSOCode = @CODE
COMMIT
--update SO_SODetails set iNatSum = (KL * total) from SO_SODetails a
--inner join tpjjtable b
--on a.AutoID = b.ID
--inner join tpjjtable b
--on a.AutoID = b.ID
IF EXISTS(SELECT * FROM tpjjtable)
drop table tpjjtable
END
0 0
- U8触发器
- U8使用手册
- U8 介绍
- U8 搭建
- U8集成
- U8"字符串"(u8前缀)的作用
- U8成本管理应用
- 如何使用U8。9
- u8维护_进销存
- U8 系统篇
- 认识用友U8-HR
- u8导出入库记录
- U8常见问题汇总
- U8工具栏变窄
- U8系列数据库字典
- U8不能自动备份
- 后台修改u8用户密码
- U8常见问题汇总
- Android Studio 代码混淆
- [LeetCode]524. Longest Word in Dictionary through Deleting
- ExpressBox 4 (1U)
- 【图像特征提取5】ORB特征提取-----FAST角点检测
- Hibernate中get与load
- U8触发器
- Android BugList
- Freeradius证书认证模式配置
- svn利用钩子脚本功能实现代码同步到web目录
- IE8兼容js原生函数问题(举例map,filter,indexof)
- JQuery操作html实现<li>标签上移和下移效果
- 读书笔记--策略模式
- asp 图片添加水印
- swift3 函数方法