智领IT

3 Comments (+add yours?)

1. neco
   Nov 30, 2011 @ 16:35:20

   触发器-出货通知单单价为0不能保存 [非赠品数量]

   create trigger COPTO_U_I
   on COPTO
   for update,insert
   as
   declare
   @NewTO020 FLOAT;
   IF UPDATE(TO020) --后台用update更新COPTO表的时候如果没有这句会出错；
   BEGIN
   SELECT @NewTO020=TO020 FROM INSERTED WHERE TO024>0;
   IF
   @NewTO020=0
   BEGIN
   RAISERROR('非赠品单价不可为零，请重新录单维护单价',16,10)
   ROLLBACK TRANSACTION
   END
   END

   Reply

2. neco
   Nov 30, 2011 @ 16:33:51

   订单改税种为不计税.sql

   update COPTD SET TD037='0.00'
   WHERE TD001='2201' AND TD002='20110330005' --税率改为0
   update COPTD SET TD012=(TD008)*(TD011) --金额=数量*单价
   WHERE TD001='2201' AND TD002='20110307001' AND TD003='0001'
   update COPTD SET TD038=TD012 --税前金额=金额
   WHERE TD001='2201' AND TD002='20110307001' AND TD003='0001'
   update COPTD SET TD039=TD012-TD038 --税额=金额-税前金额
   WHERE TD001='2201' AND TD002='20110307001' AND TD003='0001'

   UPDATE COPTC SET TC029=(SELECT SUM(TD038) --单身税前金额汇总更新单尾订单金额
   FROM COPTD WHERE TD001=TC001 AND TD002=TC002 )
   UPDATE COPTC SET TC030=(SELECT SUM(TD039) --单身税额汇总更新单尾订单税额
   FROM COPTD WHERE TD001=TC001 AND TD002=TC002)

   Reply

3. neco
   Nov 30, 2011 @ 16:33:12

   销货单更新单身汇总单尾(含税).sql

   select TJ001,TJ002,TJ003,TJ007 AS 数量,TJ011 AS 单价,TJ012 AS 金额,TJ031 AS 原币税前金额,
   TJ032 AS 原币税额,TJ033 AS 本币税前金额,TJ034 AS 本币税额

   --销退单身汇总更新到单尾
   UPDATE COPTI SET TI010= (SELECT SUM(TJ031) --更新 单尾原币销退金额 = 单身原币税前金额汇总；
   FROM COPTJ
   WHERE TI001=TJ001 AND TI002=TJ002)

   UPDATE COPTI SET TI011= (SELECT SUM(TJ032) --更新 单尾原币销退税额 = 单身原币税额汇总；
   FROM COPTJ
   WHERE TI001=TJ001 AND TI002=TJ002)

   UPDATE COPTI SET TI037= (SELECT SUM(TJ033) --更新 单尾本币销退金额 = 单身本币税前金额汇总；
   FROM COPTJ
   WHERE TI001=TJ001 AND TI002=TJ002)

   UPDATE COPTI SET TI038= (SELECT SUM(TJ034) --更新 单尾本币销退税额 = 单身本币税额汇总；
   FROM COPTJ
   WHERE TI001=TJ001 AND TI002=TJ002)

   --销退单价*数量=相应金额 【仅针对本位币 RMB】

   UPDATE COPTJ SET TJ011='4.1' --维护单价
   where TJ001='2402' AND TJ002='20110507001' AND TJ003='0003'

   update COPTJ set TJ012=(TJ011)*(TJ007) --金额=单价*数量
   where TJ001='2402' AND TJ002='20110507001' AND TJ003='0003'

   update COPTJ set TJ031=(TJ012)/(1+0.17) --原币税前金额=金额/（1+税率）
   where TJ001='2402' AND TJ002='20110507001' AND TJ003='0003'

   update COPTJ set TJ032=(TJ012)-(TJ031) --原币税额=金额-原币税前金额
   where TJ001='2402' AND TJ002='20110507001' AND TJ003='0003'

   update COPTJ set TJ033=TJ031 --本币税前金额=原币税前金额
   where TJ001='2402' AND TJ002='20110507001' AND TJ003='0003'

   update COPTJ set TJ034=TJ032 --本币税额=原币税额
   where TJ001='2402' AND TJ002='20110507001' AND TJ003='0003'

   销货单如果更新核价，严谨角度，还应在 录入客户商品价格档，修改该记录的单价。

   Reply