条码系统的 存储过程 示例

这个是sql sever的存储过程，以后要改成oracle的

手持式条码机批量扫描条码后，无线上传到服务器，服务器后台通过存储过程自动生产对应的资料。

USE [Barcode]

GO


/****** Object:  StoredProcedure [dbo].[ds_insertPURTG]    Script Date: 03/16/2016 09:23:28 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO


--把条码数据库的内容，进行转到ERP里去（实行一次行全部转移）
ALTER   PROCEDURE [dbo].[ds_insertPURTG] 
@optcode varchar(10)
AS
declare @TG001   CHAR(4),  --单别
@TG002  CHAR(11),  --单号
@TG003 CHAR(8),  --进货日期
@TG004 CHAR(6), --工厂编号
@TG005 CHAR(10) ,----供应商编号
@TG007  CHAR(4), --币种
@TG008 numeric(10,6),  --汇率
@TG009 char(1),  --发票种类
@TG010 CHAR(1),  --税种  1:内涵，2外加，其他：0
@TG021   CHAR(72),  --供应商全称
@TG030     numeric(5,4),  --税率
@TG033    char(6), --付款条件编号
@MF004  CHAR(10),
@createdate char(17), --进货创建日期
@TD001 CHAR(4),  --采购单别
@TD002 CHAR(11),--采购单号
@TD003 CHAR(4),--采购序号
@amount numeric(18,6), --本次送货数量
@amount1 numeric(18,6),  --本次验收数量
@boxAmount numeric(18,6),--本次送货包装数量
@boxAmount1 numeric(18,6),  --本次入库包装数量
@backAmount numeric(18,6),  --退回数量
@boxBackAmount numeric(18,6),  --退回包装数量
@JJAmount NUMERIC(18,6) ,--代表计价数量
@XH NUMERIC(9),  ---定义序号
@TH045 NUMERIC(11,2),   --原币税前金额 
@TH046 NUMERIC(11,2),   --原币税额
@TH047 NUMERIC(11,2),   --本币税前金额
@TH048 NUMERIC(11,2),   ---本币税额
@TA006 CHAR(10),     --检验人员
@DHID INT ,--到货ID
@YSID INT ,--验收ID
@QRID INT , --确认ID
@CC001 CHAR(4),
@CC002 CHAR(11),
@CC003 CHAR(8), --到货日期
@TA004 CHAR(8),--检验日期
@DHoptcode CHAR(10), --到货人员
@DHcreatedate CHAR(17), --到货创建日期
@YScreatedate CHAR(17),--验收创建日期
@amountpk  numeric(18,3),  --平库数量
@amountgk  numeric(18,3),   --高库数量
@TC001 CHAR(4),
@TC002 CHAR(11)
begin transaction
---先进行取得进货单别,再单据性质里，必须设置标识，UDF01='Y'则，进行取的
--SELECT @TG001=MQ001 FROM EGP..CMSMQ WHERE MQ003='34' AND UDF01='Y'  
        ---//原来条形码扫描生成ERP进货单，单别都是340，现在想分开，采购单别为330的生成进货单别340，采购单别为331的生成进货单别342
SELECT @CC001=MQ001 FROM EGP..CMSMQ WHERE MQ003='37' AND UDF01='Y'--取到货单别
--取得工厂信息
SELECT @TG004=MB001 FROM EGP..CMSMB
    --SELECT @TG003=CONVERT(CHAR(8),GETDATE(),112)
SELECT @MF004=MF004 FROM EGP..ADMMF WHERE MF001=@optcode
--select @createdate=replace(replace(replace(replace(CONVERT(CHAR(23),GETDATE(),121),'-',''),':',''),'.',''),' ','')



--开始定义游标，以供应商为导向
DECLARE MYCURSOR CURSOR FOR 
SELECT DISTINCT TC004,TC001,TC002  FROM barcode_PURTC WHERE status='0' AND [type]='确认' AND optcode=@optcode ORDER BY TC004


OPEN MYCURSOR
FETCH MYCURSOR INTO
@TG005,@TC001,@TC002
WHILE @@FETCH_STATUS=0
BEGIN
        IF  @TC001='330' OR @TC001='332'
BEGIN
SET @TG001='340'
END
IF  @TC001='331'
BEGIN
SET @TG001='342'
         END
--单别都是340，现在想分开，采购单别为330的生成进货单别340，采购单别为331的生成进货单别342
        --取确认,验收,到货ID
        SELECT  top 1 @QRID=[id],@YSID=relbillid FROM barcode_PURTC WHERE status='0' AND [type]='确认' AND optcode=@optcode 
        SELECT  @DHID=relbillid,@TA004=CONVERT(CHAR(8),optdatetime,112), @YScreatedate=replace(replace(replace(replace(CONVERT(CHAR(23),optdatetime,121),'-',''),':',''),'.',''),' ','') FROM barcode_PURTC WHERE id=@YSID 
        SELECT @TG003=CONVERT(CHAR(8),optdatetime,112),@createdate=replace(replace(replace(replace(CONVERT(CHAR(23),optdatetime,121),'-',''),':',''),'.',''),' ','') from barcode_PURTC WHERE id=@QRID  --取进货时间
        SELECT @CC003=CONVERT(CHAR(8),optdatetime,112),@DHoptcode=optcode,@DHcreatedate=replace(replace(replace(replace(CONVERT(CHAR(23),optdatetime,121),'-',''),':',''),'.',''),' ','') from barcode_PURTC WHERE id=@DHID  --取到货时间
SELECT @CC002=CAST(CAST(ISNULL(MAX(CC002),CONVERT(CHAR(8),GETDATE(),112)+'000') AS NUMERIC(11))+1  AS CHAR(11))   FROM EGP..PURCC WHERE CC003=CONVERT(CHAR(8),GETDATE(),112)


        --取检验人员  
SELECT  @TA006=optcode from barcode_PURTC WHERE id=@YSID
----取得单号 ---此种方式适合于编码为日遍的客户
SELECT @TG002=CAST(CAST(ISNULL(MAX(TG002),CONVERT(CHAR(8),GETDATE(),112)+'000') AS NUMERIC(11))+1  AS CHAR(11))   FROM EGP..PURTG WHERE  TG001=@TG001 and TG002 LIKE CONVERT(CHAR(8),GETDATE(),112)+'%'--使用TG003代替getdate(),避免因输入日期不一致,导致单号重复

SELECT @TG021=MA003,@TG009=MA030,@TG010=MA044,@TG030=MA064,@TG033=MA025 FROM EGP..PURMA WHERE MA001=@TG005  --@TG007=MA021供应商信息改为取采购订单
SELECT @TG007=TC005 FROM EGP..PURTC WHERE TC001=@TC001 AND TC002=@TC002  --20130225 修改.
select top 1 @TG008=ISNULL(MG004,1) from EGP..CMSMG WHERE MG001=@TG007 AND MG002<= CONVERT(CHAR(8),GETDATE(),112) ORDER BY MG002 DESC


--插入到货单
/*


INSERT EGP..PURCC
SELECT 'EGP',optcode,(SELECT MF004 FROM EGP..ADMMF WHERE MF001=optcode),@DHcreatedate,'','',1,@CC001,@CC002,@CC003,@TG004,
TC004,'',0,'','Y' AS CC009,@CC003,'',@TG021,0,0,optcode,'','','','',0,0,'N','','',0,'','N','','','','','','',0,0,0,0,0,0,
'','','','','','',0,0,0,0,0,0
FROM barcode_PURTC WHERE status='0' AND id=@DHID



--插入到货单单身
    INSERT EGP..PURCD
    SELECT 'EGP',optcode,(SELECT MF004 FROM EGP..ADMMF WHERE MF001=optcode),@DHcreatedate,'','',1,@CC001,@CC002,barcode_PURTD.TD003,
    TD004,TD005,TD006,TD009,amount1,0,TD001,TD002,barcode_PURTD.TD003,TD008,0,TD009,TD007,'',amount1,
    0,0,TD009,'',boxAmount1,0,0,0,0,0,0,0,0,0,'' AS CD033,'Y','',optcode,'','N','','',0,0,0,
    '','','','','','',0,0,0,0,0,0,'','','','','','',0,0,0,0,0,0
    FROM barcode_PURTC,barcode_PURTD WHERE barcode_PURTC.id=barcode_PURTD.billid AND barcode_PURTC.status='0' AND barcode_PURTC.id=@DHID
AND TC001=@TC001 AND TC002=@TC002*/




UPDATE barcode_PURTC SET status='1' WHERE [id]=@DHID

        --插入单头信息
INSERT EGP..PURTG
VALUES('EGP',@optcode,@MF004,@createdate,'','',1,@TG001,@TG002,@TG003,@TG004,@TG005,'',@TG007,@TG008,@TG009,@TG010,'',0,'N',@TG003,'N','',0,0,0,0,@TG021,'','1','N',0,0,'',0,'',@TG030,
0,0,@TG033,'','','','',0,0,0,0,'N','N','',0,0,'','','','N','','',0,0,0,'','',0,'','','','','','','',0,0,0,0,0,0,'','','','','','',0,0,0,0,0,0)
select @XH=1

                          ---进行循环单身插入
DECLARE MYDETAILCURSOR CURSOR FOR
SELECT TD001,TD002,barcode_PURTD.TD003,amount,amount1,boxAmount1,backAmount,boxBackAmount,JJAmount,amountpk,amountgk FROM barcode_PURTD,barcode_PURTC 
WHERE barcode_PURTD.billid=barcode_PURTC.id AND barcode_PURTC.status='0' AND [type]='确认' AND optcode=@optcode AND TC004=@TG005 AND TC001=@TC001
AND TC002=@TC002


OPEN MYDETAILCURSOR 
FETCH MYDETAILCURSOR INTO
@TD001,@TD002,@TD003,@amount,@amount1,@boxAmount1,@backAmount,@boxBackAmount,@JJAmount,@amountpk,@amountgk
WHILE @@FETCH_STATUS=0
BEGIN
  IF  @amountpk<>0 AND @amountgk<>0 
  BEGIN
