3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
关于locking问题
我的程序功能是先在pb部分insert到表1里面,然后表1里自动执行trigger存到另外一个表2里面。
程序执行后出现locking问题。
我为了找问题把程序分了两个部分.
1。我把表1的trigger删掉(注释），执行程序，在pb里面insert到表1部分没有出现问题。
2. 我使用sql profile 截获了pb里面insert到表1部分的insert sql内容，然后在sql analysis运行。在表1里insert成功，表2里也insert成功。
做第2步的时候,我在trigger里面 加了很多 select '1', select @变量 等程序. 以便追踪trigger里面的传递信息, 都没有出现问题。
但是1和2加起来运行出现你的程序正在执行中的信息框,也就是被锁住(这时我不能在sql enterprise里查看表1的内容)
注: pb=power builder
程序执行后出现locking问题。
我为了找问题把程序分了两个部分.
1。我把表1的trigger删掉(注释），执行程序，在pb里面insert到表1部分没有出现问题。
2. 我使用sql profile 截获了pb里面insert到表1部分的insert sql内容，然后在sql analysis运行。在表1里insert成功，表2里也insert成功。
做第2步的时候,我在trigger里面 加了很多 select '1', select @变量 等程序. 以便追踪trigger里面的传递信息, 都没有出现问题。
但是1和2加起来运行出现你的程序正在执行中的信息框,也就是被锁住(这时我不能在sql enterprise里查看表1的内容)
注: pb=power builder
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
kimcz 2003-01-10
从早上10点开始企图进入csdn，直到现在才成功
非常感谢mousean(幻想无限)
set nocount on --加这一句 没有效果
其实能存数据，只能存一个数据(能存到表2里面），同时被锁住。
但pb开始insert时候并不是一个.
非常感谢mousean(幻想无限)
set nocount on --加这一句 没有效果
其实能存数据，只能存一个数据(能存到表2里面），同时被锁住。
但pb开始insert时候并不是一个.
j9988 2003-01-09
晕死!
1.首先把那些调试语句去了。
2.把insert 内的变量用前面的SELECT语句替代
3.最后改成表链接方式，insert .....select ..from tableA A,tableB B,....where .....
另：
触发器调试：可把插入的记录SELECT到时临表#T，
然后把以上代码，放到分析器中运行，只要把INSERTED表改成#T就行了，看看那出问题了。
这种代码很难帮上你的忙。我想你自已可能都晕了:)
1.首先把那些调试语句去了。
2.把insert 内的变量用前面的SELECT语句替代
3.最后改成表链接方式，insert .....select ..from tableA A,tableB B,....where .....
另：
触发器调试：可把插入的记录SELECT到时临表#T，
然后把以上代码，放到分析器中运行，只要把INSERTED表改成#T就行了，看看那出问题了。
这种代码很难帮上你的忙。我想你自已可能都晕了:)
昵称被占用了 2003-01-09
试一下：
CREATE TRIGGER TI_SMISLEF_temp
ON SMISLEF_temp
FOR INSERT
AS
set nocount on --加这一句，其他的不变
...
CREATE TRIGGER TI_SMISLEF_temp
ON SMISLEF_temp
FOR INSERT
AS
set nocount on --加这一句，其他的不变
...
mouseanAnya 2003-01-09
UPDATE SCA020T
SET ORD_STATUS = '9',
CLOSE_FLAG = 'Y',
CLOSE_DT = GETDATE() ,
UPDT_EMP_NO = @UPDT_EMP_NO,
UPDT_DT = @UPDT_DT
WHERE ORG_CD = @ORG_CD
AND ORD_NO = @ORD_NO
AND ITEM_CD = @ITEM_CD
AND DVRY_DT = @DVRY_DT
IF (@@ERROR <> 0)
BEGIN
RAISERROR('UPDATE error', 16, -1, @@ERROR)
ROLLBACK TRANSACTION
RETURN
END
END
ELSE
IF @ROWCOUNT > 1
BEGIN
SELECT TOP 1 @ORD_NO = A.ORD_NO, @ORD_DT = A.ORD_DT, @ORD_QTY = B.ORD_QTY
FROM SCA010T A LEFT OUTER JOIN SCA020T B ON A.ORD_NO = B.ORD_NO
WHERE A.ORG_CD = B.ORG_CD
AND A.ORG_CD = @ORG_CD
AND B.ITEM_CD = @ITEM_CD
AND B.ORD_STATUS <> '9'
AND B.CLOSE_FLAG <> 'Y'
AND CONVERT(CHAR(10), B.DVRY_DT, 120) = CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
ORDER BY A.ORD_DT
END
return
select '5'
EXIT_ROUTIN:
UPDATE SMISLEF_TEMP
SET REASON = @ERR_MSG
WHERE FHAFMON = @FHAFMON
AND FITEMCD = @FITEMCD
AND FORDNO = @FORDNO
select '6'
SET ORD_STATUS = '9',
CLOSE_FLAG = 'Y',
CLOSE_DT = GETDATE() ,
UPDT_EMP_NO = @UPDT_EMP_NO,
UPDT_DT = @UPDT_DT
WHERE ORG_CD = @ORG_CD
AND ORD_NO = @ORD_NO
AND ITEM_CD = @ITEM_CD
AND DVRY_DT = @DVRY_DT
IF (@@ERROR <> 0)
BEGIN
RAISERROR('UPDATE error', 16, -1, @@ERROR)
ROLLBACK TRANSACTION
RETURN
END
END
ELSE
IF @ROWCOUNT > 1
BEGIN
SELECT TOP 1 @ORD_NO = A.ORD_NO, @ORD_DT = A.ORD_DT, @ORD_QTY = B.ORD_QTY
FROM SCA010T A LEFT OUTER JOIN SCA020T B ON A.ORD_NO = B.ORD_NO
WHERE A.ORG_CD = B.ORG_CD
AND A.ORG_CD = @ORG_CD
AND B.ITEM_CD = @ITEM_CD
AND B.ORD_STATUS <> '9'
AND B.CLOSE_FLAG <> 'Y'
AND CONVERT(CHAR(10), B.DVRY_DT, 120) = CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
ORDER BY A.ORD_DT
END
return
select '5'
EXIT_ROUTIN:
UPDATE SMISLEF_TEMP
SET REASON = @ERR_MSG
WHERE FHAFMON = @FHAFMON
AND FITEMCD = @FITEMCD
AND FORDNO = @FORDNO
select '6'
mouseanAnya 2003-01-09
SELECT @SER_NO1 = 1
SELECT @DVRY_DT = @PROD_DVRY_DT
SELECT @ORD_STATUS = '1'
SELECT @ORD_UNIT = (SELECT INV_UNIT FROM BAA010T WHERE ITEM_CD = @ITEM_CD)
SELECT @ORD_UNIT = @ORD_UNIT
SELECT @ROWCOUNT = COUNT(*)
FROM SCA010T A LEFT OUTER JOIN SCA020T B ON A.ORD_NO = B.ORD_NO
WHERE A.ORG_CD = B.ORG_CD
AND A.ORG_CD = @ORG_CD
AND B.ITEM_CD = @ITEM_CD
AND B.ORD_STATUS <> '9'
-- AND B.CLOSE_FLAG <> 'Y'
AND CONVERT(CHAR(10), B.DVRY_DT, 120) = CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
SELECT @ROWCOUNT
SELECT @PROD_DVRY_DT
SELECT CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = @ITEM_CD + '-' + @ORG_CD + ' - ' + CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
GOTO EXIT_ROUTIN
END
IF @ROWCOUNT = 1
BEGIN
SELECT @ORD_NO = A.ORD_NO, @ORD_DT = A.ORD_DT, @ORD_QTY = B.ORD_QTY
FROM SCA010T A LEFT OUTER JOIN SCA020T B ON A.ORD_NO = B.ORD_NO
WHERE A.ORG_CD = B.ORG_CD
AND A.ORG_CD = @ORG_CD
AND B.ITEM_CD = @ITEM_CD
AND B.ORD_STATUS <> '9'
AND CONVERT(CHAR(10), B.DVRY_DT, 120) = CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
select '4.3'
select @ord_no
select @ord_dt
select @ord_qty
select @org_cd
select @item_cd
select @PROD_DVRY_DT
select CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
SELECT @ORD_PRICE = ITEM_PRICE
FROM SAA010T
WHERE ORG_CD = @ORG_CD
AND CUST_CD = @CUST_CD
AND ITEM_CD = @ITEM_CD
AND CONVERT(VARCHAR(10),APPLY_STRT_DT,120) <= CONVERT(VARCHAR(10),@ORD_DT,120)
AND CONVERT(VARCHAR(10),APPLY_FNSH_DT,120) >= CONVERT(VARCHAR(10),@ORD_DT,120)
select '4.4'
select CONVERT(VARCHAR(10),@ORD_DT,112)
select @ORD_DT
select @cust_cd
select @item_cd
select @org_cd
SELECT @ORD_PRICE
SELECT @ORD_PRICE = ISNULL(@ORD_PRICE, 0)
SELECT @ORD_AMT = @FSLEQTY * @ORD_PRICE
SELECT @ORD_AMT1 = @FSLEQTY * @ORD_PRICE
select '4.5'
SELECT @ORD_PRICE
SELECT @ORD_AMT
SELECT @ORD_AMT1
SELECT @SUBUL = 'SB' + '0000000000001'
SELECT @SUBUL1 = (SELECT MAX(TRANS_NO) FROM SDA010T WHERE ORG_CD = @ORG_CD AND TRANS_NO LIKE 'SB%')
IF @SUBUL <= @SUBUL1
BEGIN
SELECT @SUBUL = LEFT(@SUBUL1,2) + REPLACE(STR(SUBSTRING(@SUBUL1,3, LEN(@SUBUL1) - 2 ) + 1,13,0),' ', '0')
END
ELSE
BEGIN
SELECT @SUBUL = @SUBUL
END
select '4.7'
INSERT INTO SDA010T
VALUES
( @ORG_CD,
'2',
@SUBUL,
@ITEM_CD,
@PROD_DVRY_DT,
'6',
'6',
'01',
'1',
@CUST_CD,
'B02',
@FSLEQTY ,
@ORD_PRICE,
@ORD_AMT1,
@ORD_AMT1,
'KRW',
1,
@FSLEQTY ,
@ORD_NO,
1,
NULL,
0,
0,
NULL,
NULL,
@ORD_PRSN,
@ORD_DEPT,
@ISRT_EMP_NO,
@ISRT_DT,
@UPDT_EMP_NO,
@UPDT_DT,
0,
0,
NULL,
'01',
@COLET_TYPE,
0,
@FSLEQTY ,
'1',
'*',
0,
0,
1,
0,
0,
0,
NULL,
NULL,
@CUST_CD,
@AVR_PRICE,
NULL,
@CUST_CD,
NULL,
@ORD_UNIT,
@CUST_CD,
1,
'01',
0,
0,
0,
0,
'*',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0,
0,
'*',
@AVR_PRICE * @FSLEQTY ,
'*',
'*',
'1',
@CRT_INTERNAL_CD,
0,
10,
NULL,
NULL,
0,
@ORG_CD
)
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
RAISERROR('UPDATE error', 16, -1, @@ERROR)
ROLLBACK TRANSACTION
RETURN
END
SELECT @DVRY_DT = @PROD_DVRY_DT
SELECT @ORD_STATUS = '1'
SELECT @ORD_UNIT = (SELECT INV_UNIT FROM BAA010T WHERE ITEM_CD = @ITEM_CD)
SELECT @ORD_UNIT = @ORD_UNIT
SELECT @ROWCOUNT = COUNT(*)
FROM SCA010T A LEFT OUTER JOIN SCA020T B ON A.ORD_NO = B.ORD_NO
WHERE A.ORG_CD = B.ORG_CD
AND A.ORG_CD = @ORG_CD
AND B.ITEM_CD = @ITEM_CD
AND B.ORD_STATUS <> '9'
-- AND B.CLOSE_FLAG <> 'Y'
AND CONVERT(CHAR(10), B.DVRY_DT, 120) = CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
SELECT @ROWCOUNT
SELECT @PROD_DVRY_DT
SELECT CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = @ITEM_CD + '-' + @ORG_CD + ' - ' + CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
GOTO EXIT_ROUTIN
END
IF @ROWCOUNT = 1
BEGIN
SELECT @ORD_NO = A.ORD_NO, @ORD_DT = A.ORD_DT, @ORD_QTY = B.ORD_QTY
FROM SCA010T A LEFT OUTER JOIN SCA020T B ON A.ORD_NO = B.ORD_NO
WHERE A.ORG_CD = B.ORG_CD
AND A.ORG_CD = @ORG_CD
AND B.ITEM_CD = @ITEM_CD
AND B.ORD_STATUS <> '9'
AND CONVERT(CHAR(10), B.DVRY_DT, 120) = CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
select '4.3'
select @ord_no
select @ord_dt
select @ord_qty
select @org_cd
select @item_cd
select @PROD_DVRY_DT
select CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
SELECT @ORD_PRICE = ITEM_PRICE
FROM SAA010T
WHERE ORG_CD = @ORG_CD
AND CUST_CD = @CUST_CD
AND ITEM_CD = @ITEM_CD
AND CONVERT(VARCHAR(10),APPLY_STRT_DT,120) <= CONVERT(VARCHAR(10),@ORD_DT,120)
AND CONVERT(VARCHAR(10),APPLY_FNSH_DT,120) >= CONVERT(VARCHAR(10),@ORD_DT,120)
select '4.4'
select CONVERT(VARCHAR(10),@ORD_DT,112)
select @ORD_DT
select @cust_cd
select @item_cd
select @org_cd
SELECT @ORD_PRICE
SELECT @ORD_PRICE = ISNULL(@ORD_PRICE, 0)
SELECT @ORD_AMT = @FSLEQTY * @ORD_PRICE
SELECT @ORD_AMT1 = @FSLEQTY * @ORD_PRICE
select '4.5'
SELECT @ORD_PRICE
SELECT @ORD_AMT
SELECT @ORD_AMT1
SELECT @SUBUL = 'SB' + '0000000000001'
SELECT @SUBUL1 = (SELECT MAX(TRANS_NO) FROM SDA010T WHERE ORG_CD = @ORG_CD AND TRANS_NO LIKE 'SB%')
IF @SUBUL <= @SUBUL1
BEGIN
SELECT @SUBUL = LEFT(@SUBUL1,2) + REPLACE(STR(SUBSTRING(@SUBUL1,3, LEN(@SUBUL1) - 2 ) + 1,13,0),' ', '0')
END
ELSE
BEGIN
SELECT @SUBUL = @SUBUL
END
select '4.7'
INSERT INTO SDA010T
VALUES
( @ORG_CD,
'2',
@SUBUL,
@ITEM_CD,
@PROD_DVRY_DT,
'6',
'6',
'01',
'1',
@CUST_CD,
'B02',
@FSLEQTY ,
@ORD_PRICE,
@ORD_AMT1,
@ORD_AMT1,
'KRW',
1,
@FSLEQTY ,
@ORD_NO,
1,
NULL,
0,
0,
NULL,
NULL,
@ORD_PRSN,
@ORD_DEPT,
@ISRT_EMP_NO,
@ISRT_DT,
@UPDT_EMP_NO,
@UPDT_DT,
0,
0,
NULL,
'01',
@COLET_TYPE,
0,
@FSLEQTY ,
'1',
'*',
0,
0,
1,
0,
0,
0,
NULL,
NULL,
@CUST_CD,
@AVR_PRICE,
NULL,
@CUST_CD,
NULL,
@ORD_UNIT,
@CUST_CD,
1,
'01',
0,
0,
0,
0,
'*',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0,
0,
'*',
@AVR_PRICE * @FSLEQTY ,
'*',
'*',
'1',
@CRT_INTERNAL_CD,
0,
10,
NULL,
NULL,
0,
@ORG_CD
)
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
RAISERROR('UPDATE error', 16, -1, @@ERROR)
ROLLBACK TRANSACTION
RETURN
END
mouseanAnya 2003-01-09
kimcz(东方不败) 整理之后的代码，大家多帮忙－－－－－－
***********************************************************************
CREATE TRIGGER TI_SMISLEF_temp
ON SMISLEF_temp
FOR INSERT
AS
DECLARE @FHAFMON VARCHAR(10) ,
@FITEMCD VARCHAR(18) ,
@FORDNO VARCHAR(12) ,
@FSLEQTY NUMERIC(7) ,
@FUNTCST NUMERIC(7) ,
@FSLEAMT NUMERIC(9) ,
@FRTNQTY NUMERIC(7) ,
@FRTNAMT NUMERIC(9) ,
@ITEM_CD VARCHAR(18) ,
@ERR_MSG VARCHAR(100)
SELECT @FHAFMON = FHAFMON ,
@FITEMCD = FITEMCD ,
@FORDNO = FORDNO ,
@FSLEQTY = FSLEQTY ,
@FUNTCST = FUNTCST ,
@FSLEAMT = FSLEAMT ,
@FRTNQTY = FRTNQTY ,
@FRTNAMT = FRTNAMT
FROM INSERTED
DECLARE @ORG_CD VARCHAR(4),
@ORD_NO VARCHAR(12),
@ISRT_EMP_NO VARCHAR(10),
@ISRT_DT DATETIME,
@UPDT_EMP_NO VARCHAR(10),
@UPDT_DT DATETIME,
@ORD_AMT NUMERIC(18,4),
@ORD_AMT1 NUMERIC(18,4),
@TRANS_LT NUMERIC(12,6),
@SUBUL VARCHAR(15),
@SUBUL1 VARCHAR(15),
@ROWCOUNT INT
DECLARE @CUST_CD VARCHAR(7),
@ORD_DT DATETIME,
@ORD_PRSN VARCHAR(10),
@ORD_DEPT VARCHAR(7),
@COLET_TYPE VARCHAR(1),
@CRT_INTERNAL_CD VARCHAR(7)
DECLARE @SER_NO1 NUMERIC(3,0),
@DVRY_DT VARCHAR(10),
@ORD_PRICE NUMERIC(18,6),
@AVR_PRICE NUMERIC(18,6),
@ORD_STATUS VARCHAR(1),
@PROD_DVRY_DT DATETIME,
@ORD_UNIT VARCHAR(3),
@ORD_QTY NUMERIC(18,6)
SELECT @PROD_DVRY_DT = CONVERT(DATETIME, @FHAFMON, 120)
SELECT @CUST_CD = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '01'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = 'aaa'
GOTO EXIT_ROUTIN
END
SELECT @ISRT_EMP_NO = 'unierp'
SELECT @ISRT_DT = GETDATE()
SELECT @UPDT_EMP_NO = 'unierp'
SELECT @UPDT_DT = GETDATE()
SELECT @ITEM_CD = ITEM_CD FROM BAA010T WHERE REPLACE(ITEM_CD,'-','') = @FITEMCD
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = 'ITEM_CD = ' + @FITEMCD + ' error'
GOTO EXIT_ROUTIN
END
SELECT @ORG_CD = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '02'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = 'aaaa'
GOTO EXIT_ROUTIN
END
SELECT @AVR_PRICE = AVR_PRICE
FROM BAA040T
WHERE ORG_CD = @ORG_CD
AND ITEM_CD = @ITEM_CD
SELECT @AVR_PRICE = ISNULL(@AVR_PRICE, 0)
SELECT @ORD_PRSN = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '09'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = 'aaa'
GOTO EXIT_ROUTIN
END
SELECT @ORD_DEPT = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '10'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = 'aaa'
GOTO EXIT_ROUTIN
END
SELECT @COLET_TYPE = '1'
SELECT @CRT_INTERNAL_CD = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '13'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = 'aaa'
GOTO EXIT_ROUTIN
END
***********************************************************************
CREATE TRIGGER TI_SMISLEF_temp
ON SMISLEF_temp
FOR INSERT
AS
DECLARE @FHAFMON VARCHAR(10) ,
@FITEMCD VARCHAR(18) ,
@FORDNO VARCHAR(12) ,
@FSLEQTY NUMERIC(7) ,
@FUNTCST NUMERIC(7) ,
@FSLEAMT NUMERIC(9) ,
@FRTNQTY NUMERIC(7) ,
@FRTNAMT NUMERIC(9) ,
@ITEM_CD VARCHAR(18) ,
@ERR_MSG VARCHAR(100)
SELECT @FHAFMON = FHAFMON ,
@FITEMCD = FITEMCD ,
@FORDNO = FORDNO ,
@FSLEQTY = FSLEQTY ,
@FUNTCST = FUNTCST ,
@FSLEAMT = FSLEAMT ,
@FRTNQTY = FRTNQTY ,
@FRTNAMT = FRTNAMT
FROM INSERTED
DECLARE @ORG_CD VARCHAR(4),
@ORD_NO VARCHAR(12),
@ISRT_EMP_NO VARCHAR(10),
@ISRT_DT DATETIME,
@UPDT_EMP_NO VARCHAR(10),
@UPDT_DT DATETIME,
@ORD_AMT NUMERIC(18,4),
@ORD_AMT1 NUMERIC(18,4),
@TRANS_LT NUMERIC(12,6),
@SUBUL VARCHAR(15),
@SUBUL1 VARCHAR(15),
@ROWCOUNT INT
DECLARE @CUST_CD VARCHAR(7),
@ORD_DT DATETIME,
@ORD_PRSN VARCHAR(10),
@ORD_DEPT VARCHAR(7),
@COLET_TYPE VARCHAR(1),
@CRT_INTERNAL_CD VARCHAR(7)
DECLARE @SER_NO1 NUMERIC(3,0),
@DVRY_DT VARCHAR(10),
@ORD_PRICE NUMERIC(18,6),
@AVR_PRICE NUMERIC(18,6),
@ORD_STATUS VARCHAR(1),
@PROD_DVRY_DT DATETIME,
@ORD_UNIT VARCHAR(3),
@ORD_QTY NUMERIC(18,6)
SELECT @PROD_DVRY_DT = CONVERT(DATETIME, @FHAFMON, 120)
SELECT @CUST_CD = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '01'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = 'aaa'
GOTO EXIT_ROUTIN
END
SELECT @ISRT_EMP_NO = 'unierp'
SELECT @ISRT_DT = GETDATE()
SELECT @UPDT_EMP_NO = 'unierp'
SELECT @UPDT_DT = GETDATE()
SELECT @ITEM_CD = ITEM_CD FROM BAA010T WHERE REPLACE(ITEM_CD,'-','') = @FITEMCD
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = 'ITEM_CD = ' + @FITEMCD + ' error'
GOTO EXIT_ROUTIN
END
SELECT @ORG_CD = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '02'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = 'aaaa'
GOTO EXIT_ROUTIN
END
SELECT @AVR_PRICE = AVR_PRICE
FROM BAA040T
WHERE ORG_CD = @ORG_CD
AND ITEM_CD = @ITEM_CD
SELECT @AVR_PRICE = ISNULL(@AVR_PRICE, 0)
SELECT @ORD_PRSN = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '09'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = 'aaa'
GOTO EXIT_ROUTIN
END
SELECT @ORD_DEPT = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '10'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = 'aaa'
GOTO EXIT_ROUTIN
END
SELECT @COLET_TYPE = '1'
SELECT @CRT_INTERNAL_CD = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '13'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = 'aaa'
GOTO EXIT_ROUTIN
END
kimcz 2003-01-09
在这个版写的太乱了，我在pb版也提问了，在那里删除了碎了的字和注释
http://expert.csdn.net/Expert/topic/1342/1342657.xml?temp=.7823755
http://expert.csdn.net/Expert/topic/1342/1342657.xml?temp=.7823755
kimcz 2003-01-09
UPDATE SCA020T
SET ORD_STATUS = '9',
CLOSE_FLAG = 'Y',
CLOSE_DT = GETDATE() ,
UPDT_EMP_NO = @UPDT_EMP_NO,
UPDT_DT = @UPDT_DT
WHERE ORG_CD = @ORG_CD
AND ORD_NO = @ORD_NO
AND ITEM_CD = @ITEM_CD
AND DVRY_DT = @DVRY_DT
IF (@@ERROR <> 0)
BEGIN
RAISERROR('UPDATE error', 16, -1, @@ERROR)
ROLLBACK TRANSACTION
RETURN
END
END
ELSE
IF @ROWCOUNT > 1
BEGIN
SELECT TOP 1 @ORD_NO = A.ORD_NO, @ORD_DT = A.ORD_DT, @ORD_QTY = B.ORD_QTY
FROM SCA010T A LEFT OUTER JOIN SCA020T B ON A.ORD_NO = B.ORD_NO
WHERE A.ORG_CD = B.ORG_CD
AND A.ORG_CD = @ORG_CD
AND B.ITEM_CD = @ITEM_CD
AND B.ORD_STATUS <> '9'
AND B.CLOSE_FLAG <> 'Y'
AND CONVERT(CHAR(10), B.DVRY_DT, 120) = CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
ORDER BY A.ORD_DT
END
return
select '5'
EXIT_ROUTIN:
UPDATE SMISLEF_TEMP
SET REASON = @ERR_MSG
WHERE FHAFMON = @FHAFMON
AND FITEMCD = @FITEMCD
AND FORDNO = @FORDNO
select '6'
还有,因为不能连续回复,才写上了
SET ORD_STATUS = '9',
CLOSE_FLAG = 'Y',
CLOSE_DT = GETDATE() ,
UPDT_EMP_NO = @UPDT_EMP_NO,
UPDT_DT = @UPDT_DT
WHERE ORG_CD = @ORG_CD
AND ORD_NO = @ORD_NO
AND ITEM_CD = @ITEM_CD
AND DVRY_DT = @DVRY_DT
IF (@@ERROR <> 0)
BEGIN
RAISERROR('UPDATE error', 16, -1, @@ERROR)
ROLLBACK TRANSACTION
RETURN
END
END
ELSE
IF @ROWCOUNT > 1
BEGIN
SELECT TOP 1 @ORD_NO = A.ORD_NO, @ORD_DT = A.ORD_DT, @ORD_QTY = B.ORD_QTY
FROM SCA010T A LEFT OUTER JOIN SCA020T B ON A.ORD_NO = B.ORD_NO
WHERE A.ORG_CD = B.ORG_CD
AND A.ORG_CD = @ORG_CD
AND B.ITEM_CD = @ITEM_CD
AND B.ORD_STATUS <> '9'
AND B.CLOSE_FLAG <> 'Y'
AND CONVERT(CHAR(10), B.DVRY_DT, 120) = CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
ORDER BY A.ORD_DT
END
return
select '5'
EXIT_ROUTIN:
UPDATE SMISLEF_TEMP
SET REASON = @ERR_MSG
WHERE FHAFMON = @FHAFMON
AND FITEMCD = @FITEMCD
AND FORDNO = @FORDNO
select '6'
还有,因为不能连续回复,才写上了
愉快的登山者 2003-01-09
太长了。容我慢慢看来。
kimcz 2003-01-09
--==========================================================================
IF EXISTS( SELECT * FROM MHA000T WHERE ORG_CD = @ORG_CD AND WH_TYPE = '3' AND CARR_OVER_YYMM >= @PROD_DVRY_DT )
BEGIN
SELECT @ERR_MSG = '자재 이월완료되어 처리하지 못합니다.'
GOTO EXIT_ROUTIN
END
-----------------------------------------------------------------------------------------------------------
-- 수불번호계산
SELECT @SUBUL = 'SB' + '0000000000001'
SELECT @SUBUL1 = (SELECT MAX(TRANS_NO) FROM SDA010T WHERE ORG_CD = @ORG_CD AND TRANS_NO LIKE 'SB%')
IF @SUBUL <= @SUBUL1
BEGIN
SELECT @SUBUL = LEFT(@SUBUL1,2) + REPLACE(STR(SUBSTRING(@SUBUL1,3, LEN(@SUBUL1) - 2 ) + 1,13,0),' ', '0')
END
ELSE
BEGIN
SELECT @SUBUL = @SUBUL
END -- 수불번호계산
select '4.7'
-- 출고처리
INSERT INTO SDA010T
VALUES
( @ORG_CD, -- 사업장코드==================================================
'2', -- 수불구분(고정)
@SUBUL, -- 수불전표번호
@ITEM_CD, -- 품목코드
@PROD_DVRY_DT, -- 수불일(납입일 )
'6', -- 수불처구분(고정)
'6', -- 수불형태(고정)
'01', -- 수불유형(고정)
'1', -- 양품/불량구분(고정)
@CUST_CD, -- 수불처======================================================
'B02', -- 주창고======================================================
@FSLEQTY , -- 수불총량
@ORD_PRICE, -- 수불단가
@ORD_AMT1, -- 출하금액
@ORD_AMT1, -- 출하원화금액
'KRW', -- 화폐단위(고정)
1, -- 환율(고정)
@FSLEQTY , -- 수주단위량
@ORD_NO, -- 수주번호
1, -- 순번(고정)
NULL, -- 근거번호(고정)
0, -- 반품량(고정)
0, -- 매출량(고정)
NULL, -- L/C번호(고정)
NULL, -- P/O번호(고정)
@ORD_PRSN, -- 수불담당자==================================================
@ORD_DEPT, -- 수불부서====================================================
@ISRT_EMP_NO, -- 등록자
@ISRT_DT, -- 등록일
@UPDT_EMP_NO, -- 변경자
@UPDT_DT, -- 변경일
0, -- Palette 수량(고정)
0, -- Carton 수량(고정)
NULL, -- 발송방법(고정)
'01', -- 거래유형(고정)
@COLET_TYPE, -- 수금유형====================================================
0, -- 기준(환산)수량(고정)
@FSLEQTY , -- 수불량
'1', -- 보관장소====================================================
'*', -- LOT 번호(고정)
0, -- LOT SUB 번호(고정)
0, -- 인도잔량(고정)
1, -- 수불순번(고정)
0, -- 자재전표수불번호(고정)
0, -- 운송량(고정)
0, -- 수출통관수량(고정)
NULL, -- 검사일(고정)
NULL, -- 검사순번(고정)
@CUST_CD, -- 납품처코드==================================================
@AVR_PRICE, -- 재고단가(원가)
NULL, -- D/C 금액(고정)
@CUST_CD, -- 매출처======================================================
NULL, -- 세금신고사업장
@ORD_UNIT, -- 수불단위
@CUST_CD, -- 발송처======================================================
1, -- 출고요청번호순번(고정)
'01', -- 수금담당자==================================================
0, -- 할인금액(고정)
0, -- 요청할증량(고정)
0, -- 반품할증량(고정)
0, -- 반품순번(고정)
'*', -- 계획번호(고정)
NULL, -- 예비금액1
NULL, -- 예비금액2
NULL, -- 예비금액3
NULL, -- 예비수량1
NULL, -- 예비수량2
NULL, -- 예비수량3
0, -- 수불할증량(고정)
0, -- 반품매출량(고정)
'*', -- MAKER LOT 번호(고정)
@AVR_PRICE * @FSLEQTY , -- 재고금액( mha020 출고 금액 update 됨)
'*', -- 타계정품목(고정)
'*', -- 이동후사업장(고정)
'1', -- 부가세구분(고정)
@CRT_INTERNAL_CD, -- 내부부서코드(고정)
0, -- 할인단가(고정)
10, -- 부가세율(고정)
NULL, -- 매출사업부(고정)
NULL, -- 재고금액관련그룹(고정)
0, -- LC 수량(고정)
@ORG_CD -- 과거 사업장코드=============================================
)
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
RAISERROR('UPDATE 에러(@@ERROR = %d) 입니다.', 16, -1, @@ERROR)
ROLLBACK TRANSACTION
RETURN
END
IF EXISTS( SELECT * FROM MHA000T WHERE ORG_CD = @ORG_CD AND WH_TYPE = '3' AND CARR_OVER_YYMM >= @PROD_DVRY_DT )
BEGIN
SELECT @ERR_MSG = '자재 이월완료되어 처리하지 못합니다.'
GOTO EXIT_ROUTIN
END
-----------------------------------------------------------------------------------------------------------
-- 수불번호계산
SELECT @SUBUL = 'SB' + '0000000000001'
SELECT @SUBUL1 = (SELECT MAX(TRANS_NO) FROM SDA010T WHERE ORG_CD = @ORG_CD AND TRANS_NO LIKE 'SB%')
IF @SUBUL <= @SUBUL1
BEGIN
SELECT @SUBUL = LEFT(@SUBUL1,2) + REPLACE(STR(SUBSTRING(@SUBUL1,3, LEN(@SUBUL1) - 2 ) + 1,13,0),' ', '0')
END
ELSE
BEGIN
SELECT @SUBUL = @SUBUL
END -- 수불번호계산
select '4.7'
-- 출고처리
INSERT INTO SDA010T
VALUES
( @ORG_CD, -- 사업장코드==================================================
'2', -- 수불구분(고정)
@SUBUL, -- 수불전표번호
@ITEM_CD, -- 품목코드
@PROD_DVRY_DT, -- 수불일(납입일 )
'6', -- 수불처구분(고정)
'6', -- 수불형태(고정)
'01', -- 수불유형(고정)
'1', -- 양품/불량구분(고정)
@CUST_CD, -- 수불처======================================================
'B02', -- 주창고======================================================
@FSLEQTY , -- 수불총량
@ORD_PRICE, -- 수불단가
@ORD_AMT1, -- 출하금액
@ORD_AMT1, -- 출하원화금액
'KRW', -- 화폐단위(고정)
1, -- 환율(고정)
@FSLEQTY , -- 수주단위량
@ORD_NO, -- 수주번호
1, -- 순번(고정)
NULL, -- 근거번호(고정)
0, -- 반품량(고정)
0, -- 매출량(고정)
NULL, -- L/C번호(고정)
NULL, -- P/O번호(고정)
@ORD_PRSN, -- 수불담당자==================================================
@ORD_DEPT, -- 수불부서====================================================
@ISRT_EMP_NO, -- 등록자
@ISRT_DT, -- 등록일
@UPDT_EMP_NO, -- 변경자
@UPDT_DT, -- 변경일
0, -- Palette 수량(고정)
0, -- Carton 수량(고정)
NULL, -- 발송방법(고정)
'01', -- 거래유형(고정)
@COLET_TYPE, -- 수금유형====================================================
0, -- 기준(환산)수량(고정)
@FSLEQTY , -- 수불량
'1', -- 보관장소====================================================
'*', -- LOT 번호(고정)
0, -- LOT SUB 번호(고정)
0, -- 인도잔량(고정)
1, -- 수불순번(고정)
0, -- 자재전표수불번호(고정)
0, -- 운송량(고정)
0, -- 수출통관수량(고정)
NULL, -- 검사일(고정)
NULL, -- 검사순번(고정)
@CUST_CD, -- 납품처코드==================================================
@AVR_PRICE, -- 재고단가(원가)
NULL, -- D/C 금액(고정)
@CUST_CD, -- 매출처======================================================
NULL, -- 세금신고사업장
@ORD_UNIT, -- 수불단위
@CUST_CD, -- 발송처======================================================
1, -- 출고요청번호순번(고정)
'01', -- 수금담당자==================================================
0, -- 할인금액(고정)
0, -- 요청할증량(고정)
0, -- 반품할증량(고정)
0, -- 반품순번(고정)
'*', -- 계획번호(고정)
NULL, -- 예비금액1
NULL, -- 예비금액2
NULL, -- 예비금액3
NULL, -- 예비수량1
NULL, -- 예비수량2
NULL, -- 예비수량3
0, -- 수불할증량(고정)
0, -- 반품매출량(고정)
'*', -- MAKER LOT 번호(고정)
@AVR_PRICE * @FSLEQTY , -- 재고금액( mha020 출고 금액 update 됨)
'*', -- 타계정품목(고정)
'*', -- 이동후사업장(고정)
'1', -- 부가세구분(고정)
@CRT_INTERNAL_CD, -- 내부부서코드(고정)
0, -- 할인단가(고정)
10, -- 부가세율(고정)
NULL, -- 매출사업부(고정)
NULL, -- 재고금액관련그룹(고정)
0, -- LC 수량(고정)
@ORG_CD -- 과거 사업장코드=============================================
)
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
RAISERROR('UPDATE 에러(@@ERROR = %d) 입니다.', 16, -1, @@ERROR)
ROLLBACK TRANSACTION
RETURN
END
kimcz 2003-01-09
-- =================================================================================================
-- SCA010T(수주정보)TABLE
-- 수주담당
SELECT @ORD_PRSN = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '09'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = '종합정보(수주담당) 미등록'
GOTO EXIT_ROUTIN
END
-- 수주부서
SELECT @ORD_DEPT = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '10'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = '종합정보(수주부서) 미등록'
GOTO EXIT_ROUTIN
END
SELECT @COLET_TYPE = '1' -- 수금유형
-- 내부 부서코드
SELECT @CRT_INTERNAL_CD = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '13'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = '종합정보(내부부서) 미등록'
GOTO EXIT_ROUTIN
END
-- SCA010T(수주정보)TABLE
----------------------------------------------------------------------------------------------------
-- SCA020T(수주내역(거래처)TABLE
SELECT @SER_NO1 = 1 -- 순번1
SELECT @DVRY_DT = @PROD_DVRY_DT -- 납기일
SELECT @ORD_STATUS = '1' -- 수주진행상태
-- 수주단위
SELECT @ORD_UNIT = (SELECT INV_UNIT FROM BAA010T WHERE ITEM_CD = @ITEM_CD)
SELECT @ORD_UNIT = @ORD_UNIT -- 수주단위
--------------------------------------------------------------------------------------
-- 수주번호와 수주일 검색
SELECT @ROWCOUNT = COUNT(*)
FROM SCA010T A LEFT OUTER JOIN SCA020T B ON A.ORD_NO = B.ORD_NO
WHERE A.ORG_CD = B.ORG_CD
AND A.ORG_CD = @ORG_CD
AND B.ITEM_CD = @ITEM_CD
AND B.ORD_STATUS <> '9'
-- AND B.CLOSE_FLAG <> 'Y'
AND CONVERT(CHAR(10), B.DVRY_DT, 120) = CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
SELECT @ROWCOUNT
SELECT @PROD_DVRY_DT
SELECT CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = @ITEM_CD + '-' + @ORG_CD + ' - ' + CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
GOTO EXIT_ROUTIN
END
IF @ROWCOUNT = 1
-- 납기일에 해당한 수주가 하나일 때
BEGIN
SELECT @ORD_NO = A.ORD_NO, @ORD_DT = A.ORD_DT, @ORD_QTY = B.ORD_QTY
FROM SCA010T A LEFT OUTER JOIN SCA020T B ON A.ORD_NO = B.ORD_NO
WHERE A.ORG_CD = B.ORG_CD
AND A.ORG_CD = @ORG_CD
AND B.ITEM_CD = @ITEM_CD
AND B.ORD_STATUS <> '9'
-- AND B.CLOSE_FLAG <> 'Y'
AND CONVERT(CHAR(10), B.DVRY_DT, 120) = CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
select '4.3'
select @ord_no
select @ord_dt
select @ord_qty
select @org_cd
select @item_cd
select @PROD_DVRY_DT
select CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
---------------------------------------------------------------------------------------
-- 수주금액 계산(제품단가 SELECT * 지시수량)
SELECT @ORD_PRICE = ITEM_PRICE
FROM SAA010T
WHERE ORG_CD = @ORG_CD
AND CUST_CD = @CUST_CD
AND ITEM_CD = @ITEM_CD
AND CONVERT(VARCHAR(10),APPLY_STRT_DT,120) <= CONVERT(VARCHAR(10),@ORD_DT,120)
AND CONVERT(VARCHAR(10),APPLY_FNSH_DT,120) >= CONVERT(VARCHAR(10),@ORD_DT,120)
select '4.4'
select CONVERT(VARCHAR(10),@ORD_DT,112)
select @ORD_DT
select @cust_cd
select @item_cd
select @org_cd
SELECT @ORD_PRICE
SELECT @ORD_PRICE = ISNULL(@ORD_PRICE, 0) -- 수주단가
SELECT @ORD_AMT = @FSLEQTY * @ORD_PRICE -- 수주금액(수주량 * 수주단가)
SELECT @ORD_AMT1 = @FSLEQTY * @ORD_PRICE -- 출하금액(출하량 * 수주단가)
select '4.5'
SELECT @ORD_PRICE
SELECT @ORD_AMT
SELECT @ORD_AMT1
-- SCA010T(수주정보)TABLE
-- 수주담당
SELECT @ORD_PRSN = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '09'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = '종합정보(수주담당) 미등록'
GOTO EXIT_ROUTIN
END
-- 수주부서
SELECT @ORD_DEPT = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '10'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = '종합정보(수주부서) 미등록'
GOTO EXIT_ROUTIN
END
SELECT @COLET_TYPE = '1' -- 수금유형
-- 내부 부서코드
SELECT @CRT_INTERNAL_CD = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '13'
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = '종합정보(내부부서) 미등록'
GOTO EXIT_ROUTIN
END
-- SCA010T(수주정보)TABLE
----------------------------------------------------------------------------------------------------
-- SCA020T(수주내역(거래처)TABLE
SELECT @SER_NO1 = 1 -- 순번1
SELECT @DVRY_DT = @PROD_DVRY_DT -- 납기일
SELECT @ORD_STATUS = '1' -- 수주진행상태
-- 수주단위
SELECT @ORD_UNIT = (SELECT INV_UNIT FROM BAA010T WHERE ITEM_CD = @ITEM_CD)
SELECT @ORD_UNIT = @ORD_UNIT -- 수주단위
--------------------------------------------------------------------------------------
-- 수주번호와 수주일 검색
SELECT @ROWCOUNT = COUNT(*)
FROM SCA010T A LEFT OUTER JOIN SCA020T B ON A.ORD_NO = B.ORD_NO
WHERE A.ORG_CD = B.ORG_CD
AND A.ORG_CD = @ORG_CD
AND B.ITEM_CD = @ITEM_CD
AND B.ORD_STATUS <> '9'
-- AND B.CLOSE_FLAG <> 'Y'
AND CONVERT(CHAR(10), B.DVRY_DT, 120) = CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
SELECT @ROWCOUNT
SELECT @PROD_DVRY_DT
SELECT CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = @ITEM_CD + '-' + @ORG_CD + ' - ' + CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
GOTO EXIT_ROUTIN
END
IF @ROWCOUNT = 1
-- 납기일에 해당한 수주가 하나일 때
BEGIN
SELECT @ORD_NO = A.ORD_NO, @ORD_DT = A.ORD_DT, @ORD_QTY = B.ORD_QTY
FROM SCA010T A LEFT OUTER JOIN SCA020T B ON A.ORD_NO = B.ORD_NO
WHERE A.ORG_CD = B.ORG_CD
AND A.ORG_CD = @ORG_CD
AND B.ITEM_CD = @ITEM_CD
AND B.ORD_STATUS <> '9'
-- AND B.CLOSE_FLAG <> 'Y'
AND CONVERT(CHAR(10), B.DVRY_DT, 120) = CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
select '4.3'
select @ord_no
select @ord_dt
select @ord_qty
select @org_cd
select @item_cd
select @PROD_DVRY_DT
select CONVERT(CHAR(10), @PROD_DVRY_DT, 120)
---------------------------------------------------------------------------------------
-- 수주금액 계산(제품단가 SELECT * 지시수량)
SELECT @ORD_PRICE = ITEM_PRICE
FROM SAA010T
WHERE ORG_CD = @ORG_CD
AND CUST_CD = @CUST_CD
AND ITEM_CD = @ITEM_CD
AND CONVERT(VARCHAR(10),APPLY_STRT_DT,120) <= CONVERT(VARCHAR(10),@ORD_DT,120)
AND CONVERT(VARCHAR(10),APPLY_FNSH_DT,120) >= CONVERT(VARCHAR(10),@ORD_DT,120)
select '4.4'
select CONVERT(VARCHAR(10),@ORD_DT,112)
select @ORD_DT
select @cust_cd
select @item_cd
select @org_cd
SELECT @ORD_PRICE
SELECT @ORD_PRICE = ISNULL(@ORD_PRICE, 0) -- 수주단가
SELECT @ORD_AMT = @FSLEQTY * @ORD_PRICE -- 수주금액(수주량 * 수주단가)
SELECT @ORD_AMT1 = @FSLEQTY * @ORD_PRICE -- 출하금액(출하량 * 수주단가)
select '4.5'
SELECT @ORD_PRICE
SELECT @ORD_AMT
SELECT @ORD_AMT1
kimcz 2003-01-09
CREATE TRIGGER TI_SMISLEF_temp
ON SMISLEF_temp
FOR INSERT
AS
DECLARE @FHAFMON VARCHAR(10) ,
@FITEMCD VARCHAR(18) ,
@FORDNO VARCHAR(12) ,
@FSLEQTY NUMERIC(7) , --납품수량
@FUNTCST NUMERIC(7) , --납품단가
@FSLEAMT NUMERIC(9) , --납품금액
@FRTNQTY NUMERIC(7) , --반품수량
@FRTNAMT NUMERIC(9) , --반품금액
@ITEM_CD VARCHAR(18) , --품목코드
@ERR_MSG VARCHAR(100)
SELECT @FHAFMON = FHAFMON ,
@FITEMCD = FITEMCD ,
@FORDNO = FORDNO ,
@FSLEQTY = FSLEQTY ,
@FUNTCST = FUNTCST ,
@FSLEAMT = FSLEAMT ,
@FRTNQTY = FRTNQTY ,
@FRTNAMT = FRTNAMT
FROM INSERTED
-- 변수선언
-- =================================================================================================
-- 공통 변수
DECLARE @ORG_CD VARCHAR(4), -- 사업장코드
@ORD_NO VARCHAR(12), -- 수주번호
@ISRT_EMP_NO VARCHAR(10), -- 등록자사번
@ISRT_DT DATETIME, -- 등록일
@UPDT_EMP_NO VARCHAR(10), -- 변경자사번
@UPDT_DT DATETIME, -- 변경일
@ORD_AMT NUMERIC(18,4), -- 수주금액
@ORD_AMT1 NUMERIC(18,4), -- 출하금액
@TRANS_LT NUMERIC(12,6), -- 이동리드타임
@SUBUL VARCHAR(15), -- 수불번호
@SUBUL1 VARCHAR(15), -- 수불번호1
@ROWCOUNT INT
-- =================================================================================================
-- SCA010T(수주정보)TABLE
DECLARE @CUST_CD VARCHAR(7), -- 거래처코드
@ORD_DT DATETIME, -- 수주일
@ORD_PRSN VARCHAR(10), -- 수주담당
@ORD_DEPT VARCHAR(7), -- 수주부서
@COLET_TYPE VARCHAR(1), -- 수금유형
@CRT_INTERNAL_CD VARCHAR(7) -- 내부 부서코드
-- SCA030T(수주내역)TABLE
DECLARE @SER_NO1 NUMERIC(3,0), -- 순번1
@DVRY_DT VARCHAR(10), -- 납기일
@ORD_PRICE NUMERIC(18,6), -- 수주단가
@AVR_PRICE NUMERIC(18,6), -- 기준단가
@ORD_STATUS VARCHAR(1), -- 수주진행상태
@PROD_DVRY_DT DATETIME, -- 생산납기일
@ORD_UNIT VARCHAR(3), -- 수주단위
@ORD_QTY NUMERIC(18,6) -- 수주량
-- =================================================================================================
-- 값설정
-- =================================================================================================
-- 공통 값설정
SELECT @PROD_DVRY_DT = CONVERT(DATETIME, @FHAFMON, 120)
-- 거래처코드
SELECT @CUST_CD = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '01' -- VAN 관련 거래처코드
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = '종합정보(거래처코드) 미등록'
GOTO EXIT_ROUTIN
END
SELECT @ISRT_EMP_NO = 'unierp' -- 등록자사번
SELECT @ISRT_DT = GETDATE() -- 등록일
SELECT @UPDT_EMP_NO = 'unierp' -- 변경자사번
SELECT @UPDT_DT = GETDATE() -- 변경일
SELECT @ITEM_CD = ITEM_CD FROM BAA010T WHERE REPLACE(ITEM_CD,'-','') = @FITEMCD
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = 'ITEM_CD = ' + @FITEMCD + ' 미등록'
GOTO EXIT_ROUTIN
END
-- 사업장코드
SELECT @ORG_CD = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '02' -- 사업장코드
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = '종합정보(사업장) 미등록'
GOTO EXIT_ROUTIN
END
-- 기 준 단가
SELECT @AVR_PRICE = AVR_PRICE
FROM BAA040T
WHERE ORG_CD = @ORG_CD
AND ITEM_CD = @ITEM_CD
SELECT @AVR_PRICE = ISNULL(@AVR_PRICE, 0) -- 기준단가
ON SMISLEF_temp
FOR INSERT
AS
DECLARE @FHAFMON VARCHAR(10) ,
@FITEMCD VARCHAR(18) ,
@FORDNO VARCHAR(12) ,
@FSLEQTY NUMERIC(7) , --납품수량
@FUNTCST NUMERIC(7) , --납품단가
@FSLEAMT NUMERIC(9) , --납품금액
@FRTNQTY NUMERIC(7) , --반품수량
@FRTNAMT NUMERIC(9) , --반품금액
@ITEM_CD VARCHAR(18) , --품목코드
@ERR_MSG VARCHAR(100)
SELECT @FHAFMON = FHAFMON ,
@FITEMCD = FITEMCD ,
@FORDNO = FORDNO ,
@FSLEQTY = FSLEQTY ,
@FUNTCST = FUNTCST ,
@FSLEAMT = FSLEAMT ,
@FRTNQTY = FRTNQTY ,
@FRTNAMT = FRTNAMT
FROM INSERTED
-- 변수선언
-- =================================================================================================
-- 공통 변수
DECLARE @ORG_CD VARCHAR(4), -- 사업장코드
@ORD_NO VARCHAR(12), -- 수주번호
@ISRT_EMP_NO VARCHAR(10), -- 등록자사번
@ISRT_DT DATETIME, -- 등록일
@UPDT_EMP_NO VARCHAR(10), -- 변경자사번
@UPDT_DT DATETIME, -- 변경일
@ORD_AMT NUMERIC(18,4), -- 수주금액
@ORD_AMT1 NUMERIC(18,4), -- 출하금액
@TRANS_LT NUMERIC(12,6), -- 이동리드타임
@SUBUL VARCHAR(15), -- 수불번호
@SUBUL1 VARCHAR(15), -- 수불번호1
@ROWCOUNT INT
-- =================================================================================================
-- SCA010T(수주정보)TABLE
DECLARE @CUST_CD VARCHAR(7), -- 거래처코드
@ORD_DT DATETIME, -- 수주일
@ORD_PRSN VARCHAR(10), -- 수주담당
@ORD_DEPT VARCHAR(7), -- 수주부서
@COLET_TYPE VARCHAR(1), -- 수금유형
@CRT_INTERNAL_CD VARCHAR(7) -- 내부 부서코드
-- SCA030T(수주내역)TABLE
DECLARE @SER_NO1 NUMERIC(3,0), -- 순번1
@DVRY_DT VARCHAR(10), -- 납기일
@ORD_PRICE NUMERIC(18,6), -- 수주단가
@AVR_PRICE NUMERIC(18,6), -- 기준단가
@ORD_STATUS VARCHAR(1), -- 수주진행상태
@PROD_DVRY_DT DATETIME, -- 생산납기일
@ORD_UNIT VARCHAR(3), -- 수주단위
@ORD_QTY NUMERIC(18,6) -- 수주량
-- =================================================================================================
-- 값설정
-- =================================================================================================
-- 공통 값설정
SELECT @PROD_DVRY_DT = CONVERT(DATETIME, @FHAFMON, 120)
-- 거래처코드
SELECT @CUST_CD = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '01' -- VAN 관련 거래처코드
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = '종합정보(거래처코드) 미등록'
GOTO EXIT_ROUTIN
END
SELECT @ISRT_EMP_NO = 'unierp' -- 등록자사번
SELECT @ISRT_DT = GETDATE() -- 등록일
SELECT @UPDT_EMP_NO = 'unierp' -- 변경자사번
SELECT @UPDT_DT = GETDATE() -- 변경일
SELECT @ITEM_CD = ITEM_CD FROM BAA010T WHERE REPLACE(ITEM_CD,'-','') = @FITEMCD
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = 'ITEM_CD = ' + @FITEMCD + ' 미등록'
GOTO EXIT_ROUTIN
END
-- 사업장코드
SELECT @ORG_CD = REL_CD1
FROM ZAA010T
WHERE MAJOR_CD = 'ZZ01'
AND MINOR_CD = '02' -- 사업장코드
IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
BEGIN
SELECT @ERR_MSG = '종합정보(사업장) 미등록'
GOTO EXIT_ROUTIN
END
-- 기 준 단가
SELECT @AVR_PRICE = AVR_PRICE
FROM BAA040T
WHERE ORG_CD = @ORG_CD
AND ITEM_CD = @ITEM_CD
SELECT @AVR_PRICE = ISNULL(@AVR_PRICE, 0) -- 기준단가
愉快的登山者 2003-01-09
将你的TRIGGER贴出来，帮你分析。
