88 lines
2.4 KiB
Transact-SQL
88 lines
2.4 KiB
Transact-SQL
USE RedApp
|
|
GO
|
|
|
|
BEGIN TRY
|
|
|
|
/* set RP, LinkType and LIE values here */
|
|
-- RP company number
|
|
DECLARE @rp INT = __RP_Nr__
|
|
DECLARE @rpKey INT = ( SELECT rp.Pa_PK FROM PA_Pa rp WHERE rp.CompanyNr = @rp )
|
|
|
|
-- LinkType code
|
|
DECLARE @typ NVARCHAR(20) = 'RP_to_LIE'
|
|
DECLARE @typKey INT = ( SELECT CoLink_PK FROM PA_CoLink WHERE Code = @typ )
|
|
|
|
-- LIE company number
|
|
DECLARE @lie INT = __LIE_Nr__
|
|
DECLARE @lieKey INT = ( SELECT lie.Pa_PK FROM PA_Pa lie WHERE lie.CompanyNr = @lie )
|
|
|
|
--DECLARE @parent INT = 0
|
|
|
|
-- error vars
|
|
DECLARE @err NVARCHAR(MAX) = ''
|
|
DECLARE @errFound BIT = 0
|
|
|
|
/* validation on values provided */
|
|
-- RP exists
|
|
IF (@rpKey IS NULL)
|
|
BEGIN
|
|
SET @err = 'RechnungsPartner with CompanyNr ' + CAST(@rp AS varchar(10)) + ' does NOT exist - cannot continue'
|
|
SET @errFound = 1
|
|
END
|
|
|
|
-- LinkType exists
|
|
IF (@typKey IS NULL)
|
|
BEGIN
|
|
SET @err = ISNULL(' | ' + @err, '') + 'Partner LinkType ' + @typ + ' does NOT exist - cannot continue'
|
|
SET @errFound = 1
|
|
END
|
|
|
|
-- LIE exists
|
|
IF (@lieKey IS NULL)
|
|
BEGIN
|
|
SET @err = ISNULL(' | ' + @err, '') + 'Lieferant with CompanyNr ' + CAST(@rp AS varchar(10)) + ' does NOT exist - cannot continue'
|
|
SET @errFound = 1
|
|
END
|
|
|
|
-- if error found then throw error
|
|
IF (@errFound = 1)
|
|
BEGIN
|
|
RAISERROR (@err, 16, 1)
|
|
END
|
|
|
|
-- show possible values
|
|
SELECT @rp AS RP, @rpKey AS RP_Key, rp.ShortName AS RP_Name, @typ AS LinkType,
|
|
@lie AS LIE, lie.Pa_PK AS LIE_Key, lie.ShortName AS LIE_Name
|
|
FROM PA_Pa rp
|
|
CROSS JOIN PA_Pa lie
|
|
WHERE rp.Pa_PK = @rpKey AND lie.Pa_PK = @lieKey
|
|
|
|
BEGIN TRANSACTION
|
|
|
|
-- show current data
|
|
SELECT 'BEFORE' AS Stat, PaLink_PK, Pa_FK, @rp AS RP_CompanyNr, CoLink_FK, @typ AS LinkType, Linked_Pa_FK, @lie AS LIE_CompanyNr, Db_Status, Db_InsDt, Db_InsUser, Db_UpdDt, Db_UpdUser
|
|
FROM PA_PaLink
|
|
WHERE Pa_FK = @rpKey OR Linked_Pa_FK = @lieKey
|
|
|
|
-- insert into PA_PaLink
|
|
INSERT INTO PA_PaLink (Pa_FK, CoLink_FK, Linked_Pa_FK)
|
|
SELECT @rpKey AS Pa_FK, @typKey AS CoLink_FK, @lieKey AS Linked_Pa_FK
|
|
|
|
-- show results after insert
|
|
SELECT 'AFTER' as Stat, PaLink_PK, Pa_FK, @rp AS RP_CompanyNr, CoLink_FK, @typ AS LinkType, Linked_Pa_FK, @lie AS LIE_CompanyNr, Db_Status, Db_InsDt, Db_InsUser, Db_UpdDt, Db_UpdUser
|
|
FROM PA_PaLink
|
|
WHERE Pa_FK = @rpKey OR Linked_Pa_FK = @lieKey
|
|
|
|
|
|
--
|
|
ROLLBACK TRANSACTION
|
|
-- COMMIT TRANSACTION
|
|
|
|
--SELECT * FROM PA_Pa WHERE Parent_Pa_FK = ( SELECT Pa_PK FROM PA_Pa WHERE CompanyNr = @parent ) ORDER BY CompanyNr
|
|
|
|
END TRY
|
|
|
|
BEGIN CATCH
|
|
EXECUTE [master].dbo.sp_RethrowError
|
|
END CATCH
|