--高库
INSERT EGP..PURTH
SELECT 'EGP',@optcode,@MF004,@createdate,'','',1,@TG001,@TG002,RIGHT('0000'+RTRIM(CAST(@XH AS CHAR(4))),4) AS TH003,TD004 AS TH004,TD005 AS TH005,TD006 AS TH006,@amountgk AS TH007,
TD009 AS TH008,TD007 AS TH009,TD021 AS TH010,TD001,TD002,TD003,@TG003,@amountgk,@amountgk,@backAmount,TD010 AS TH018,TD010*@amountgk AS TH019,0,'','','',   ---TH023
0,'','N','N' AS TH027,'2' AS TH028,'N','N','N','N',TD014,(CASE WHEN (EXISTS(SELECT * FROM EGP..INVMD WHERE MD001=TD004 AND MD002=TD009)) THEN @amountgk*(SELECT MD004/MD003 FROM EGP..INVMD WHERE MD001=TD004 AND MD002=TD009) ELSE @amountgk END) AS TH034,'','','','','','','','' AS TH042,'N','N', 
(CASE @TG010 WHEN '1' THEN TD010*@amountgk/(1+@TG030)  ELSE TD010*@amountgk END)  AS TH045,
(CASE @TG010 WHEN '1' THEN TD010*@amountgk/(1+@TG030)*@TG030  when '2' then TD010*@amountgk*@TG030 else 0 END)  AS TH046,
(CASE @TG010 WHEN '1' THEN TD010*@amountgk/(1+@TG030)  ELSE TD010*@amountgk END) *@TG008 as TH047 ,
(CASE @TG010 WHEN '1' THEN TD010*@amountgk/(1+@TG030)*@TG030  when '2' then TD010*@amountgk*@TG030 else 0 END) *@TG008 AS TH048,
@boxAmount,@boxAmount1,@boxBackAmount,0,TD032,0,0,'','',0,0,0 AS TH060,0,0,'',TD038,TD009,'','',0,0,'N',1,'gaoku','','','','',0,0,0,'N' AS TH080,
0,'2','','','','0001','0001','','','','','',TD201,0,0,0,0,0,0,'','','','','','',0,0,0,0,0,0 FROM 
EGP..PURTD WHERE TD001=@TD001 AND TD002=@TD002 AND TD003=@TD003
  ----直接进行插入检验单头档
            INSERT EGP..QMSTA
            SELECT  'EGP',@TA006,@MF004,@YScreatedate,'','',1,@TG001,@TG002,RIGHT('0000'+RTRIM(CAST(@XH AS CHAR(4))),4) AS TH003,@TG003,@TG003,@TA006,0,
            0,0,'','2','4',0,'Y' AS TA014,'','','','',@TG003,@TA006,'N',0,0,0,0,'0001','允收',1,@TG003,'',0,0,0,'N' AS TA034,'','','','','','',0 AS TA041,0,0,0,0 AS TA045,0,0,0,'' as TAC04,0,'',0,0,0,'','','','',TD021,TD201,0,0,0,0,0,0,
            '','','','','','',0,0,0,0,0,0 FROM EGP..PURTD WHERE TD001=@TD001 AND TD002=@TD002 AND TD003=@TD003

