ErrorMessage A cursor with the name db_cursorAsset already exists
-- =============================================
-- Author:
-- Create date:
-- Description: Update the cost center of an order of an asset
-- =============================================
CREATE PROCEDURE [dbo].[spImportOrderCostCenterJobSelect]
@IDImportFile typIDImportFile
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SpName VARCHAR(100) = OBJECT_NAME(@@PROCID)
DECLARE @TranCount INT = @@TRANCOUNT
DECLARE @ReturnMessage typReturnMessage
DECLARE @IDImportOrder [int]
DECLARE @OrderNumber typBlob
DECLARE @CostCenter typTitle
DECLARE @IDBlobItem typIDBlobItem
DECLARE @IDBlobItemOrder typIDBlobItem
DECLARE @UrlRandom typUrlRandom
DECLARE @IDCostCenter typIDCostCenter
DECLARE @IDCustomerSubContractor typIDCustomer
DECLARE @ProcessCode typProcessCode
DECLARE @ProcessDescription typProcessDescription
DECLARE @ProcessDate datetimeoffset(7)
DECLARE @IDLanguage typIDLanguage
DECLARE @IDWebSite typIDWebSite
DECLARE @IDZone typIDZone
DECLARE @IDSection typIDSection
DECLARE @IDLogin typIDLogin
DECLARE @IDCustomerJoin typIDCustomer
DECLARE @IDCustomerSearch TABLE (IDCustomer INT UNIQUE)
BEGIN TRY
-----------------------------
-- LOGIN DATA
-----------------------------
SELECT @IDWebSite = tif.IDWebSite, @IDZone = tif.IDZone, @IDSection = tif.IDSection, @IDLogin = tif.IDLogin
FROM tabImportFile AS tif
WHERE tif.IDImportFile = @IDImportFile
SELECT @IDLanguage = ISNULL(tc.IDLanguage, 'ENG')
FROM tabLogin AS tl
LEFT JOIN tabCustomer AS tc ON tc.IDCustomer = tl.IDCustomer
WHERE tl.IDLogin = @IDLogin
SET @IDCustomerJoin = dbo.sfBlobItemCustomerGet(@IDWebSite, @IDZone, @IDSection, @IDLogin, 'ORD')
IF @IDCustomerJoin IS NOT NULL
INSERT INTO @IDCustomerSearch (IDCustomer) SELECT IDCustomer FROM dbo.sfTableCustomer(@IDWebSite, @IDZone, @IDSection, @IDLogin, 'ORD')
DECLARE db_cursor CURSOR LOCAL FOR
SELECT tiocc.IDImportOrder, tiocc.OrderNumber, tiocc.CostCenter
FROM tabImportOrderCostCenter AS tiocc
WHERE tiocc.IDImportFile = @IDImportFile
AND tiocc.ProcessCode = 0
ORDER BY tiocc.IDImportOrder
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @IDImportOrder, @OrderNumber, @CostCenter
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ProcessDate = GETUTCDATE()
--SET @IDBlobItem = NULL
--SET @UrlRandom = NULL
SET @IDCostCenter = NULL
--SET @IDCustomerSubContractor = NULL
-----------------------------
-- ORDER CHECK
-----------------------------
IF @IDCustomerJoin IS NOT NULL
SELECT @IDBlobItemOrder = tbi.IDBlobItem
FROM tabBlobItem AS tbi
INNER JOIN tabBlob AS tb ON tb.IDBlob = tbi.IDBlob
INNER JOIN tabBusinessZoneProcess AS tbzp ON tbzp.IDBusinessZoneProcess = tbi.IDBusinessZoneProcess
INNER JOIN tabBusinessZoneProcessWebSiteZoneSection AS tbzpwszs ON tbzpwszs.IDBusinessZoneProcess = tbzp.IDBusinessZoneProcess
AND tbzpwszs.IDWebSite = @IDWebSite
AND tbzpwszs.IDZone = @IDZone
AND tbzpwszs.IDSection = @IDSection
INNER JOIN tabProcess AS tp ON tp.IDProcess = tbzp.IDProcess
INNER JOIN tabProcessType AS tpt ON tpt.IDProcessType = tp.IDProcessType
INNER JOIN tabProcessStatus AS tps ON tps.IDProcess = tbi.IDProcess AND tps.IDStatus = tbi.IDStatus
INNER JOIN tabStatus AS ts ON ts.IDStatus = tps.IDStatus
INNER JOIN tabBlobItemOrder AS tbio ON tbio.IDBlobItem = tbi.IDBlobItem
INNER JOIN @IDCustomerSearch AS ics ON ics.IDCustomer = tb.IDCustomer
WHERE tpt.IDProcessTypeCategory = 'ORD'
AND ts.IDStatusType = 1
AND tb.Blob = @OrderNumber
ELSE
SELECT @IDBlobItemOrder = tbi.IDBlobItem
FROM tabBlobItem AS tbi
INNER JOIN tabBlob AS tb ON tb.IDBlob = tbi.IDBlob
INNER JOIN tabBusinessZoneProcess AS tbzp ON tbzp.IDBusinessZoneProcess = tbi.IDBusinessZoneProcess
INNER JOIN tabBusinessZoneProcessWebSiteZoneSection AS tbzpwszs ON tbzpwszs.IDBusinessZoneProcess = tbzp.IDBusinessZoneProcess
AND tbzpwszs.IDWebSite = @IDWebSite
AND tbzpwszs.IDZone = @IDZone
AND tbzpwszs.IDSection = @IDSection
INNER JOIN tabProcess AS tp ON tp.IDProcess = tbzp.IDProcess
INNER JOIN tabProcessType AS tpt ON tpt.IDProcessType = tp.IDProcessType
INNER JOIN tabProcessStatus AS tps ON tps.IDProcess = tbi.IDProcess AND tps.IDStatus = tbi.IDStatus
INNER JOIN tabStatus AS ts ON ts.IDStatus = tps.IDStatus
INNER JOIN tabBlobItemOrder AS tbio ON tbio.IDBlobItem = tbi.IDBlobItem
WHERE tpt.IDProcessTypeCategory = 'ORD'
AND ts.IDStatusType = 1
AND tb.Blob = @OrderNumber
IF @IDBlobItemOrder IS NULL
BEGIN
SET @ProcessCode = -1
SET @ProcessDescription = dbo.sfDictionaryGet(CAST(@SpName + '01' AS VARCHAR(50)), @IDLanguage)
GOTO NextRecord
END
-----------------------------
-- ASSET CHECK
-----------------------------
DECLARE db_cursorAsset CURSOR LOCAL FOR
SELECT tbi.IDBlobItem, tbi.UrlRandom, tbia.IDCustomerSubContractor
FROM tabBlobItem AS tbi
INNER JOIN tabBlob AS tb ON tb.IDBlob = tbi.IDBlob
INNER JOIN tabBlobItemCross AS tbic ON tbic.IDBlobItem = tbi.IDBlobItem
INNER JOIN tabBlobItem AS tbiFather ON tbiFather.IDBlobItem = tbic.IDBlobItemFather
INNER JOIN tabBusinessZoneProcess AS tbzp ON tbzp.IDBusinessZoneProcess = tbi.IDBusinessZoneProcess
INNER JOIN tabBusinessZoneProcessWebSiteZoneSection AS tbzpwszs ON tbzpwszs.IDBusinessZoneProcess = tbzp.IDBusinessZoneProcess
AND tbzpwszs.IDWebSite = @IDWebSite
AND tbzpwszs.IDZone = @IDZone
AND tbzpwszs.IDSection = @IDSection
INNER JOIN tabProcess AS tp ON tp.IDProcess = tbzp.IDProcess
INNER JOIN tabProcessType AS tpt ON tpt.IDProcessType = tp.IDProcessType
INNER JOIN tabProcessStatus AS tps ON tps.IDProcess = tbi.IDProcess AND tps.IDStatus = tbi.IDStatus
INNER JOIN tabStatus AS ts ON ts.IDStatus = tps.IDStatus
INNER JOIN tabBlobItemAsset AS tbia ON tbia.IDBlobItem = tbi.IDBlobItem
WHERE tpt.IDProcessTypeCategory = 'ASS'
AND ts.IDStatusType = 1
AND tbiFather.IDBlobItem = @IDBlobItemOrder
OPEN db_cursorAsset
FETCH NEXT FROM db_cursorAsset INTO @IDBlobItem , @UrlRandom, @IDCustomerSubContractor
WHILE @@FETCH_STATUS = 0
BEGIN
IF @IDBlobItem IS NULL
BEGIN
SET @ProcessCode = -1
SET @ProcessDescription = dbo.sfDictionaryGet(CAST(@SpName + '02' AS VARCHAR(50)), @IDLanguage)
GOTO NextRecord
END
IF @IDCustomerSubContractor IS NULL
BEGIN
SET @ProcessCode = -1
SET @ProcessDescription = dbo.sfDictionaryGet(CAST(@SpName + '03' AS VARCHAR(50)), @IDLanguage)
GOTO NextRecord
END
-----------------------------
-- COST CENTER CHECK
-----------------------------
IF @CostCenter IS NOT NULL
BEGIN
SELECT @IDCostCenter = tcc.IDCostCenter
FROM tabCostCenter AS tcc
LEFT JOIN tabCostCenterLanguage AS tccl ON tccl.IDCostCenter = tcc.IDCostCenter
WHERE tcc.IDCustomer = @IDCustomerSubContractor
AND ((tcc.CostCenterCode = @CostCenter) OR
(tccl.CostCenterDescription = @CostCenter) OR
(tcc.CostCenterDescription = @CostCenter))
AND tcc.[Deleted] = 0
IF @IDCostCenter IS NULL
BEGIN
SET @ProcessCode = -1
SET @ProcessDescription = dbo.sfDictionaryGet(CAST(@SpName + '04' AS VARCHAR(50)), @IDLanguage)
GOTO NextRecord
END
END
IF @TranCount = 0 BEGIN TRAN
ELSE SAVE TRAN @SpName
EXEC spBlobItemUpdate
@IDWebSite = @IDWebSite,
@IDZone = @IDZone,
@IDSection = @IDSection,
@IDLogin = @IDLogin,
@IDLanguage = @IDLanguage,
@TypeUpdate = 'COSTCENTER',
@IDBlobItem = @IDBlobItem,
@UrlRandom = @UrlRandom,
@IDCostCenter = @IDCostCenter,
@ReturnMessage = @ReturnMessage OUT
IF @ReturnMessage <> 'OK'
BEGIN
IF @TranCount > 0 ROLLBACK TRAN @SpName
ELSE IF @TranCount = 0 ROLLBACK TRAN
SET @ProcessCode = -1
SET @ProcessDescription = @ReturnMessage
GOTO NextRecord
END
FETCH NEXT FROM db_cursorAsset INTO @IDBlobItem , @UrlRandom, @IDCustomerSubContractor
END
CLOSE db_cursorAsset
DEALLOCATE db_cursorAsset
-----------------------------
SET @ProcessCode = 1
SET @ProcessDescription = 'OK'
IF @TranCount = 0 COMMIT TRAN
NextRecord:
UPDATE tabImportOrderCostCenter
SET
IDBlobItem = @IDBlobItemOrder,
ProcessCode = @ProcessCode,
ProcessDescription = @ProcessDescription,
ProcessDate = @ProcessDate
WHERE IDImportOrder = @IDImportOrder
FETCH NEXT FROM db_cursor INTO @IDImportOrder, @OrderNumber, @CostCenter
END
CLOSE db_cursor
DEALLOCATE db_cursor
EXEC [spImportFileJobResult]
@IDWebSite = @IDWebSite,
@IDZone = @IDZone,
@IDSection = @IDSection,
@IDLogin = @IDLogin,
@IDLanguage = @IDLanguage,
@IDImportFile = @IDImportFile,
@thCol1 = 'Order',
@thCol2 = 'CostCenterCode',
@thCol3 = '',
@Sql = 'DECLARE db_cursor CURSOR FOR SELECT TOP 500 IDImportOrder, OrderNumber, CostCenter, NULL, ProcessCode, ProcessDescription, ProcessDate FROM tabImportOrderCostCenter WHERE IDImportFile = @IDImportFile ORDER BY ProcessCode, 1'
END TRY
BEGIN CATCH
DECLARE @xState INT = XACT_STATE()
IF @xState = -1
ROLLBACK
IF @xState = 1 AND @TranCount = 0
ROLLBACK
IF @xState = 1 AND @TranCount > 0
ROLLBACK TRAN @SpName
SET @ReturnMessage = ISNULL(ERROR_PROCEDURE(), '-') + ': ' + ERROR_MESSAGE()
EXEC sysLogAdd @ErrorMessage = @ReturnMessage
END CATCH
END
