안녕하세요. 질문드릴게 있어 글 남깁니다.
저희 납품카드 발행시스템 -> mes 시스템으로 발주정보를 인터페이스 하는데요.
오늘 아침 갑자기 아래와 같은 에러 메세지가 출력되면서 발주정보가 인터페이스 되지 않습니다.
primary key 제약조건
'pk_tb_erp_spin_oneday_data를 위반했습니다. 개체 dbo.tb_erp_spin_oneday에 중복키를 삽입할 수 없습니다.
중복키값은 (**** DH202208139745) 입니다.
관련 프로시저 쿼리는 아래와 같습니다.
ALTER PROCEDURE [dbo].[SP_ERP_SPIN_ORDER_S]
@OUTPUT_CUST varchar(10),
@ORDER_NO varchar(20),
@PART_ID varchar(20),
@PART_NAME varchar(50),
@ORDER_QTY int ,
@MI_QTY int ,
@SUN_QTY float ,
@CANCEL_QTY int ,
@INPUT_QTY int ,
@CUST_ID varchar(20),
@NAPUM_ID varchar(20),
@ORDER_DATE varchar(20),
@NAP_DATE varchar(20),
@PACK_ID varchar(20),
@LOCATION_ID varchar(20),
@SAUPSO_ID varchar(20),
@DAERIJUM_ID varchar(20),
@GUMSA_TYPE varchar(20),
@GUMSA_LOCATION varchar(20),
@GUMSA_QTY int ,
@GUMSA_PROD int ,
@CAR_ID varchar(20),
@UNION_CLASS varchar(20)
,@BAL_TYPE varchar(5)
,@QC char(1)
,@C_PART_ID varchar(20)
,@EAC varchar(20)
,@INPUT_CTL char(1)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @V_DATE DATETIME = GETDATE()
DECLARE @V_DATE_ONDAY DATETIME = CONVERT(DATETIME,CONVERT(VARCHAR(6),GETDATE(),112) + '01')
IF NOT EXISTS(SELECT TOP 1 * FROM TB_ERP_SPIN WHERE OUTPUT_CUST = @OUTPUT_CUST AND ORDER_NO = @ORDER_NO AND CREATE_DTTM >= @V_DATE_ONDAY AND CREATE_DTTM < DATEADD(DAY,1,@V_DATE_ONDAY)) AND CONVERT(VARCHAR(8),@V_DATE,112) = CONVERT(VARCHAR(8),@V_DATE_ONDAY,112)
BEGIN
INSERT INTO TB_ERP_SPIN_ONEDAY
(
OUTPUT_CUST
,ORDER_NO
,PART_ID
,PART_NAME
,ORDER_QTY
,MI_QTY
,SUN_QTY
,CANCEL_QTY
,INPUT_QTY
,CUST_ID
,NAPUM_ID
,ORDER_DATE
,NAP_DATE
,PACK_ID
,LOCATION_ID
,SAUPSO_ID
,DAERIJUM_ID
,GUMSA_TYPE
,GUMSA_LOCATION
,GUMSA_QTY
,GUMSA_PROD
,CAR_ID
,UNION_CLASS
,BAL_TYPE
,QC
,C_PART_ID
,EAC
,INPUT_CTL
)
VALUES
(
@OUTPUT_CUST
,@ORDER_NO
,@PART_ID
,@PART_NAME
,@ORDER_QTY
,@MI_QTY
,@SUN_QTY
,@CANCEL_QTY
,@INPUT_QTY
,@CUST_ID
,@NAPUM_ID
,SUBSTRING(@ORDER_DATE, 1, 10)
,SUBSTRING(@NAP_DATE, 1, 10)
,@PACK_ID
,@LOCATION_ID
,@SAUPSO_ID
,@DAERIJUM_ID
,@GUMSA_TYPE
,@GUMSA_LOCATION
,@GUMSA_QTY
,@GUMSA_PROD
,@CAR_ID
,@UNION_CLASS
,@BAL_TYPE
,@QC
,@C_PART_ID
,@EAC
,@INPUT_CTL
)
END
DELETE
FROM TB_ERP_SPIN
WHERE OUTPUT_CUST = @OUTPUT_CUST
AND ORDER_NO = @ORDER_NO
INSERT INTO TB_ERP_SPIN
( OUTPUT_CUST
,ORDER_NO
,PART_ID
,PART_NAME
,ORDER_QTY
,MI_QTY
,SUN_QTY
,CANCEL_QTY
,INPUT_QTY
,CUST_ID
,NAPUM_ID
,ORDER_DATE
,NAP_DATE
,PACK_ID
,LOCATION_ID
,SAUPSO_ID
,DAERIJUM_ID
,GUMSA_TYPE
,GUMSA_LOCATION
,GUMSA_QTY
,GUMSA_PROD
,CAR_ID
,UNION_CLASS
,BAL_TYPE
,QC
,C_PART_ID
,EAC
,INPUT_CTL
)
VALUES ( @OUTPUT_CUST
,@ORDER_NO
,@PART_ID
,@PART_NAME
,@ORDER_QTY
,@MI_QTY
,@SUN_QTY
,@CANCEL_QTY
,@INPUT_QTY
,@CUST_ID
,@NAPUM_ID
,SUBSTRING(@ORDER_DATE, 1, 10)
,SUBSTRING(@NAP_DATE, 1, 10)
,@PACK_ID
,@LOCATION_ID
,@SAUPSO_ID
,@DAERIJUM_ID
,@GUMSA_TYPE
,@GUMSA_LOCATION
,@GUMSA_QTY
,@GUMSA_PROD
,@CAR_ID
,@UNION_CLASS
,@BAL_TYPE
,@QC
,@C_PART_ID
,@EAC
,@INPUT_CTL
)
IF(ISNULL(@MI_QTY,0) > 0)
BEGIN
DELETE dbo.TB_PRV_PRN
WHERE OUTPUT_CUST = @OUTPUT_CUST
AND ORDER_NO = @ORDER_NO
DELETE TB_PRV_SPIN
WHERE OUTPUT_CUST = @OUTPUT_CUST
AND ORDER_NO = @ORDER_NO
INSERT INTO dbo.TB_PRV_SPIN
(
OUTPUT_CUST
,ORDER_NO
,PART_ID
,PART_NAME
,ORDER_QTY
,MI_QTY
,SUN_QTY
,CANCEL_QTY
,INPUT_QTY
,CUST_ID
,NAPUM_ID
,ORDER_DATE
,NAP_DATE
,PACK_ID
,LOCATION_ID
,SAUPSO_ID
,DAERIJUM_ID
,GUMSA_TYPE
,GUMSA_LOCATION
,GUMSA_QTY
,GUMSA_PROD
,CAR_ID
,UNION_CLASS
,BAL_TYPE
,QC
,C_PART_ID
,EAC
,INPUT_CTL
,PRINT_FLAG
,CREATE_DTTM
)
VALUES
(
@OUTPUT_CUST
,@ORDER_NO
,@PART_ID
,@PART_NAME
,@ORDER_QTY
,@MI_QTY
,@SUN_QTY
,@CANCEL_QTY
,@INPUT_QTY
,@CUST_ID
,@NAPUM_ID
,SUBSTRING(@ORDER_DATE, 1, 10)
,SUBSTRING(@NAP_DATE, 1, 10)
,@PACK_ID
,@LOCATION_ID
,@SAUPSO_ID
,@DAERIJUM_ID
,@GUMSA_TYPE
,@GUMSA_LOCATION
,@GUMSA_QTY
,@GUMSA_PROD
,@CAR_ID
,@UNION_CLASS
,@BAL_TYPE
,@QC
,@C_PART_ID
,@EAC
,@INPUT_CTL
,'N'
,GETDATE()
)
END
어느 구문을 수정해야 하는지 고수님께 문의 드립니다.
참고바랍니다.
핵심은 tb_erp_spin_oneday의 pk에 해당하는 칼럼을 조사
기존 데이터 삭제 or 다른 처리구문이 들어가면 될듯.
아래는 삭제 형태예요
alter procedure SP_ERP_SPIN_ORDER_S (
@vars....
)
AS
Begin
declare @v....
if not exists(select .... from TB_ERP_SPIN..... ) Begin
-- 이 부분 추가
if exists(select ... from TB_ERP_SPIN_ONEDAY where primary_column....) begin
delete from TB_ERP_SPIN_ONEDAY where primary_columns....
end
-- 추가끝.
insert into TB_ERP_SPIN_ONEDAY
이하 같음....
End