--平库
INSERT EGP..PURTH
SELECT 'EGP',@optcode,@MF004,@createdate,'','',1,@TG001,@TG002,RIGHT('0010'+RTRIM(CAST(@XH AS CHAR(4))),4) AS TH003,TD004 AS TH004,TD005 AS TH005,TD006 AS TH006,@amountpk AS TH007,
TD009 AS TH008,TD007 AS TH009,TD021 AS TH010,TD001,TD002,TD003,@TG003,@amountpk,@amountpk,@backAmount,TD010 AS TH018,TD010*@amountpk AS TH019,0,'','','',   ---TH023
0,'','N','N' AS TH027,'2' AS TH028,'N','N','N','N',TD014,(CASE WHEN (EXISTS(SELECT * FROM EGP..INVMD WHERE MD001=TD004 AND MD002=TD009)) THEN @amountpk*(SELECT MD004/MD003 FROM EGP..INVMD WHERE MD001=TD004 AND MD002=TD009) ELSE @amountpk END) AS TH034,'','','','','','','','' AS TH042,'N','N', 
(CASE @TG010 WHEN '1' THEN TD010*@amountpk/(1+@TG030)  ELSE TD010*@amountpk END)  AS TH045,
(CASE @TG010 WHEN '1' THEN TD010*@amountpk/(1+@TG030)*@TG030  when '2' then TD010*@amountpk*@TG030 else 0 END)  AS TH046,
(CASE @TG010 WHEN '1' THEN TD010*@amountpk/(1+@TG030)  ELSE TD010*@amountpk END) *@TG008 as TH047 ,
(CASE @TG010 WHEN '1' THEN TD010*@amountpk/(1+@TG030)*@TG030  when '2' then TD010*@amountpk*@TG030 else 0 END) *@TG008 AS TH048,
@boxAmount,@boxAmount1,@boxBackAmount,0,TD032,0,0,'','',0,0,0 AS TH060,0,0,'',TD038,TD009,'','',0,0,'N',1,'##########','','','','',0,0,0,'N' AS TH080,
0,'2','','','','0001','0001','','','','','',TD201,0,0,0,0,0,0,'','','','','','',0,0,0,0,0,0 FROM 
EGP..PURTD WHERE TD001=@TD001 AND TD002=@TD002 AND TD003=@TD003
  ----直接进行插入检验单头档
            INSERT EGP..QMSTA
            SELECT  'EGP',@TA006,@MF004,@YScreatedate,'','',1,@TG001,@TG002,RIGHT('0010'+RTRIM(CAST(@XH AS CHAR(4))),4) AS TH003,@TG003,@TG003,@TA006,0,
            0,0,'','2','4',0,'Y' AS TA014,'','','','',@TG003,@TA006,'N',0,0,0,0,'0001','允收',1,@TG003,'',0,0,0,'N' AS TA034,'','','','','','',0 AS TA041,0,0,0,0 AS TA045,0,0,0,'' as TAC04,0,'',0,0,0,'','','','',TD021,TD201,0,0,0,0,0,0,
            '','','','','','',0,0,0,0,0,0 FROM EGP..PURTD WHERE TD001=@TD001 AND TD002=@TD002 AND TD003=@TD003
      END 
ELSE
  IF  @amountpk=0 AND @amountgk<>0 
  BEGIN
