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