Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ** Object: StoredProcedure [fin].[ExectGO_importIncome] Script DATE: 03/27/2015 09:35:25 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER PROCEDURE [fin].[ExectGO_importIncome]
- @GroupOperationId BIGINT
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @MONTH DATETIME2
- , @OperationTypeId INT
- , @MonthEnd DATETIME2
- SELECT TOP 1 @MONTH = DateStart
- FROM fin.GroupOperation
- WHERE Id = @GroupOperationId
- SELECT TOP 1 @OperationTypeId = ot.Id
- FROM fin.OperationType AS ot
- WHERE Code = 'income'
- INSERT fin.AccountChargedRaised
- ( AccountId ,
- MONTH ,
- Charged ,
- Raised,
- GroupOperationId
- )
- SELECT a.Id ,
- @MONTH,
- SUM(ISNULL(d.Charged, 0) + ISNULL(d.Recalculation,0)),
- SUM(ISNULL(d.Raised, 0)),
- @groupOperationId
- FROM no.sr$Income_alt d
- JOIN no.sr$Service_alt s ON d.ServiceId = s.Id
- JOIN no.sr$ServiceGroupsHC g ON s.GroupId = g.id
- JOIN fin.vw_Account a ON d.ObjectId = a.ObjectId
- AND a.StatusCode IN ('created', 'opened')
- AND a.DateStart < @MonthEnd AND (a.DateEnd IS NULL OR a.DateEnd >= @MonthEnd)
- WHERE UPPER(g.name) = UPPER('Капитальный ремонт после 271-ФЗ')
- AND d.[MONTH] = @MONTH
- GROUP BY a.Id, d.[MONTH]
- IF @@ROWCOUNT > 0 BEGIN
- print 'Удаление мелких операций fin.Operation внутри групповой'
- DELETE o
- FROM fin.Operation o
- JOIN fin.vw_Account a ON a.Id = o.AccountId
- WHERE o.GroupOperationId = @groupOperationId AND a.StatusCode IN ('created', 'opened')
- DBCC CHECKIDENT('fin.Operation', RESEED, 0) WITH NO_INFOMSGS
- DBCC CHECKIDENT('fin.Operation', RESEED) WITH NO_INFOMSGS
- INSERT fin.Operation
- ( Created ,
- AccountId ,
- OperationId ,
- SUM ,
- GroupOperationId ,
- DateTransaction
- )
- SELECT GETDATE() ,
- AccountId ,
- @OperationTypeId ,
- Raised ,
- @groupOperationId ,
- @MONTH
- FROM fin.AccountChargedRaised
- WHERE GroupOperationId = @GroupOperationId
- END
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement