Notes

SELECT Id INTO #ActiveAssetIds FROM ConvertCSVToBigIntTable(@ActiveAssetIds, ',')

IF NOT EXISTS(SELECT TOP 1 1 FROM #ActiveAssetIds)--This check is added as inner join during update and insert
RETURN

CREATE TABLE #NegativeAssetsToBeScrapped
(Id BIGINT,
NewStatus NVARCHAR(30)
)

SELECT
PayableInvoiceAssets.AssetId,
PayableInvoiceAssets.Id
INTO #PIAssetIds
FROM PayableInvoiceAssets
WHERE PayableInvoiceAssets.PayableInvoiceId = @PayableInvoiceId
AND PayableInvoiceAssets.IsActive = 0

IF NOT EXISTS(SELECT TOP 1 1 FROM #PIAssetIds)
RETURN

IF EXISTS(SELECT TOP 1 1 FROM PayableInvoices WHERE PayableInvoices.ParentPayableInvoiceId IS NULL AND PayableInvoices.Id=@PayableInvoiceId AND PayableInvoices.Status='InActive')
BEGIN

INSERT INTO #NegativeAssetsToBeScrapped
SELECT Assets.Id
,@ScrapStatus AS NewStatus
FROM Assets
INNER JOIN #PIAssetIds ON #PIAssetIds.AssetId = Assets.Id
WHERE Assets.Status <> @ScrapStatus
AND Assets.FinancialType <> @NegativeDeposit

END

SELECT
#PIAssetIds.AssetId
,#PIAssetIds.Id
INTO #PIAssetInfo
FROM Assets
INNER JOIN #PIAssetIds ON #PIAssetIds.AssetId = Assets.Id
WHERE Assets.Status <> @ScrapStatus
AND Assets.FinancialType = @NegativeDeposit
AND Assets.Status <> @ErrorStatus

IF EXISTS(SELECT TOP 1 1 FROM #PIAssetInfo)
BEGIN

SELECT
PIAssets.AssetId
,PayableInvoiceDepositAssets.IsActive
,PayableInvoiceDepositTakeDownAssets.TakeDownAssetId
INTO #NegativeAssetInfo
FROM #PIAssetInfo PIAssets
INNER JOIN PayableInvoiceDepositTakeDownAssets ON PIAssets.Id = PayableInvoiceDepositTakeDownAssets.NegativeDepositAssetId
INNER JOIN PayableInvoiceDepositAssets ON PayableInvoiceDepositTakeDownAssets.PayableInvoiceDepositAssetId = PayableInvoiceDepositAssets.Id

IF EXISTS(SELECT TOP 1 1 FROM #NegativeAssetInfo)
BEGIN
INSERT INTO #NegativeAssetsToBeScrapped
SELECT
NegativeAsset.AssetId
,IIF(RealAssets.IsActive = 0 AND NegativeAsset.IsActive <> 0 , @ScrapStatus,@ErrorStatus) AS NewStatus
FROM #NegativeAssetInfo NegativeAsset
LEFT JOIN PayableInvoiceAssets AS RealAssets ON NegativeAsset.TakeDownAssetId = RealAssets.Id
END
END

IF EXISTS(SELECT TOP 1 1 FROM #NegativeAssetsToBeScrapped)
BEGIN

SELECT * INTO #FinalNegativeAssetsToBeScrapped FROM #NegativeAssetsToBeScrapped
WHERE NOT EXISTS(SELECT * FROM PayableInvoiceAssets WHERE AssetId = #NegativeAssetsToBeScrapped.Id and IsActive=1 )
AND EXISTS((SELECT Id FROM #ActiveAssetIds WHERE #NegativeAssetsToBeScrapped.Id = #ActiveAssetIds.Id))

UPDATE Assets SET Status = #FinalNegativeAssetsToBeScrapped.NewStatus,PropertyTaxCost_Amount = 0.0,UpdatedById = @UpdatedById,UpdatedTime = @UpdatedTime
FROM Assets A
INNER JOIN #FinalNegativeAssetsToBeScrapped ON A.Id = #FinalNegativeAssetsToBeScrapped.Id

INSERT INTO AssetHistories
(
[Reason]
,[AsOfDate]
,[AcquisitionDate]
,[Status]
,[FinancialType]
,[SourceModule]
,[SourceModuleId]
,[CreatedById]
,[CreatedTime]
,[UpdatedById]
,[UpdatedTime]
,[CustomerId]
,[ParentAssetId]
,[LegalEntityId]
--,[ContractId]
,[AssetId]
,[IsReversed]
,[PropertyTaxReportCodeId])
SELECT
@Reason,
@InvoiceDate,
Assets.AcquisitionDate,
NewStatus,
Assets.FinancialType,
@SourceModule,
@PayableInvoiceId,
@UpdatedById,
@UpdatedTime,
NULL,
NULL,
Assets.CustomerId,
Assets.ParentAssetId,
Assets.LegalEntityId,
--NULL,
Assets.Id,
0,
Assets.PropertyTaxReportCodeId
FROM Assets
INNER JOIN #FinalNegativeAssetsToBeScrapped ON Assets.Id = #FinalNegativeAssetsToBeScrapped.Id
END