SqlServer-MERGE

IF EXISTS ( SELECT *
FROM sysobjects
WHERE id = OBJECT_ID(N'Sync_SM_Supplier')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE Sync_SM_Supplier;
END;
GO

CREATE PROCEDURE Sync_SM_Supplier
AS
BEGIN
--1.同步供应商数据
-->>.同步SAP供应商新数据
MERGE INTO dbo.SM_Supplier t
USING
( SELECT *
FROM dbo.ZFTPO0020 sap
WHERE sap.MASTERDATA_RESULT IS NULL
AND (sap.OGUID IS NULL OR sap.OGUID ='')
AND NOT EXISTS(SELECT 1 FROM dbo.SM_Supplier s WHERE dbo.ToGuid(sap.ZGUID) = s.Supplierid )
) AS s
ON s.ZGUID = t.SapId
--匹配，且数据有效 更新
WHEN MATCHED AND s.MASTERDATA_DATASTATUS = 'A'
AND s.DELETE_FLAG = N'否' THEN
UPDATE SET
/*不需要更新的
t.FromType=1,--代表sap
t.Ishassuperior='否',
t.Superiorid='00000000-0000-0000-0000-000000000000',
t.Regcompanyid=1,
t.SapId=s.ZGUID,
t.Supplierid = dbo.ToGuid(s.ZGUID) ,
*/

/*找不到对应关系的
t.Supplierno = NULL,
t.Superiorname=NULL,
t.Enterpriselevel=NULL,
t.Comapnysize=NULL,
t.Naturecode=NULL,
t.Nation=NULL,
t.QualificationNumber=NULL,
t.TaxregistrationNo=NULL,
t.BiddingArea=NULL,
t.BiddingAreaId=NULL,
t.LegalPersonNature=NULL,
t.CanissueInvoiceNature=NULL,ss
t.InvoiceTaxRate=NULL,
t.GovernmentMonoPoly=NULL,
t.CanissueInvoiceNatureList=NULL,
t.InvoiceTaxRateList=NULL,
t.LegalPersonPhone=NULL,
t.SurveyTeamLeader=NULL,
t.SurveyPersonNel=NULL,
t.SurveyDate=NULL,
t.SurveyreMark=NULL,
t.InspectionDep=NULL,
t.InspectionLevel=NULL,
t.TaxPayerIdentificationNumber=NULL,
t.TaxNum=NULL,
t.Position=NULL,
t.CreditcertificatevalId=NULL,
t.EnterpriseArchitecture=NULL,
t.TaxpayerQualification=NULL,
*/
t.Suppliername = s.LIFNR_NAME ,
t.Officeaddress = s.TEL_ADDRESS ,
t.Enterpriselegalperson = s.LEGAL_NAME ,--法人姓名
t.Contractperson = s.LEGAL_NAME ,--合同联系人
t.Licenceno = s.LIFNR ,
t.Licenceavailable = s.BUS_LICENCE_ENDDT ,
t.Registeramt = s.REG_CAPITAL ,
t.Buliddate = s.CREATE_DATE ,
t.City = s.CITY ,
t.Postcode = s.COM_TAX ,
t.Telno = s.COM_PHONE ,
t.Faxno = s.COM_TAX ,
t.Email = s.REC_EMAIL ,
t.Website = s.WEBSITE ,
t.Businesslicense = s.BUS_LICENCE ,
t.Corporatetelno = s.LEGAL_PHONE ,
t.Telephone = s.LEGAL_PHONE ,
t.LicenseStartDate = s.BUS_LICENCE_BEGDT ,
t.Isattached = s.ZSFZGS ,
t.Controlentrustname = s.KC_ORGMAN,
t.Recordstatus='Active',
t.Modifydate= GETDATE(),
t.Modifyuserid=2

--匹配数据逻辑删除或物理删除,删除
WHEN MATCHED AND ( s.MASTERDATA_DATASTATUS = 'I'
OR s.DELETE_FLAG = N'是'
) THEN
DELETE
--不匹配，且数据有效，新增
WHEN NOT MATCHED AND s.MASTERDATA_DATASTATUS = 'A'
AND s.DELETE_FLAG = N'否' THEN
INSERT ( Supplierid ,
SapId,
Suppliername ,
Ishassuperior ,
Superiorid ,
Regcompanyid ,
Officeaddress ,
Enterpriselegalperson ,
Contractperson ,
Licenceno ,
Licenceavailable ,
Registeramt ,
Buliddate ,
City ,
Postcode ,
Telno ,
Faxno ,
Email ,
Website ,
Businesslicense ,
Corporatetelno ,
Telephone ,
LicenseStartDate ,
Isattached ,
Controlentrustname,
Recordstatus,
Createuserid,
Createdate
)
VALUES ( dbo.ToGuid(s.ZGUID) ,
s.ZGUID ,
s.LIFNR_NAME ,
'N' ,
'00000000-0000-0000-0000-000000000000' ,
1 ,
s.TEL_ADDRESS ,
s.LEGAL_NAME ,
s.LEGAL_NAME ,
s.LIFNR ,
s.BUS_LICENCE_ENDDT ,
s.REG_CAPITAL ,
s.CREATE_DATE ,
s.CITY ,
s.COM_TAX ,
s.COM_PHONE ,
s.COM_TAX ,
s.REC_EMAIL ,
s.WEBSITE ,
s.BUS_LICENCE ,
s.LEGAL_PHONE ,
s.LEGAL_PHONE ,
s.BUS_LICENCE_BEGDT ,
s.ZSFZGS ,
s.KC_ORGMAN,
'Active',
1,
GETDATE()
);
-->>.回调更新(供应商主数据状态)
UPDATE sap20
SET MASTERDATA_RESULT = 'Success'
FROM dbo.ZFTPO0020 sap20
WHERE sap20.MASTERDATA_RESULT IS NULL
AND (sap20.OGUID IS NULL OR sap20.OGUID ='')
AND NOT EXISTS(SELECT 1 FROM dbo.SM_Supplier s WHERE dbo.ToGuid(sap20.ZGUID) = s.Supplierid );

-->>.同步SAP供应商老数据
MERGE INTO dbo.SM_Supplier t
USING
( SELECT *
FROM dbo.ZFTPO0020 sap
WHERE sap.MASTERDATA_RESULT IS NULL
AND sap.OGUID IS NOT NULL
AND LEN(OGUID) = 36
AND EXISTS ( SELECT 1
FROM ( SELECT OGUID
FROM ZFTPO0020
WHERE LEN(OGUID) = 36
GROUP BY OGUID
HAVING COUNT(1) = 1
) AS sap_dist
WHERE sap_dist.OGUID = sap.OGUID )
) AS s
ON s.OGUID = CAST(t.Supplierid AS NVARCHAR(36))
--匹配,更新(老数据SAP这边只处理核心关系数据)
WHEN MATCHED THEN
UPDATE SET
t.SapId = ( CASE WHEN s.MASTERDATA_DATASTATUS = 'A'
AND s.DELETE_FLAG = N'否'
THEN s.ZGUID
ELSE NULL
END )
--不匹配且数据有效,新增
WHEN NOT MATCHED AND s.MASTERDATA_DATASTATUS = 'A'
AND s.DELETE_FLAG = N'否' THEN
INSERT ( Supplierid ,
SapId ,
Suppliername ,
Ishassuperior ,
Superiorid ,
Regcompanyid ,
Officeaddress ,
Enterpriselegalperson ,
Contractperson ,
Licenceno ,
Licenceavailable ,
Registeramt ,
Buliddate ,
City ,
Postcode ,
Telno ,
Faxno ,
Email ,
Website ,
Businesslicense ,
Corporatetelno ,
Telephone ,
LicenseStartDate ,
Isattached ,
Controlentrustname,
Recordstatus,
Createuserid,
Createdate
)
VALUES ( s.OGUID ,
s.ZGUID ,
s.LIFNR_NAME ,
'N' ,
'00000000-0000-0000-0000-000000000000' ,
1 ,
s.TEL_ADDRESS ,
s.LEGAL_NAME ,
s.LEGAL_NAME ,
s.LIFNR ,
s.BUS_LICENCE_ENDDT ,
s.REG_CAPITAL ,
s.CREATE_DATE ,
s.CITY ,
s.COM_TAX ,
s.COM_PHONE ,
s.COM_TAX ,
s.REC_EMAIL ,
s.WEBSITE ,
s.BUS_LICENCE ,
s.LEGAL_PHONE ,
s.LEGAL_PHONE ,
s.BUS_LICENCE_BEGDT ,
s.ZSFZGS ,
s.KC_ORGMAN,
'Active',
1,
GETDATE()
);
-->>.回调更新(供应商主数据状态)
UPDATE sap20
SET MASTERDATA_RESULT = 'Success'
FROM dbo.ZFTPO0020 sap20
WHERE sap20.MASTERDATA_RESULT IS NULL
AND sap20.OGUID IS NOT NULL
AND LEN(sap20.OGUID) = 36
AND EXISTS ( SELECT 1
FROM ( SELECT OGUID
FROM ZFTPO0020
WHERE LEN(OGUID) = 36
GROUP BY OGUID
HAVING COUNT(1) = 1
) AS sap_dist
WHERE sap_dist.OGUID = sap20.OGUID );

--2.依据sap46关系表同步更新供应商数据
MERGE INTO dbo.SM_Supplier t
USING
( SELECT sap46.OGUID1 ,
sap46.OGUID2 ,
sap46.MASTERDATA_DATASTATUS ,
s.Supplierid ,
s.SapId
FROM dbo.ZFTPO0046 sap46
INNER JOIN dbo.SM_Supplier s ON s.Supplierid = sap46.OGUID2
WHERE sap46.MASTERDATA_RESULT IS NULL
) s
ON s.OGUID1 = t.Supplierid
--匹配,则更新
WHEN MATCHED THEN
UPDATE SET
t.SapId = ( CASE WHEN s.MASTERDATA_DATASTATUS = 'A'
THEN s.SapId
ELSE NULL
END ),
t.Modifyuserid=2,
t.Modifydate=GETDATE();

-->>.回调更新(供应商关系数据状态)
UPDATE sap46
SET MASTERDATA_RESULT = 'Success'
FROM dbo.ZFTPO0046 sap46
WHERE sap46.MASTERDATA_RESULT IS NULL;


--3.同步供应商账户数据

END;
GO