--高库
INSERT EGP..PURTH
SELECT 'EGP',@optcode,@MF004,@createdate,'','',1,@TG001,@TG002,RIGHT('0000'+RTRIM(CAST(@XH AS CHAR(4))),4) AS TH003,TD004 AS TH004,TD005 AS TH005,TD006 AS TH006,@amountgk AS TH007,
TD009 AS TH008,TD007 AS TH009,TD021 AS TH010,TD001,TD002,TD003,@TG003,@amountgk,@amountgk,@backAmount,TD010 AS TH018,TD010*@amountgk AS TH019,0,'','','',   ---TH023
0,'','N','N' AS TH027,'2' AS TH028,'N','N','N','N',TD014,(CASE WHEN (EXISTS(SELECT * FROM EGP..INVMD WHERE MD001=TD004 AND MD002=TD009)) THEN @amountgk*(SELECT MD004/MD003 FROM EGP..INVMD WHERE MD001=TD004 AND MD002=TD009) ELSE @amountgk END) AS TH034,'','','','','','','','' AS TH042,'N','N', 
(CASE @TG010 WHEN '1' THEN TD010*@amountgk/(1+@TG030)  ELSE TD010*@amountgk END)  AS TH045,
(CASE @TG010 WHEN '1' THEN TD010*@amountgk/(1+@TG030)*@TG030  when '2' then TD010*@amountgk*@TG030 else 0 END)  AS TH046,
(CASE @TG010 WHEN '1' THEN TD010*@amountgk/(1+@TG030)  ELSE TD010*@amountgk END) *@TG008 as TH047 ,
(CASE @TG010 WHEN '1' THEN TD010*@amountgk/(1+@TG030)*@TG030  when '2' then TD010*@amountgk*@TG030 else 0 END) *@TG008 AS TH048,
@boxAmount,@boxAmount1,@boxBackAmount,0,TD032,0,0,'','',0,0,0 AS TH060,0,0,'',TD038,TD009,'','',0,0,'N',1,'gaoku','','','','',0,0,0,'N' AS TH080,
0,'2','','','','0001','0001','','','','','',TD201,0,0,0,0,0,0,'','','','','','',0,0,0,0,0,0 FROM 
EGP..PURTD WHERE TD001=@TD001 AND TD002=@TD002 AND TD003=@TD003
  ----直接进行插入检验单头档
            INSERT EGP..QMSTA
            SELECT  'EGP',@TA006,@MF004,@YScreatedate,'','',1,@TG001,@TG002,RIGHT('0000'+RTRIM(CAST(@XH AS CHAR(4))),4) AS TH003,@TG003,@TG003,@TA006,0,
            0,0,'','2','4',0,'Y' AS TA014,'','','','',@TG003,@TA006,'N',0,0,0,0,'0001','允收',1,@TG003,'',0,0,0,'N' AS TA034,'','','','','','',0 AS TA041,0,0,0,0 AS TA045,0,0,0,'' as TAC04,0,'',0,0,0,'','','','',TD021,TD201,0,0,0,0,0,0,
            '','','','','','',0,0,0,0,0,0 FROM EGP..PURTD WHERE TD001=@TD001 AND TD002=@TD002 AND TD003=@TD003
      END 
ELSE
BEGIN
INSERT EGP..PURTH
SELECT 'EGP',@optcode,@MF004,@createdate,'','',1,@TG001,@TG002,RIGHT('0000'+RTRIM(CAST(@XH AS CHAR(4))),4) AS TH003,TD004 AS TH004,TD005 AS TH005,TD006 AS TH006,@amount1 AS TH007,
TD009 AS TH008,TD007 AS TH009,TD021 AS TH010,TD001,TD002,TD003,@TG003,@amount1,@amount1,@backAmount,TD010 AS TH018,TD010*@JJAmount AS TH019,0,'','','',   ---TH023
0,'','N','N' AS TH027,'2' AS TH028,'N','N','N','N',TD014,(CASE WHEN (EXISTS(SELECT * FROM EGP..INVMD WHERE MD001=TD004 AND MD002=TD009)) THEN @amount1*(SELECT MD004/MD003 FROM EGP..INVMD WHERE MD001=TD004 AND MD002=TD009) ELSE @amount1 END) AS TH034,'','','','','','','','' AS TH042,'N','N', 
(CASE @TG010 WHEN '1' THEN TD010*@JJAmount/(1+@TG030)  ELSE TD010*@JJAmount END)  AS TH045,
(CASE @TG010 WHEN '1' THEN TD010*@JJAmount/(1+@TG030)*@TG030  when '2' then TD010*@JJAmount*@TG030 else 0 END)  AS TH046,
(CASE @TG010 WHEN '1' THEN TD010*@JJAmount/(1+@TG030)  ELSE TD010*@JJAmount END) *@TG008 as TH047 ,
(CASE @TG010 WHEN '1' THEN TD010*@JJAmount/(1+@TG030)*@TG030  when '2' then TD010*@JJAmount*@TG030 else 0 END) *@TG008 AS TH048,
@boxAmount,@boxAmount1,@boxBackAmount,0,TD032,0,0,'','',0,0,0 AS TH060,0,0,'',TD038,TD009,'','',0,0,'N',1,'##########','','','','',0,0,0,'N' AS TH080,
0,'2','','','','0001','0001','','','','','',TD201,0,0,0,0,0,0,'','','','','','',0,0,0,0,0,0 FROM 
EGP..PURTD WHERE TD001=@TD001 AND TD002=@TD002 AND TD003=@TD003
  ----直接进行插入检验单头档
            INSERT EGP..QMSTA
            SELECT  'EGP',@TA006,@MF004,@YScreatedate,'','',1,@TG001,@TG002,RIGHT('0000'+RTRIM(CAST(@XH AS CHAR(4))),4) AS TH003,@TG003,@TG003,@TA006,0,
            0,0,'','2','4',0,'Y' AS TA014,'','','','',@TG003,@TA006,'N',0,0,0,0,'0001','允收',1,@TG003,'',0,0,0,'N' AS TA034,'','','','','','',0 AS TA041,0,0,0,0 AS TA045,0,0,0,'' as TAC04,0,'',0,0,0,'','','','',TD021,TD201,0,0,0,0,0,0,
            '','','','','','',0,0,0,0,0,0 FROM EGP..PURTD WHERE TD001=@TD001 AND TD002=@TD002 AND TD003=@TD003


