SQLize Online / PHPize Online  /  SQLtest Online

DECLARE @idCOCertificateState INT = 319092150 DECLARE @bMiddleLayer INT = 1 declare @idCOCertificate int = (select idCOCertificate from ftCOCertificateState where idCOCertificateState = @idCOCertificateState) declare @idFirstCOCertificateState int set @idFirstCOCertificateState = isnull(@idFirstCOCertificateState, [dbo].[fn_GetFirstCOCertificateState](@idCOCertificate)) declare @dFirstDateIn datetime, @dFirstDateOut datetime select @dFirstDateIn = dDateIn, @dFirstDateOut = dDateOut from vftCOCertificateState_listall where idCOCertificateState = @idFirstCOCertificateState IF (@bMiddleLayer = 1 AND @dFirstDateOut IS NULL) BEGIN SET @dFirstDateOut = GETDATE() END DECLARE @BPKindCode nvarchar(max) = (select bp.BPKindCode from [dbo].[KK_CREDITREQUESTS] cr left join [dbo].[KK_BPKind] bp on cr.BPKindID = bp.ID where cr.lmdealstate=@idCOCertificateState) declare @tbCurrencyOperationAll table ( [idCurrencyOperationState] [int] NULL, [idCurrencyOperation] [int] NULL, [sDocNumber] [varchar](50) NULL, [dDocDate] [datetime] NULL, [sSurrogat] [varchar](25) NULL, [bOperPrizPc95] [bit] NULL, [dDateOperation] [datetime] NULL, [idPaymentType] [int] NULL, [sPaymentType_BCode] [varchar](100) NULL, [sPaymentType_Code] [varchar](100) NULL, [sPaymentType_Name] [varchar](150) NULL, [idKVVO] [int] NULL, [sKVVO_BCode] [varchar](100) NULL, [sKVVO_Code] [varchar](100) NULL, [sKVVO_Name] [varchar](150) NULL, [mOperationSum] [money] NULL, [fRate] [float] NULL, [idCurrency] [int] NULL, [sCurrency_BCode] [varchar](100) NULL, [sCurrency_Code] [varchar](100) NULL, [sCurrency_Name] [varchar](150) NULL, [sCurrency_OkvNum] [char](3) NULL, [dEditDate] [datetime] NULL, [sPassportNumber] [varchar](50) NULL, [sContractNumber] [nvarchar](250) NULL, [dContractDate] [datetime] NULL, [mContractSum] [money] NULL, [idContractCurrency] [int] NULL, [sContractCurrency_BCode] [varchar](100) NULL, [sContractCurrency_Code] [varchar](100) NULL, [sContractCurrency_Name] [varchar](150) NULL, [sContractCurrency_OkvNum] [char](3) NULL, [dExpectedDate] [datetime] NULL, [idContractBankCountry] [int] NULL, [sContractBankCountry_BCode] [varchar](100) NULL, [sContractBankCountry_Code] [varchar](100) NULL, [sContractBankCountry_Name] [varchar](150) NULL, [sContractBankCountry_NumberCode] [varchar](3) NULL, [bAllowContractSum] [bit] NULL, [idPaymentOrder] [int] NULL, [idSimpleRequest] [int] NULL, [iRowNumber] [int] NULL, idContractState int NULL, idClient int NULL, idSourceObject int NULL, bAutorizedWithoutContract bit NULL, idCOCertificateState int NULL, [dReissueDocDate] DATETIME NULL, dReturnDate DATETIME NULL ,idDocType int null ,idAccountCurrency int NULL ,sBankCountry_NumberCode NVARCHAR(3) NULL ,sDocType_Code varchar(100) NULL /*PaymentDocType, */ /*SimpleRequestDocType*/ ) insert into @tbCurrencyOperationAll select fcos.[idCurrencyOperationState] /*ID*/ ,fcos.[idCurrencyOperation] /*ID*/ ,fcos.[sDocNumber] /*Номер расчетного документа*/ ,fcos.[dDocDate] /*Дата расчетного документа*/ ,fcos.[sSurrogat] ,fcos.[bOperPrizPc95] ,fcos.[dDateOperation] /*Дата операции*/ ,fcos.[idPaymentType] /*ID признака платежа*/ ,fcos.sPaymentType_BCode ,fcos.sPaymentType_Code ,fcos.sPaymentType_Name ,fcos.[idKVVO] /*ID кода вида ВО*/ ,fcos.sKVVO_BCode ,fcos.sKVVO_Code ,fcos.sKVVO_Name ,fcos.[mOperationSum] /*Сумма операции*/ ,fcos.[fRate] /*Курс пересчета*/ ,fcos.[idCurrency] /*ID валюты операции*/ ,fcos.sCurrency_BCode ,fcos.sCurrency_Code ,fcos.sCurrency_Name ,fcos.sCurrency_OkvNum ,fcos.[dEditDate] ,fcos.[sPassportNumber] /*Номер ПС*/ ,fcos.[sContractNumber] /*Номер контракта*/ ,fcos.[dContractDate] /*Дата контракта*/ ,fcos.[mContractSum] /*Сумма операции в валюте ВК/КД*/ ,fcos.[idContractCurrency] /*ID валюты контракта*/ ,fcos.sContractCurrency_BCode ,fcos.sContractCurrency_Code ,fcos.sContractCurrency_Name ,fcos.sContractCurrency_OkvNum /*Числовой код валюты (810, 840, 978, и т.д.)*/ ,fcos.[dExpectedDate] /*Ожидаемый срок*/ ,fcos.[idContractBankCountry] /*ID кода страны банка-нерезидента отправителя/получателя платежа*/ ,fcos.sContractBankCountry_BCode ,fcos.sContractBankCountry_Code ,fcos.sContractBankCountry_Name ,fcos.sContractBankCountry_NumberCode ,fcos.[bAllowContractSum] /*логическое значение, определяет учитывать/не учитывать операцию по ПД в сумме контракта*/ ,fcos.idPaymentOrder ,fcos.idSimpleRequest ,fcos.iRowNumber ,ccs.idContractState ,ccs.idClient ,fcos.idSourceObject ,fcos.bAutorizedWithoutContract ,@idCOCertificateState as idCOCertificateState ,fcos.dReissueDocDate ,fcos.dReturnDate ,fcos.idDocType ,fcos.idAccountCurrency ,T6.sNumberCode /*sBankCountry_NumberCode*/ ,NULL /* sDocType_Code . для операций с СВО - NULL*/ from dbo.[vftCurrencyOperation_listALL_COCertificate] fcos join dbo.ftContractState ccs ON ccs.idContractState = [dbo].[fn_GetActualContractState] (fcos.idContract, null) JOIN dbo.ftCOCertificateState cs ON cs.idCOCertificateState = fcos.idCOCertificateState LEFT JOIN dbo.vdrCountry T6 ON T6.idCountry = cs.idBankCountry where fcos.idCOCertificateState = @idCOCertificateState and fcos.idContract is not null AND fcos.bPrint = 1 AND fcos.sPassportNumber IS NOT NULL /*только для строк с УНК*/ /* Если xml формируется не на этапе авторизациии, то нам надо забрать операции со связанных платежек и симпл рекустов*/ if (@bMiddleLayer = 1) BEGIN insert into @tbCurrencyOperationAll select fcos.[idCurrencyOperationState] /*ID*/ ,fcos.[idCurrencyOperation] /*ID*/ ,fcos.[sDocNumber] /*Номер расчетного документа*/ ,fcos.[dDocDate] /*Дата расчетного документа*/ ,fcos.[sSurrogat] ,fcos.[bOperPrizPc95] ,fcos.[dDateOperation] /*Дата операции*/ ,fcos.[idPaymentType] /*ID признака платежа*/ ,fcos.sPaymentType_BCode ,fcos.sPaymentType_Code ,fcos.sPaymentType_Name ,fcos.[idKVVO] /*ID кода вида ВО*/ ,fcos.sKVVO_BCode ,fcos.sKVVO_Code ,fcos.sKVVO_Name ,fcos.[mOperationSum] /*Сумма операции*/ ,fcos.[fRate] /*Курс пересчета*/ ,fcos.[idCurrency] /*ID валюты операции*/ ,fcos.sCurrency_BCode ,fcos.sCurrency_Code ,fcos.sCurrency_Name ,fcos.sCurrency_OkvNum ,fcos.[dEditDate] ,fcos.[sPassportNumber] /*Номер ПС*/ ,fcos.[sContractNumber] /*Номер контракта*/ ,fcos.[dContractDate] /*Дата контракта*/ ,fcos.[mContractSum] /*Сумма операции в валюте ВК/КД*/ ,fcos.[idContractCurrency] /*ID валюты контракта*/ ,fcos.sContractCurrency_BCode ,fcos.sContractCurrency_Code ,fcos.sContractCurrency_Name ,fcos.sContractCurrency_OkvNum /*Числовой код валюты (810, 840, 978, и т.д.)*/ ,fcos.[dExpectedDate] /*Ожидаемый срок*/ ,fcos.[idContractBankCountry] /*ID кода страны банка-нерезидента отправителя/получателя платежа*/ ,fcos.sContractBankCountry_BCode ,fcos.sContractBankCountry_Code ,fcos.sContractBankCountry_Name ,fcos.sContractBankCountry_NumberCode ,fcos.[bAllowContractSum] /*логическое значение, определяет учитывать/не учитывать операцию по ПД в сумме контракта*/ ,fcos.idPaymentOrder ,fcos.idSimpleRequest ,fcos.iRowNumber ,ccs.idContractState ,ccs.idClient ,rl.idObject2 as idSourceObject ,fcos.bAutorizedWithoutContract ,@idCOCertificateState as idCOCertificateState ,fcos.dReissueDocDate ,fcos.dReturnDate ,fcos.idDocType ,fcos.idAccountCurrency ,T6.sNumberCode /*sBankCountry_NumberCode*/ ,'SimpleRequestDocType' /* sDocType_Code . для операций с СВО - NULL*/ from vftCurrencyOperation_listALL_SimpleRequest fcos join dbo.rlObjectRelation rl on rl.idObject2 = fcos.idSimpleRequest join dbo.ftContractState ccs ON ccs.idContractState = [dbo].[fn_GetActualContractState] (fcos.idContract, null) JOIN dbo.ftCOCertificateState cs ON cs.idCOCertificateState = rl.idObject1 LEFT JOIN dbo.vdrCountry T6 ON T6.idCountry = cs.idBankCountry where rl.idObject1 = @idCOCertificateState and fcos.idContract is not null -- ТУТ БОЛЬШОЙ ВОПРОС С idContract AND fcos.bPrint = 1 AND fcos.sPassportNumber IS NOT NULL /*только для строк с УНК*/ insert into @tbCurrencyOperationAll select fcos.[idCurrencyOperationState] /*ID*/ ,fcos.[idCurrencyOperation] /*ID*/ ,fcos.[sDocNumber] /*Номер расчетного документа*/ ,fcos.[dDocDate] /*Дата расчетного документа*/ ,fcos.[sSurrogat] ,fcos.[bOperPrizPc95] ,fcos.[dDateOperation] /*Дата операции*/ ,fcos.[idPaymentType] /*ID признака платежа*/ ,fcos.sPaymentType_BCode ,fcos.sPaymentType_Code ,fcos.sPaymentType_Name ,fcos.[idKVVO] /*ID кода вида ВО*/ ,fcos.sKVVO_BCode ,fcos.sKVVO_Code ,fcos.sKVVO_Name ,fcos.[mOperationSum] /*Сумма операции*/ ,fcos.[fRate] /*Курс пересчета*/ ,fcos.[idCurrency] /*ID валюты операции*/ ,fcos.sCurrency_BCode ,fcos.sCurrency_Code ,fcos.sCurrency_Name ,fcos.sCurrency_OkvNum ,fcos.[dEditDate] ,fcos.[sPassportNumber] /*Номер ПС*/ ,fcos.[sContractNumber] /*Номер контракта*/ ,fcos.[dContractDate] /*Дата контракта*/ ,fcos.[mContractSum] /*Сумма операции в валюте ВК/КД*/ ,fcos.[idContractCurrency] /*ID валюты контракта*/ ,fcos.sContractCurrency_BCode ,fcos.sContractCurrency_Code ,fcos.sContractCurrency_Name ,fcos.sContractCurrency_OkvNum /*Числовой код валюты (810, 840, 978, и т.д.)*/ ,fcos.[dExpectedDate] /*Ожидаемый срок*/ ,fcos.[idContractBankCountry] /*ID кода страны банка-нерезидента отправителя/получателя платежа*/ ,fcos.sContractBankCountry_BCode ,fcos.sContractBankCountry_Code ,fcos.sContractBankCountry_Name ,fcos.sContractBankCountry_NumberCode ,fcos.[bAllowContractSum] /*логическое значение, определяет учитывать/не учитывать операцию по ПД в сумме контракта*/ ,fcos.idPaymentOrder ,fcos.idSimpleRequest ,fcos.iRowNumber ,ccs.idContractState ,ccs.idClient ,rl.idObject2 as idSourceObject ,fcos.bAutorizedWithoutContract ,@idCOCertificateState as idCOCertificateState ,fcos.dReissueDocDate ,fcos.dReturnDate ,fcos.idDocType ,fcos.idAccountCurrency ,T6.sNumberCode /*sBankCountry_NumberCode*/ ,'PaymentDocType' /* sDocType_Code . для операций с СВО - NULL*/ from vftCurrencyOperation_listALL_PaymentEdit fcos join dbo.rlObjectRelation rl on rl.idObject2 = fcos.idPaymentOrder join dbo.ftContractState ccs ON ccs.idContractState = [dbo].[fn_GetActualContractState] (fcos.idContract, null) JOIN dbo.ftCOCertificateState cs ON cs.idCOCertificateState = rl.idObject1 LEFT JOIN dbo.vdrCountry T6 ON T6.idCountry = cs.idBankCountry where rl.idObject1 = @idCOCertificateState and fcos.idContract is not null -- ТУТ БОЛЬШОЙ ВОПРОС С idContract AND fcos.bPrint = 1 AND fcos.sPassportNumber IS NOT NULL /*только для строк с УНК*/ END declare @tbObjectIds table(idObject int) declare @tbPaymentOrderIds table(idObject int, idPaymentOrder int, bIsArchive BIT) insert into @tbObjectIds select distinct fcos.idSourceObject from @tbCurrencyOperationAll fcos --where fcos.idCOCertificateState = @idCOCertificateState DECLARE @idObjectType_PaymentOrder INT = (select idType from drType(NOLOCK) where sCode = 'ObjectType_PaymentOrder') insert into @tbPaymentOrderIds select ids.idObject, p.idPaymentOrder, CASE WHEN NOT p.idPaymentOrder IS NULL THEN 0 ELSE 1 END from @tbObjectIds ids INNER JOIN ftObject o ON o.idObject = ids.idObject AND o.idObjectType = @idObjectType_PaymentOrder left join dbo.[ftPaymentOrder] p on ids.idObject = p.idPaymentOrder insert into @tbPaymentOrderIds select ids.idObject, rn.idPaymentOrder, CASE WHEN NOT p.idPaymentOrder IS NULL THEN 0 ELSE 1 END from @tbObjectIds ids join dbo.[ftSimpleRequest] sr on ids.idObject = sr.idSimpleRequest join rlObjectRelation rlOT_RN on rlOT_RN.idObject1 = sr.idSimpleRequest join ftSimpleRequestRevenueNotification rn on rlOT_RN.idObject2 = rn.idSimpleRequestRevenueNotification left join dbo.[ftPaymentOrder] p on ids.idObject = p.idPaymentOrder declare @tbCurrencyOperation table ( [idCurrencyOperationState] [int] NULL, [idCurrencyOperation] [int] NULL, [sDocNumber] [varchar](50) NULL, [dDocDate] [datetime] NULL, [sSurrogat] [varchar](25) NULL, [bOperPrizPc95] [bit] NULL, [dDateOperation] [datetime] NULL, [idPaymentType] [int] NULL, [sPaymentType_BCode] [varchar](100) NULL, [sPaymentType_Code] [varchar](100) NULL, [sPaymentType_Name] [varchar](150) NULL, [idKVVO] [int] NULL, [sKVVO_BCode] [varchar](100) NULL, [sKVVO_Code] [varchar](100) NULL, [sKVVO_Name] [varchar](150) NULL, [mOperationSum] [money] NULL, [fRate] [float] NULL, [idCurrency] [int] NULL, [sCurrency_BCode] [varchar](100) NULL, [sCurrency_Code] [varchar](100) NULL, [sCurrency_Name] [varchar](150) NULL, [sCurrency_OkvNum] [char](3) NULL, [dEditDate] [datetime] NULL, [sPassportNumber] [varchar](50) NULL, [sContractNumber] [nvarchar](250) NULL, [dContractDate] [datetime] NULL, [mContractSum] [money] NULL, [idContractCurrency] [int] NULL, [sContractCurrency_BCode] [varchar](100) NULL, [sContractCurrency_Code] [varchar](100) NULL, [sContractCurrency_Name] [varchar](150) NULL, [sContractCurrency_OkvNum] [char](3) NULL, [dExpectedDate] [datetime] NULL, [idContractBankCountry] [int] NULL, [sContractBankCountry_BCode] [varchar](100) NULL, [sContractBankCountry_Code] [varchar](100) NULL, [sContractBankCountry_Name] [varchar](150) NULL, [sContractBankCountry_NumberCode] [varchar](3) NULL, [bAllowContractSum] [bit] NULL, [idPaymentOrder] [int] NULL, [idSimpleRequest] [int] NULL, [iRowNumber] [int] NULL, idContractState int NULL, idSourceObject int NULL, bAutorizedWithoutContract bit NULL, [sBank_Name] [nvarchar](max) NULL, [sBank_BIK] [nvarchar](max) NULL, [sBank_SWIFT_BIC] [nvarchar](max) NULL, [sClient_Name] [nvarchar](max) NULL, [sClient_INN] [nvarchar](max) NULL, [sBankCountry_NumberCode] [nvarchar](3) NULL, [bBankCountry_NumberCode] [bit] NULL, idCOCertificateState int NULL, [dReissueDocDate] DATETIME NULL, dReturnDate DATETIME null ,idDocType int null ,idAccountCurrency int NULL ,bCPStatusFiniteNegative INT NULL --В случае, если есть связанный платежный документ на отклоненном статусе ,bRejectNonPaymentDocument BIT NULL ,bClosed BIT NULL -- -- на связанной операции незакрытый неплатёжный документ ) -- СВО insert into @tbCurrencyOperation select fcos.[idCurrencyOperationState] /*ID*/ ,fcos.[idCurrencyOperation] /*ID*/ ,fcos.[sDocNumber] /*Номер расчетного документа*/ ,fcos.[dDocDate] /*Дата расчетного документа*/ ,fcos.[sSurrogat] ,fcos.[bOperPrizPc95] ,fcos.[dDateOperation] /*Дата операции*/ ,fcos.[idPaymentType] /*ID признака платежа*/ ,fcos.sPaymentType_BCode ,fcos.sPaymentType_Code ,fcos.sPaymentType_Name ,fcos.[idKVVO] /*ID кода вида ВО*/ ,fcos.sKVVO_BCode ,fcos.sKVVO_Code ,fcos.sKVVO_Name ,fcos.[mOperationSum] /*Сумма операции*/ ,fcos.[fRate] /*Курс пересчета*/ ,fcos.[idCurrency] /*ID валюты операции*/ ,fcos.sCurrency_BCode ,fcos.sCurrency_Code ,fcos.sCurrency_Name ,fcos.sCurrency_OkvNum ,fcos.[dEditDate] ,fcos.[sPassportNumber] /*Номер ПС*/ ,fcos.[sContractNumber] /*Номер контракта*/ ,fcos.[dContractDate] /*Дата контракта*/ ,fcos.[mContractSum] /*Сумма операции в валюте ВК/КД*/ ,fcos.[idContractCurrency] /*ID валюты контракта*/ ,fcos.sContractCurrency_BCode ,fcos.sContractCurrency_Code ,fcos.sContractCurrency_Name ,fcos.sContractCurrency_OkvNum /*Числовой код валюты (810, 840, 978, и т.д.)*/ ,fcos.[dExpectedDate] /*Ожидаемый срок*/ ,fcos.[idContractBankCountry] /*ID кода страны банка-нерезидента отправителя/получателя платежа*/ ,fcos.sContractBankCountry_BCode ,fcos.sContractBankCountry_Code ,fcos.sContractBankCountry_Name ,fcos.sContractBankCountry_NumberCode ,fcos.[bAllowContractSum] /*логическое значение, определяет учитывать/не учитывать операцию по ПД в сумме контракта*/ ,fcos.idPaymentOrder ,fcos.idSimpleRequest ,fcos.iRowNumber ,fcos.idContractState ,fcos.idSourceObject ,fcos.bAutorizedWithoutContract ,null as sBank_Name ,null as sBank_BIK ,null as sBank_SWIFT_BIC ,null as sClient_Name ,null as sClient_INN ,fcos.sBankCountry_NumberCode ,(case when isnull(fcos.sBankCountry_NumberCode, '643') = '643' then 0 else 1 end) as bBankCountry_NumberCode ,@idCOCertificateState as idCOCertificateState ,fcos.dReissueDocDate ,fcos.dReturnDate ,fcos.idDocType ,fcos.idAccountCurrency ,NULL ,NULL--@bRejectNonPaymentDocument ,NULL from @tbCurrencyOperationAll fcos where (fcos.idSourceObject is null) -- Письма insert into @tbCurrencyOperation select fcos.[idCurrencyOperationState] /*ID*/ ,fcos.[idCurrencyOperation] /*ID*/ ,fcos.[sDocNumber] /*Номер расчетного документа*/ ,fcos.[dDocDate] /*Дата расчетного документа*/ ,fcos.[sSurrogat] ,fcos.[bOperPrizPc95] ,fcos.[dDateOperation] /*Дата операции*/ ,fcos.[idPaymentType] /*ID признака платежа*/ ,fcos.sPaymentType_BCode ,fcos.sPaymentType_Code ,fcos.sPaymentType_Name ,fcos.[idKVVO] /*ID кода вида ВО*/ ,fcos.sKVVO_BCode ,fcos.sKVVO_Code ,fcos.sKVVO_Name ,fcos.[mOperationSum] /*Сумма операции*/ ,fcos.[fRate] /*Курс пересчета*/ ,fcos.[idCurrency] /*ID валюты операции*/ ,fcos.sCurrency_BCode ,fcos.sCurrency_Code ,fcos.sCurrency_Name ,fcos.sCurrency_OkvNum ,fcos.[dEditDate] ,fcos.[sPassportNumber] /*Номер ПС*/ ,fcos.[sContractNumber] /*Номер контракта*/ ,fcos.[dContractDate] /*Дата контракта*/ ,fcos.[mContractSum] /*Сумма операции в валюте ВК/КД*/ ,fcos.[idContractCurrency] /*ID валюты контракта*/ ,fcos.sContractCurrency_BCode ,fcos.sContractCurrency_Code ,fcos.sContractCurrency_Name ,fcos.sContractCurrency_OkvNum /*Числовой код валюты (810, 840, 978, и т.д.)*/ ,fcos.[dExpectedDate] /*Ожидаемый срок*/ ,fcos.[idContractBankCountry] /*ID кода страны банка-нерезидента отправителя/получателя платежа*/ ,fcos.sContractBankCountry_BCode ,fcos.sContractBankCountry_Code ,fcos.sContractBankCountry_Name ,fcos.sContractBankCountry_NumberCode ,fcos.[bAllowContractSum] /*логическое значение, определяет учитывать/не учитывать операцию по ПД в сумме контракта*/ ,fcos.idPaymentOrder ,fcos.idSimpleRequest ,fcos.iRowNumber ,fcos.idContractState ,fcos.idSourceObject ,fcos.bAutorizedWithoutContract ,( case when sr.sSimpleRequestDocType_BCode = 'SimpleRequestDocType_MultiCurrencyTransaction' then sr.sBank_Name else null end ) as sBank_Name ,null as sBank_BIK ,( case when sr.sSimpleRequestDocType_BCode = 'SimpleRequestDocType_MultiCurrencyTransaction' then sr.sBank_SWIFT_BIC else null end ) as sBank_SWIFT_BIC ,( case when sr.sSimpleRequestDocType_BCode = 'SimpleRequestDocType_MultiCurrencyTransaction' then sr.sClient_Name else null end ) as sClient_Name ,null as sClient_INN ,fcos.sBankCountry_NumberCode ,(case when isnull(fcos.sBankCountry_NumberCode, '643') = '643' then 0 else 1 end) as bBankCountry_NumberCode ,@idCOCertificateState as idCOCertificateState ,fcos.dReissueDocDate ,fcos.dReturnDate ,fcos.idDocType ,fcos.idAccountCurrency ,NULL ,iif(srs.sBCode = 'SimpleRequest_Rejected', 1, 0) ,ISNULL(srs.bClosed, 0) from @tbCurrencyOperationAll fcos join dbo.[vftSimpleRequest_short_listALL] sr on fcos.idSourceObject = sr.idSimpleRequest join dbo.[vdrStatus] srs on srs.idStatus = sr.idStatus where (fcos.idSourceObject is not null)-- and srs.bPositiveClosed=1 -- ПД declare @idCurrencyOperationState int declare @sPaymentOrderPin varchar(16) declare cur cursor for select fcos.[idCurrencyOperationState], rlabs.sPin from @tbCurrencyOperationAll fcos join @tbPaymentOrderIds x on x.idObject = fcos.idSourceObject join [dbo].[vftPaymentOrderPinCP] rlabs on rlabs.[idPaymentOrder] = x.idPaymentOrder where (fcos.idSourceObject is not null and ISNULL(x.bIsArchive, 0) = 0) open cur; fetch next from cur into @idCurrencyOperationState, @sPaymentOrderPin while @@FETCH_STATUS = 0 begin declare @sCPStatus varchar(100) declare @bFinitePositive bit declare @bFiniteNegative bit declare @bPaymentDocTypeOut bit declare @bPaymentDocTypeIn bit declare @sPayerClientPinEq varchar(50) declare @sPayeeClientPinEq varchar(50) declare @sPayerBankPinEq varchar(50) declare @sPayeeBankPinEq varchar(50) exec [dbo].[PaymentOrder_WSInvoke] @sPaymentOrderPin = @sPaymentOrderPin, @sCPStatus = @sCPStatus output, @bPaymentDocTypeOut = @bPaymentDocTypeOut output, @bPaymentDocTypeIn = @bPaymentDocTypeIn output, @sPayerClientPinEq = @sPayerClientPinEq output, @sPayeeClientPinEq = @sPayeeClientPinEq output, @sPayerBankPinEq = @sPayerBankPinEq output, @sPayeeBankPinEq = @sPayeeBankPinEq output --if @bFinitePositive=1 --begin select @bFinitePositive = isnull(cps.bFinitePositive, 0), @bFiniteNegative = ISNULL(cps.bFiniteNegative, 0) FROM dbo.vdrCPStatus cps where cps.sCode = @sCPStatus declare @sPayee_Name nvarchar(255) declare @sPayee_INN varchar(12) select @sPayee_Name = sName, @sPayee_INN = sINN from dbo.vftClient_listALL where sPinEq = @sPayeeClientPinEq declare @sPayer_Name nvarchar(255) declare @sPayer_INN varchar(12) select @sPayer_Name = sName, @sPayer_INN = sINN from dbo.vftClient_listALL where sPinEq = @sPayerClientPinEq declare @sPayeeBank_Name nvarchar(255) declare @sPayeeBank_BIK nvarchar(9) declare @sPayeeBank_SWIFT_BIC nvarchar(11) select @sPayeeBank_Name = cl.sName, @sPayeeBank_BIK = b.sBIK, @sPayeeBank_SWIFT_BIC = b.sSWIFT_BIC from dbo.vftClient_listALL cl join dbo.vftBank_listALL b on b.idBank = cl.idClient left join dbo.vdrCountry c on c.idCountry = b.idCountryReg where sPinEq = @sPayeeBankPinEq declare @sPayerBank_Name nvarchar(255) declare @sPayerBank_BIK nvarchar(9) declare @sPayerBank_SWIFT_BIC nvarchar(11) select @sPayerBank_Name = cl.sName, @sPayerBank_BIK = b.sBIK, @sPayerBank_SWIFT_BIC = b.sSWIFT_BIC from dbo.vftClient_listALL cl join dbo.vftBank_listALL b on b.idBank = cl.idClient left join dbo.vdrCountry c on c.idCountry = b.idCountryReg where sPinEq = @sPayerBankPinEq insert into @tbCurrencyOperation select fcos.[idCurrencyOperationState] /*ID*/ ,fcos.[idCurrencyOperation] /*ID*/ ,fcos.[sDocNumber] /*Номер расчетного документа*/ ,fcos.[dDocDate] /*Дата расчетного документа*/ ,fcos.[sSurrogat] ,fcos.[bOperPrizPc95] ,fcos.[dDateOperation] /*Дата операции*/ ,fcos.[idPaymentType] /*ID признака платежа*/ ,fcos.sPaymentType_BCode ,fcos.sPaymentType_Code ,fcos.sPaymentType_Name ,fcos.[idKVVO] /*ID кода вида ВО*/ ,fcos.sKVVO_BCode ,fcos.sKVVO_Code ,fcos.sKVVO_Name ,fcos.[mOperationSum] /*Сумма операции*/ ,fcos.[fRate] /*Курс пересчета*/ ,fcos.[idCurrency] /*ID валюты операции*/ ,fcos.sCurrency_BCode ,fcos.sCurrency_Code ,fcos.sCurrency_Name ,fcos.sCurrency_OkvNum ,fcos.[dEditDate] ,fcos.[sPassportNumber] /*Номер ПС*/ ,fcos.[sContractNumber] /*Номер контракта*/ ,fcos.[dContractDate] /*Дата контракта*/ ,fcos.[mContractSum] /*Сумма операции в валюте ВК/КД*/ ,fcos.[idContractCurrency] /*ID валюты контракта*/ ,fcos.sContractCurrency_BCode ,fcos.sContractCurrency_Code ,fcos.sContractCurrency_Name ,fcos.sContractCurrency_OkvNum /*Числовой код валюты (810, 840, 978, и т.д.)*/ ,fcos.[dExpectedDate] /*Ожидаемый срок*/ ,fcos.[idContractBankCountry] /*ID кода страны банка-нерезидента отправителя/получателя платежа*/ ,fcos.sContractBankCountry_BCode ,fcos.sContractBankCountry_Code ,fcos.sContractBankCountry_Name ,fcos.sContractBankCountry_NumberCode ,fcos.[bAllowContractSum] /*логическое значение, определяет учитывать/не учитывать операцию по ПД в сумме контракта*/ ,fcos.idPaymentOrder ,fcos.idSimpleRequest ,fcos.iRowNumber ,fcos.idContractState ,fcos.idSourceObject ,fcos.bAutorizedWithoutContract ,( case when @bPaymentDocTypeOut = 1 then ( case when @sPayeeClientPinEq = cl.sPinEq then @sPayeeBank_Name -- списание else @sPayerBank_Name -- зачисление end ) when @bPaymentDocTypeIn = 1 then @sPayerBank_Name -- зачисление else null end ) as sBank_Name ,( case when @bPaymentDocTypeOut = 1 then ( case when @sPayeeClientPinEq = cl.sPinEq then @sPayeeBank_BIK -- списание else @sPayerBank_BIK -- зачисление end ) when @bPaymentDocTypeIn = 1 then @sPayerBank_BIK -- зачисление else null end ) as sBank_BIK ,( case when @bPaymentDocTypeOut = 1 then ( case when @sPayeeClientPinEq = cl.sPinEq then @sPayeeBank_SWIFT_BIC -- списание else @sPayerBank_SWIFT_BIC -- зачисление end ) when @bPaymentDocTypeIn = 1 then @sPayerBank_SWIFT_BIC -- зачисление else null end ) as sBank_SWIFT_BIC ,( case when @bPaymentDocTypeOut = 1 then ( case when @sPayeeClientPinEq = cl.sPinEq then @sPayee_Name -- списание else @sPayer_Name -- зачисление end ) when @bPaymentDocTypeIn = 1 then @sPayer_Name -- зачисление else null end ) as sClient_Name ,( case when @bPaymentDocTypeOut = 1 then ( case when @sPayeeClientPinEq = cl.sPinEq then @sPayee_INN -- списание else @sPayer_INN -- зачисление end ) when @bPaymentDocTypeIn = 1 then @sPayer_INN -- зачисление else null end ) as sClient_INN ,fcos.sBankCountry_NumberCode ,(case when isnull(fcos.sBankCountry_NumberCode, '643') = '643' then 0 else 1 end) as bBankCountry_NumberCode ,@idCOCertificateState as idCOCertificateState ,fcos.dReissueDocDate ,fcos.dReturnDate ,fcos.idDocType ,fcos.idAccountCurrency ,@bFiniteNegative ,NULL --@bRejectNonPaymentDocument ,NULL from @tbCurrencyOperationAll fcos join @tbPaymentOrderIds x on x.idObject = fcos.idSourceObject JOIN dbo.ftCOCertificateState t1 on t1.idCOCertificateState = fcos.idCOCertificateState JOIN dbo.ftCOCertificate t2 ON t2.idCOCertificate = t1.idCOCertificate LEFT JOIN dbo.ftClient t3 ON t3.idClient = t2.idClient LEFT JOIN dbo.vftSubject_listALL cl ON t3.idClient = cl.idSubject where fcos.[idCurrencyOperationState] = @idCurrencyOperationState --end fetch next from cur into @idCurrencyOperationState, @sPaymentOrderPin end; close cur; DEALLOCATE cur; -- ПД Архив insert into @tbCurrencyOperation select fcos.[idCurrencyOperationState] /*ID*/ ,fcos.[idCurrencyOperation] /*ID*/ ,fcos.[sDocNumber] /*Номер расчетного документа*/ ,fcos.[dDocDate] /*Дата расчетного документа*/ ,fcos.[sSurrogat] ,fcos.[bOperPrizPc95] ,fcos.[dDateOperation] /*Дата операции*/ ,fcos.[idPaymentType] /*ID признака платежа*/ ,fcos.sPaymentType_BCode ,fcos.sPaymentType_Code ,fcos.sPaymentType_Name ,fcos.[idKVVO] /*ID кода вида ВО*/ ,fcos.sKVVO_BCode ,fcos.sKVVO_Code ,fcos.sKVVO_Name ,fcos.[mOperationSum] /*Сумма операции*/ ,fcos.[fRate] /*Курс пересчета*/ ,fcos.[idCurrency] /*ID валюты операции*/ ,fcos.sCurrency_BCode ,fcos.sCurrency_Code ,fcos.sCurrency_Name ,fcos.sCurrency_OkvNum ,fcos.[dEditDate] ,fcos.[sPassportNumber] /*Номер ПС*/ ,fcos.[sContractNumber] /*Номер контракта*/ ,fcos.[dContractDate] /*Дата контракта*/ ,fcos.[mContractSum] /*Сумма операции в валюте ВК/КД*/ ,fcos.[idContractCurrency] /*ID валюты контракта*/ ,fcos.sContractCurrency_BCode ,fcos.sContractCurrency_Code ,fcos.sContractCurrency_Name ,fcos.sContractCurrency_OkvNum /*Числовой код валюты (810, 840, 978, и т.д.)*/ ,fcos.[dExpectedDate] /*Ожидаемый срок*/ ,fcos.[idContractBankCountry] /*ID кода страны банка-нерезидента отправителя/получателя платежа*/ ,fcos.sContractBankCountry_BCode ,fcos.sContractBankCountry_Code ,fcos.sContractBankCountry_Name ,fcos.sContractBankCountry_NumberCode ,fcos.[bAllowContractSum] /*логическое значение, определяет учитывать/не учитывать операцию по ПД в сумме контракта*/ ,fcos.idPaymentOrder ,fcos.idSimpleRequest ,fcos.iRowNumber ,fcos.idContractState ,fcos.idSourceObject ,fcos.bAutorizedWithoutContract ,null as sBank_Name ,null as sBank_BIK ,null as sBank_SWIFT_BIC ,null as sClient_Name ,null as sClient_INN ,fcos.sBankCountry_NumberCode ,(case when isnull(fcos.sBankCountry_NumberCode, '643') = '643' then 0 else 1 end) as bBankCountry_NumberCode ,@idCOCertificateState as idCOCertificateState ,fcos.dReissueDocDate ,fcos.dReturnDate ,fcos.idDocType ,fcos.idAccountCurrency ,NULL ,NULL --@bRejectNonPaymentDocument ,NULL from @tbCurrencyOperationAll fcos join @tbPaymentOrderIds x on x.idObject = fcos.idSourceObject LEFT JOIN @tbCurrencyOperation t1 ON t1.idSourceObject = fcos.idSourceObject AND (t1.iRowNumber = fcos.iRowNumber OR t1.sSurrogat = fcos.sSurrogat) where ISNULL(x.bIsArchive, 0) = 1 AND t1.idCurrencyOperationState IS NULL SELECT pac.sRegNumber as 'POLE1' -- 'REGNBANK' ,passp.sPassportNumber as 'POLE3' -- 'N_PC' ,cct.sName as 'POLE4' -- 'BIDK' ,pac.sRegNumber as 'POLE5' -- 'REGNBANK_B' ,pac.sRegNumber as 'POLE6' -- 'REGNBANK_K' ,co.bTechnical ,cs.sAccountNumber as 'POLE7' -- 'N_SHET' ,op.sPaymentType_Code ,cs.idBankCountry ,op.sSurrogat ,cs.bAcсountAnotherBank ,@dFirstDateOut ,op.[dDateOperation] as 'POLE8' ,op.sPaymentType_Name as 'POLE9' -- 'NAPRAV' -- «Признак платежа» из блока "Сведения об операциях" ,op.sKVVO_Code as 'POLE10' -- 'BID' -- Код вида ВО (из справочника drKVO) из блока "Сведения об операциях" ,op.sCurrency_OkvNum as 'POLE11' -- 'KVALP' ,op.[mOperationSum] as 'POLE12' -- 'SUMMAP' ,op.sContractCurrency_OkvNum , op.sCurrency_OkvNum as 'POLE13' -- 'KVALK' ,op.mContractSum ,op.[fRate] ,op.[mOperationSum] as 'POLE14' -- 'SUMMAK' -- vftPaymentOrder_listALL.mPaymentSum ,ISNULL((SELECT TOP 1 LTRIM(sOkvNum) FROM dbo.vdrCurrency WHERE idCurrency = op.idAccountCurrency), '') AS 'POLE15' -- 'KVALS' ,op.dExpectedDate ,op.dReturnDate ,op.dExpectedDate ,op.dReturnDate as 'POLE17' -- 'SROK_WAIT' -- Значение доп. свойства СВО - SVOProp_SrokWait ,cs.iCorrectionNumber AS 'POLE20' -- NPP_KORR ,op.sBankCountry_NumberCode as 'POLE21' -- 'KODINBANK' ('INBANK' ) ,(select top 1 sBCode from dbo.vdrVkDict_DocType where idVkDict_DocType = op.idDocType) as 'POLE22' -- 'PRIZ_PRDOC' ,op.sClient_Name as 'POLE23' -- 'P_NAIM' ,op.sClient_INN as 'POLE24' -- 'P_INN' ,op.sContractBankCountry_NumberCode as 'POLE26' -- 'KSTRANA' -- Код страны банка ,op.sBank_Name as 'POLE27' -- 'BANK_NAIM' ,op.sBank_BIK as 'POLE28' -- 'BANK_BIC' ,op.sBank_SWIFT_BIC as 'POLE29' -- 'BANK_SWIFT' ,@dFirstDateIn as 'POLE32' -- 'DATA_DOC' ,@dFirstDateOut as 'POLE33' -- 'DATA_PRIN' ,op.dReissueDocDate as 'POLE34' -- DATA_PRIZ ,@BPKindCode ,cs.dDateIn as 'POLE35' -- DATA_PRIZ2 ,@bMiddleLayer ,cs.dDateOut as 'POLE36' -- DATA_PRIZ3 ,op.sDocNumber as 'POLE37' -- 'N_DOCUM_S' ,op.dDocDate as 'POLE38' -- 'D_DOCUM_S' ,cs.sComment ,cmntProp.sValue as 'POLE39' -- 'PRIMESH' -- Поле «Комментарий» из блока "Информация уполномоченного банка" ,rln.[sDescription] as 'POLE40' -- 'PRIMESH_S' ,op.sSurrogat ,cs.idCOCertificate ,op.iRowNumber as 'POLE41' -- 'SURROGAT' ,bOperPrizPc95 as 'POLE42' -- 'PRIZ_FIXPL' from @tbCurrencyOperation op JOIN dbo.ftCOCertificateState cs ON op.idCOCertificateState = cs.idCOCertificateState JOIN dbo.ftCOCertificate co ON co.idCOCertificate = cs.idCOCertificate JOIN dbo.ftContractState c on c.idContractState = op.idContractState JOIN dbo.drType cct ON cct.idType = c.idContractCodeType AND cct.sCode IN ('ContractCodeType_One', 'ContractCodeType_Two', 'ContractCodeType_Three', 'ContractCodeType_Four', 'ContractCodeType_Nine') LEFT JOIN dbo.ftPassportState ps ON ps.idContractState = c.idContractState left join dbo.drType psType WITH(NOLOCK) on psType.idType = ps.idPassportStateType left join dbo.ftPassport passp on passp.idPassport = ps.idPassport left join dbo.ftPassportAppearAndClose pac on pac.idPassportState = ps.idPassportState and pac.bLast = 1 LEFT JOIN dbo.rlNote rln ON rln.idCurrencyOperation = op.idCurrencyOperation LEFT JOIN dbo.vftProperty_listALL cmntProp ON cs.idCOCertificateState = cmntProp.idObject AND cmntProp.sFieldType_Code = 'ProcParam_RequestActionComment' AND ISNULL(cmntProp.dOpen, 0) <= ISNULL(cs.dClose, '2100-01-01') AND ISNULL(cs.dClose, '2100-01-01') <= ISNULL(cmntProp.dClose, '2100-01-01')