END


UPDATE barcode_PURTC 
SET barcode_PURTC.status='1' 
from barcode_PURTC, barcode_PURTD
WHERE barcode_PURTC.id = barcode_PURTD.billid and barcode_PURTD.TD001=@TD001 AND 
barcode_PURTD.TD002=@TD002 AND barcode_PURTD.TD003=@TD003 and barcode_PURTC.optcode = @optcode and
barcode_PURTC.status = '0' 


--UPDATE barcode_PURTC SET status='Y' WHERE TC001=@TD001 AND TC002=@TD02 AND 


SELECT @XH=@XH+1
FETCH NEXT FROM MYDETAILCURSOR INTO
@TD001,@TD002,@TD003,@amount,@amount1,@boxAmount1,@backAmount,@boxBackAmount,@JJAmount,@amountpk,@amountgk
END
CLOSE MYDETAILCURSOR
DEALLOCATE MYDETAILCURSOR
update barcode_PURTC SET status='1' WHERE status='0' AND optcode=@optcode AND TC004=@TG005 AND TC001=@TD001 AND TC002=@TD002 AND TC001=@TD001 AND TD003=@TD003 
---进行循环单身插入-
--进行更新单尾合计信息  进货金额,原币税额,总验收数量,原币货款金额,本币货款金额,本币税额,总验收包装量,总进货数量,总进货包装量
UPDATE EGP..PURTG SET TG017=(SELECT SUM(TH019) FROM  EGP..PURTH WHERE TG001=TH001 AND TG002=TH002 AND TH001=@TG001 AND TH002=@TG002) , 
TG019=(SELECT SUM(TH046) FROM  EGP..PURTH WHERE TG001=TH001 AND TG002=TH002 AND TH001=@TG001 AND TH002=@TG002) ,
TG026=(SELECT SUM(TH015)  FROM  EGP..PURTH WHERE TG001=TH001 AND TG002=TH002 AND TH001=@TG001 AND TH002=@TG002) ,
TG028=(SELECT SUM(TH045)  FROM  EGP..PURTH WHERE TG001=TH001 AND TG002=TH002 AND TH001=@TG001 AND TH002=@TG002) 
,TG031=(SELECT SUM(TH047)  FROM  EGP..PURTH WHERE TG001=TH001 AND TG002=TH002 AND TH001=@TG001 AND TH002=@TG002) ,
TG032=(SELECT SUM(TH048) FROM  EGP..PURTH WHERE TG001=TH001 AND TG002=TH002 AND TH001=@TG001 AND TH002=@TG002) 
,TG040=(SELECT SUM(TH050)  FROM  EGP..PURTH WHERE TG001=TH001 AND TG002=TH002 AND TH001=@TG001 AND TH002=@TG002) ,
TG053=(SELECT SUM(TH007)  FROM  EGP..PURTH WHERE TG001=TH001 AND TG002=TH002 AND TH001=@TG001 AND TH002=@TG002) 
,TG054=(SELECT SUM(TH049)  FROM  EGP..PURTH WHERE TG001=TH001 AND TG002=TH002 AND TH001=@TG001 AND TH002=@TG002) 
WHERE TG001=@TG001 AND TG002=@TG002
--更新到货单
--UPDATE EGP..PURCD SET CD018=(SELECT SUM(TH015) FROM EGP..PURTH WHERE CD001=THC03 AND CD002=THC04 AND CD003=THC05) WHERE CD001=@CC001 AND CD002=@CC002
--UPDATE EGP..PURCC SET CC013=(SELECT SUM(CD008) FROM EGP..PURCD WHERE CD001=CC001 AND CD002=CC002) WHERE CC001=@CC001 AND CC002=@CC002
FETCH NEXT FROM MYCURSOR INTO
@TG005,@TC001,@TC002
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR




commit transaction








GO