· 5 years ago · Feb 05, 2020, 06:08 PM
1SET NOEXEC OFF
2GO
3
4-------------------------------------------------------------------------------------------------------------------------------------------------------
5-------------------------------------------------------------------------------------------------------------------------------------------------------
6--DROP PROCEDURI ref TypeElemNegocieri
7-------------------------------------------------------------------------------------------------------------------------------------------------------
8-------------------------------------------------------------------------------------------------------------------------------------------------------
9IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newSP_Negocieri_Respinge]') AND type in (N'P', N'PC'))
10 BEGIN
11 DROP PROCEDURE [dbo].[newSP_Negocieri_Respinge]
12 END;
13GO
14-------------------------------------------------------------------------------------------------------------------------------------------------------
15IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newSP_Negocieri_Aproba]') AND type in (N'P', N'PC'))
16 BEGIN
17 DROP PROCEDURE [dbo].[newSP_Negocieri_Aproba]
18 END;
19GO
20-------------------------------------------------------------------------------------------------------------------------------------------------------
21IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newSP_Negocieri_Save]') AND type in (N'P', N'PC'))
22 BEGIN
23 DROP PROCEDURE [dbo].[newSP_Negocieri_Save]
24 END;
25GO
26-------------------------------------------------------------------------------------------------------------------------------------------------------
27IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newSP_Negocieri_EditareDisc]') AND type in (N'P', N'PC'))
28 BEGIN
29 DROP PROCEDURE [dbo].[newSP_Negocieri_EditareDisc]
30 END;
31GO
32-------------------------------------------------------------------------------------------------------------------------------------------------------
33IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newSP_Negocieri_EditareProdus]') AND type in (N'P', N'PC'))
34 BEGIN
35 DROP PROCEDURE [dbo].[newSP_Negocieri_EditareProdus]
36 END;
37GO
38-------------------------------------------------------------------------------------------------------------------------------------------------------
39IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newSP_Negocieri_Anuleaza]') AND type in (N'P', N'PC'))
40 BEGIN
41 DROP PROCEDURE [dbo].[newSP_Negocieri_Anuleaza]
42 END;
43GO
44-------------------------------------------------------------------------------------------------------------------------------------------------------
45IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newSP_Negocieri_CereFeedBack]') AND type in (N'P', N'PC'))
46 BEGIN
47 DROP PROCEDURE [dbo].[newSP_Negocieri_CereFeedBack]
48 END;
49GO
50-------------------------------------------------------------------------------------------------------------------------------------------------------
51IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newSP_Negocieri_Prelungeste]') AND type in (N'P', N'PC'))
52 BEGIN
53 DROP PROCEDURE [dbo].[newSP_Negocieri_Prelungeste]
54 END;
55GO
56-------------------------------------------------------------------------------------------------------------------------------------------------------
57IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newSP_Negocieri_RaspunsFeedBack]') AND type in (N'P', N'PC'))
58 BEGIN
59 DROP PROCEDURE [dbo].[newSP_Negocieri_RaspunsFeedBack]
60 END;
61GO
62-------------------------------------------------------------------------------------------------------------------------------------------------------
63IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newSP_Negocieri_Validari]') AND type in (N'P', N'PC'))
64 BEGIN
65 DROP PROCEDURE [dbo].[newSP_Negocieri_Validari]
66 END;
67GO
68-------------------------------------------------------------------------------------------------------------------------------------------------------
69IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newSP_Negocieri_Workflow_Respinge]') AND type in (N'P', N'PC'))
70 BEGIN
71 DROP PROCEDURE [dbo].[newSP_Negocieri_Workflow_Respinge]
72 END;
73GO
74-------------------------------------------------------------------------------------------------------------------------------------------------------
75IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newSP_Negocieri_Workflow_SolicitaFeedback]') AND type in (N'P', N'PC'))
76 BEGIN
77 DROP PROCEDURE [dbo].[newSP_Negocieri_Workflow_SolicitaFeedback]
78 END;
79GO
80-------------------------------------------------------------------------------------------------------------------------------------------------------
81IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newSP_Negocieri_Workflow_Valideaza]') AND type in (N'P', N'PC'))
82 BEGIN
83 DROP PROCEDURE [dbo].[newSP_Negocieri_Workflow_Valideaza]
84 END;
85GO
86-------------------------------------------------------------------------------------------------------------------------------------------------------
87IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newSP_GetNegocieri_Detail]') AND type in (N'P', N'PC'))
88 BEGIN
89 DROP PROCEDURE [dbo].[newSP_GetNegocieri_Detail]
90 END;
91GO
92
93-------------------------------------------------------------------------------------------------------------------------------------------------------
94-------------------------------------------------------------------------------------------------------------------------------------------------------
95-------------------------------------------------------------------------------------------------------------------------------------------------------
96IF NOT EXISTS(SELECT 1 FROM sys.columns
97 WHERE Name = N'IsManualConfirmation'
98 AND Object_ID = Object_ID(N'dbo.Negocieri'))
99ALTER TABLE Negocieri
100ADD IsManualConfirmation bit NULL
101
102-------------------------------------------------------------------------------------------------------------------------------------------------------
103IF type_id('[dbo].[TypeNegocieri]') IS NOT NULL
104 DROP TYPE [dbo].[TypeNegocieri];
105GO
106
107CREATE TYPE [dbo].[TypeNegocieri] AS TABLE(
108 [IdNegocieriPK] [int] NOT NULL,
109 [IdClient] [int] NOT NULL,
110 [IdPLClient] [int] NOT NULL,
111 [Numar] [int] NULL,
112 [DataInceput] [datetime] NOT NULL,
113 [DataSfarsit] [datetime] NOT NULL,
114 [DataCreare] [datetime] NOT NULL,
115 [IdOperatorCreare] [int] NOT NULL,
116 [MomentOperare] [datetime] NOT NULL,
117 [IdOperator] [int] NOT NULL,
118 [Status] [int] NOT NULL,
119 [OperatorAprobareRV] [int] NULL,
120 [DataAprobareRV] [datetime] NULL,
121 [OperatorAprobareASM] [int] NULL,
122 [DataAprobareASM] [datetime] NULL,
123 [OperatorRespinsASM] [int] NULL,
124 [DataRespinsASM] [datetime] NULL,
125 [OperatorAprobareMKT] [int] NULL,
126 [DataAprobareMKT] [datetime] NULL,
127 [OperatorRespinsMKT] [int] NULL,
128 [DataRespinsMKT] [datetime] NULL,
129 [OperatorAprobareRSM] [int] NULL,
130 [DataAprobareRSM] [datetime] NULL,
131 [OperatorRespinsRSM] [int] NULL,
132 [DataRespinsRSM] [datetime] NULL,
133 [OperatorAprobareNSM] [int] NULL,
134 [DataAprobareNSM] [datetime] NULL,
135 [OperatorRespinsNSM] [int] NULL,
136 [DataRespinsNSM] [datetime] NULL,
137 [Valoare] [money] NOT NULL,
138 [NrCrt] [int] NOT NULL,
139 [ProcDisc] [dbo].[Procent] NOT NULL,
140 [BazaImpozitare] [money] NOT NULL,
141 [NotApplyAutomaticallyDisc] [smallint] NULL,
142 [ToatePL] [smallint] NULL,
143 [FacturareAutomata] [smallint] NULL,
144 [DiscountApplicationType] [int] NULL,
145 [IdTipNegociere] [int] NULL,
146 [ModificaDisc] [bit] NULL,
147 [MotivRefuz] [nvarchar](300) NULL,
148 [EditareDisc] [bit] NULL,
149 [Realizat] [bit] NULL,
150 [Observatie] [varchar](450) NULL,
151 [IsTermenPlataDiferentiat] [smallint] NULL,
152 [ObservatiiMKT] [varchar](450) NULL,
153 [MotivMarjaInsuficienta] [int] NULL,
154 [IdProducator] [int] NULL,
155 [FeedBack] [smallint] NULL,
156 [IdMasterNegocieri] [int] NULL,
157 [IdParinteNegocieri] [int] NULL,
158 [Versiune] [int] NULL,
159 [UltimaVersiune] [bit] NULL,
160 [IsManualConfirmation] [bit] NULL,
161 PRIMARY KEY CLUSTERED
162(
163 [IdNegocieriPK] ASC
164)WITH (IGNORE_DUP_KEY = OFF)
165)
166GO
167
168
169-------------------------------------------------------------------------------------------------------------------------------------------------------
170-------------------------------------------------------------------------------------------------------------------------------------------------------
171-------------------------------------------------------------------------------------------------------------------------------------------------------
172
173
174CREATE procedure [dbo].[newSP_Negocieri_Respinge]
175 @Negocieri TypeNegocieri readonly,
176 @ElemNegocieri TypeElemNegocieri readonly,
177 @NegocieriXDivizie TypeNegocieriXDivizie readonly,
178 @MarjaNegocieri TypeMarjaNegocieri readonly,
179 @TermenPlataDiferentiatNegocieri TypeTermenPlataDiferentiatNegocieri readonly,
180 @ActiuniNegocieri TypeActiuniNegocieri readonly,
181 @ElemNegocieriXBasicTargetDetail TypeElemNegocieriXBasicTargetDetail readonly,
182 @ManualConfirmationDetailXElemNegocieri TypeManualConfirmationDetailXElemNegocieri readonly,
183 @OperatorOid uniqueidentifier,
184 @Obs varchar(450),
185 @NegocieriFisier TypeNegocieriFisier readonly
186as
187begin try
188 declare @IsValid bit,
189 @CheckAdditionalDiscountPercentage bit = 0
190
191 exec dbo.newSP_Negocieri_Validari @Negocieri, @ElemNegocieri, @MarjaNegocieri, @TermenPlataDiferentiatNegocieri, @ActiuniNegocieri, @IsValid output, @CheckAdditionalDiscountPercentage
192
193 if @IsValid = 0
194 return
195
196 declare @oldStatus int,
197 @AprobareASM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare ASM'),
198 @RespinsASM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Respins ASM'),
199 @AprobareMKT int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare MKT'),
200 @RespinsMKT int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Respins MKT'),
201 @AprobareRSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare RSM'),
202 @RespinsRSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Respins RSM'),
203 @AprobareNSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare NSM'),
204 @RespinsNSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Respins NSM'),
205 @AprobataIntegral int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobata Integral'),
206 @GeneratRV_RVT int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Generat RV/RVT'),
207 @IdOperator int,
208 @TipRespins int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'TipActiuneNegocieri' and t0.Nume = 'Respingere'),
209 @IdNegocieri int,
210 @Status int,
211 @Email varchar(450),
212 @Numar int,
213 @Subject varchar(250),
214 @Body varchar(max),
215 @ToRecipients varchar(250),
216 @NumeClient varchar(200),
217 @profile_name varchar(60) = 'Negocieri',
218 @body_format varchar(60) = 'TEXT',
219 @IdNegocieriPk int
220
221 if object_id('tempdb..#Negocieri') is not null
222 drop table #Negocieri
223
224 create table #Negocieri (IdNegocieriPK int not null, IdClient int not null, IdPLClient int not null,
225 Numar int not null, DataInceput datetime not null, DataSfarsit datetime not null,
226 DataCreare datetime not null, IdOperatorCreare int not null, MomentOperare datetime not null,
227 IdOperator int not null, [Status] int not null, OperatorAprobareRV int null,
228 DataAprobareRV datetime null, OperatorAprobareASM int null, DataAprobareASM datetime null,
229 OperatorRespinsASM int null, DataRespinsASM datetime null, OperatorAprobareMKT int null,
230 DataAprobareMKT datetime null, OperatorRespinsMKT int null, DataRespinsMKT datetime null,
231 OperatorAprobareRSM int null, DataAprobareRSM datetime null, OperatorRespinsRSM int null,
232 DataRespinsRSM datetime null, OperatorAprobareNSM int null, DataAprobareNSM datetime null,
233 OperatorRespinsNSM int null, DataRespinsNSM datetime null, Valoare money not null,
234 NrCrt int not null, ProcDisc money not null, BazaImpozitare money not null,
235 NotApplyAutomaticallyDisc smallint null, ToatePL smallint null, FacturareAutomata smallint null, DiscountApplicationType int null, IdTipNegociere int null,
236 ModificaDisc bit null, MotivRefuz nvarchar(300) null, EditareDisc bit null,
237 Realizat bit null, Observatie varchar(450) null, IsTermenPlataDiferentiat smallint null,
238 ObservatiiMKT varchar(450) null, MotivMarjaInsuficienta int null, IdProducator int null,
239 FeedBack smallint null, IdMasterNegocieri int null, IdParinteNegocieri int null,
240 Versiune int null, IsManualConfirmation bit null
241 constraint PK_Negocieri_Temp primary key clustered (IdNegocieriPK asc))
242
243 /*if object_id('tempdb..#ElemNegocieri') is not null
244 drop table #ElemNegocieri
245
246 create table #ElemNegocieri (IdElemNegocieriPK int not null, IdNegocieriPK int not null, IdProdus int not null,
247 CantUV numeric(15, 6) not null, CantUVRamas numeric(15, 6) not null, Pret money not null,
248 ProcDiscTotal money not null, ProcDiscTotalRec money not null, ProcDisc money not null,
249 ProcTVA money not null, Valoare money not null, PretRidicata money null,
250 PretFaraDisc money null, TermenPlata int null, ProcMarja numeric(7, 4) null,
251 IdMarja int null, IsRabat smallint null, CantBax int null,
252 IdDivizie int null, ProcMarjaCalculat numeric(20, 4) null, IdElemOfertaSpeciala int null,
253 ProcDiscOfferDetail money null, ProcDiscOfferHeader money null, IsRabatHeaderOffer smallint null,
254 IsRabatDetailOffer smallint null, ProcDiscRecOfferDetail money null, ProcDiscRecOfferHeader money null,
255 ProcMarjaMinim numeric(18, 0) null, ProcDiscAutomat numeric(7, 4) null, ProcMarjaReal numeric(18, 0) null,
256 FinalSettlementTypeId int null, FinalPaymentTerm int null, DiscountRatePT numeric(6, 3) null,
257 GenerateFinDocMasterPT smallint null, SuportatProducatorOfferDetail money null, SuportatMDPOfferDetail money null,
258 SuportatASMOfferDetail money null, SuportatProducatorOfferHeader money null, SuportatMDPOfferHeader money null,
259 SuportatASMOfferHeader money null, ProcDiscSuplimentar money null, DiscSustinutOSProducator money null,
260 DiscSustinutOSMkt money null, DiscSustinutOSAsm money null, ValDiscSuplimentarProducator money null,
261 DiscSuplimentarProducator money null, ValDiscSustinutCosMkt money null, DiscSustinutCosMkt money null,
262 ValDiscSustinutCosAsm money null, DiscSustinutCosAsm money null, ValDiscSustinutCos money null,
263 DiscSustinutCos money null, ValDiscSustinutReversare money null, DiscSustinutReversare money null,
264 ValDiscSustinutNonRx money null, DiscSustinutNonRx money null, ValDisc money null,
265 ValoareNeta money null, MargineNeta money null, MarjaNeta money null,
266 NrCrt int not null, IdOfertaSpecialaTemp int null, Inlocuit smallint null,
267 DataInlocuit datetime null, ElementInlocuit int null, CantInlocuita int null,
268 MargineReala money null, MargineRealaElInlocuit money null, ElemNegocieriIstoric int null,
269 PretNet money null, SuportatCosMKTOfferDetail money null, SuportatCosMKTOfferHeader money null,
270 DiscSustinutOSCosMKT money null, ValDiscSuplimentarProducatorRec money null, DiscSuplimentarProducatorRec money null,
271 RecuperareRN smallint null, ExistaModificari smallint null, DiscOferteRebate money null,
272 ValDiscOferteRebate money null, DiscSuplimentarSustinutMDP money null, ValDiscSuplimentarSustinutMDP money null,
273 DiscSustinutASPRI money null, ValDiscSustinutASPRI money null,
274 constraint PK_ElemNegocieri_Temp primary key clustered (IdElemNegocieriPK asc))*/
275
276 insert into #Negocieri (IdNegocieriPK, IdClient, IdPLClient, Numar, DataInceput, DataSfarsit, DataCreare, IdOperatorCreare,
277 MomentOperare, IdOperator, [Status], OperatorAprobareRV, DataAprobareRV, OperatorAprobareASM,
278 DataAprobareASM, OperatorRespinsASM, DataRespinsASM, OperatorAprobareMKT, DataAprobareMKT,
279 OperatorRespinsMKT, DataRespinsMKT, OperatorAprobareRSM, DataAprobareRSM, OperatorRespinsRSM,
280 DataRespinsRSM, OperatorAprobareNSM, DataAprobareNSM, OperatorRespinsNSM, DataRespinsNSM,
281 Valoare, NrCrt, ProcDisc, BazaImpozitare, NotApplyAutomaticallyDisc, ToatePL, FacturareAutomata,DiscountApplicationType, IdTipNegociere,
282 ModificaDisc, MotivRefuz, EditareDisc, Realizat, Observatie, IsTermenPlataDiferentiat, ObservatiiMKT,
283 MotivMarjaInsuficienta, IdProducator, FeedBack, IdMasterNegocieri, IdParinteNegocieri, Versiune, IsManualConfirmation)
284 select t0.IdNegocieriPK, t0.IdClient, t0.IdPLClient, t0.Numar, t0.DataInceput, t0.DataSfarsit, t0.DataCreare, t0.IdOperatorCreare,
285 t0.MomentOperare, t0.IdOperator, t0.[Status], t0.OperatorAprobareRV, t0.DataAprobareRV, t0.OperatorAprobareASM,
286 t0.DataAprobareASM, t0.OperatorRespinsASM, t0.DataRespinsASM, t0.OperatorAprobareMKT, t0.DataAprobareMKT,
287 t0.OperatorRespinsMKT, t0.DataRespinsMKT, t0.OperatorAprobareRSM, t0.DataAprobareRSM, t0.OperatorRespinsRSM,
288 t0.DataRespinsRSM, t0.OperatorAprobareNSM, t0.DataAprobareNSM, t0.OperatorRespinsNSM, t0.DataRespinsNSM,
289 t0.Valoare, t0.NrCrt, t0.ProcDisc, t0.BazaImpozitare, t0.NotApplyAutomaticallyDisc, t0.ToatePL, t0.FacturareAutomata, t0.DiscountApplicationType, t0.IdTipNegociere,
290 t0.ModificaDisc, t0.MotivRefuz, t0.EditareDisc, t0.Realizat, t0.Observatie, t0.IsTermenPlataDiferentiat, t0.ObservatiiMKT,
291 t0.MotivMarjaInsuficienta, t0.IdProducator, t0.FeedBack, t0.IdMasterNegocieri, t0.IdParinteNegocieri, t0.Versiune, t0.IsManualConfirmation
292 from @Negocieri t0
293
294 /*insert into #ElemNegocieri (IdElemNegocieriPK, IdNegocieriPK, IdProdus, CantUV, CantUVRamas, Pret, ProcDiscTotal, ProcDiscTotalRec,
295 ProcDisc, ProcTVA, Valoare, PretRidicata, PretFaraDisc, TermenPlata, ProcMarja, IdMarja, IsRabat, CantBax,
296 IdDivizie, ProcMarjaCalculat, IdElemOfertaSpeciala, ProcDiscOfferDetail, ProcDiscOfferHeader, IsRabatHeaderOffer,
297 IsRabatDetailOffer, ProcDiscRecOfferDetail, ProcDiscRecOfferHeader, ProcMarjaMinim, ProcDiscAutomat, ProcMarjaReal,
298 FinalSettlementTypeId, FinalPaymentTerm, DiscountRatePT, GenerateFinDocMasterPT, SuportatProducatorOfferDetail,
299 SuportatMDPOfferDetail, SuportatASMOfferDetail, SuportatProducatorOfferHeader, SuportatMDPOfferHeader,
300 SuportatASMOfferHeader, ProcDiscSuplimentar, DiscSustinutOSProducator, DiscSustinutOSMkt, DiscSustinutOSAsm,
301 ValDiscSuplimentarProducator, DiscSuplimentarProducator, ValDiscSustinutCosMkt, DiscSustinutCosMkt,
302 ValDiscSustinutCosAsm, DiscSustinutCosAsm, ValDiscSustinutCos, DiscSustinutCos, ValDiscSustinutReversare,
303 DiscSustinutReversare, ValDiscSustinutNonRx, DiscSustinutNonRx, ValDisc, ValoareNeta, MargineNeta, MarjaNeta,
304 NrCrt, IdOfertaSpecialaTemp, Inlocuit, DataInlocuit, ElementInlocuit, CantInlocuita, MargineReala, MargineRealaElInlocuit,
305 ElemNegocieriIstoric, PretNet, SuportatCosMKTOfferDetail, SuportatCosMKTOfferHeader, DiscSustinutOSCosMKT, ValDiscSuplimentarProducatorRec,
306 DiscSuplimentarProducatorRec, RecuperareRN, ExistaModificari, DiscOferteRebate, ValDiscOferteRebate, DiscSuplimentarSustinutMDP,
307 ValDiscSuplimentarSustinutMDP, DiscSustinutASPRI, ValDiscSustinutASPRI)
308 select t0.IdElemNegocieriPK, t0.IdNegocieriPK, t0.IdProdus, t0.CantUV, t0.CantUVRamas, t0.Pret, t0.ProcDiscTotal, t0.ProcDiscTotalRec,
309 t0.ProcDisc, t0.ProcTVA, t0.Valoare, t0.PretRidicata, t0.PretFaraDisc, t0.TermenPlata, t0.ProcMarja, t0.IdMarja, t0.IsRabat, t0.CantBax,
310 t0.IdDivizie, t0.ProcMarjaCalculat, t0.IdElemOfertaSpeciala, t0.ProcDiscOfferDetail, t0.ProcDiscOfferHeader, t0.IsRabatHeaderOffer,
311 t0.IsRabatDetailOffer, t0.ProcDiscRecOfferDetail, t0.ProcDiscRecOfferHeader, t0.ProcMarjaMinim, t0.ProcDiscAutomat, t0.ProcMarjaReal,
312 t0.FinalSettlementTypeId, t0.FinalPaymentTerm, t0.DiscountRatePT, t0.GenerateFinDocMasterPT, t0.SuportatProducatorOfferDetail,
313 t0.SuportatMDPOfferDetail, t0.SuportatASMOfferDetail, t0.SuportatProducatorOfferHeader, t0.SuportatMDPOfferHeader,
314 t0.SuportatASMOfferHeader, t0.ProcDiscSuplimentar, t0.DiscSustinutOSProducator, t0.DiscSustinutOSMkt, t0.DiscSustinutOSAsm,
315 t0.ValDiscSuplimentarProducator, t0.DiscSuplimentarProducator, t0.ValDiscSustinutCosMkt, t0.DiscSustinutCosMkt,
316 t0.ValDiscSustinutCosAsm, t0.DiscSustinutCosAsm, t0.ValDiscSustinutCos, t0.DiscSustinutCos, t0.ValDiscSustinutReversare,
317 t0.DiscSustinutReversare, t0.ValDiscSustinutNonRx, t0.DiscSustinutNonRx, t0.ValDisc, t0.ValoareNeta, t0.MargineNeta, t0.MarjaNeta,
318 t0.NrCrt, t0.IdOfertaSpecialaTemp, t0.Inlocuit, t0.DataInlocuit, t0.ElementInlocuit, t0.CantInlocuita, t0.MargineReala, t0.MargineRealaElInlocuit,
319 t0.ElemNegocieriIstoric, t0.PretNet, t0.SuportatCosMKTOfferDetail, t0.SuportatCosMKTOfferHeader, t0.DiscSustinutOSCosMKT, t0.ValDiscSuplimentarProducatorRec,
320 t0.DiscSuplimentarProducatorRec, t0.RecuperareRN, t0.ExistaModificari, t0.DiscOferteRebate, t0.ValDiscOferteRebate, t0.DiscSuplimentarSustinutMDP,
321 t0.ValDiscSuplimentarSustinutMDP, t0.DiscSustinutASPRI, t0.ValDiscSustinutASPRI
322 from @ElemNegocieri t0*/
323
324 select @oldStatus = t0.[Status], @IdNegocieri = t0.IdNegocieriPk from #Negocieri t0
325 select @IdOperator = t0.IdUtilizator from dbo.CustomUser (nolock) t0 where t0.Oid = @OperatorOid
326
327 select @Email = t0.Email, @Numar = t1.Numar, @NumeClient = t2.Nume
328 from dbo.CustomUser (nolock) t0 join #Negocieri t1 on t1.OperatorAprobareRV = t0.IdUtilizator
329 join dbo.Firma (nolock) t2 on t1.IdClient = t2.IdFirma
330 where t0.IsActive = 1
331
332 if (@oldStatus = @AprobareASM)
333 begin
334 update #Negocieri
335 set DataAprobareRV = null, OperatorAprobareRV = null, DataRespinsASM = getdate(),
336 [Status] = @RespinsASM, OperatorRespinsASM = @IdOperator
337 select @Status = @RespinsASM, @Subject = 'Negociere: ' + cast(@Numar as varchar) + ' Respinsa', @Body = 'Client: '+ @NumeClient, @ToRecipients = @Email
338 exec dbo.newSP_SendEmail @Subject, @Body, @ToRecipients, @profile_name, @body_format
339 end
340
341 if (@oldStatus = @AprobareMKT)
342 begin
343 update #Negocieri
344 set DataAprobareRV = null, OperatorAprobareRV = null, OperatorAprobareASM = null,
345 DataRespinsMKT = getdate(), [Status] = @RespinsMKT, OperatorRespinsMKT = @IdOperator
346 select @Status = @RespinsMKT, @Subject = 'Negociere: '+ cast(@Numar as varchar) + ' Respinsa', @Body = 'Client: ' + @NumeClient, @ToRecipients = @Email
347 exec dbo.newSP_SendEmail @Subject, @Body, @ToRecipients, @profile_name, @body_format
348 end
349
350 if (@oldStatus = @AprobareRSM)
351 begin
352 update #Negocieri
353 set DataAprobareRV = null, OperatorAprobareRV = null, OperatorAprobareASM = null,
354 DataRespinsMKT = getdate(), [Status] = @RespinsRSM, OperatorRespinsRSM = @IdOperator,
355 OperatorAprobareMKT = null, DataAprobareMKT = null
356 select @Status = @RespinsRSM, @Subject = 'Negociere: ' + cast(@Numar as varchar) + ' Respinsa', @Body = 'Client: ' + @NumeClient, @ToRecipients = @Email
357 exec dbo.newSP_SendEmail @Subject, @Body, @ToRecipients, @profile_name, @body_format
358 end
359
360 if (@oldStatus = @AprobareNSM)
361 begin
362 update #Negocieri
363 set DataAprobareRV = null, OperatorAprobareRV = null, OperatorAprobareASM = null,
364 DataRespinsMKT = getdate(), [Status] = @RespinsNSM, OperatorRespinsNSM = @IdOperator,
365 OperatorAprobareMKT = null, DataAprobareMKT = null, OperatorAprobareRSM = null, DataAprobareRSM = null
366 select @Status = @RespinsNSM, @Subject = 'Negociere: ' + cast(@Numar as varchar) + ' Respinsa', @Body = 'Client: ' + @NumeClient, @ToRecipients = @Email
367 exec dbo.newSP_SendEmail @Subject, @Body, @ToRecipients, @profile_name, @body_format
368 end
369
370 declare @SaveNegocieri TypeNegocieri,
371 @SaveActiuniNegocieri TypeActiuniNegocieri
372
373 insert into @SaveNegocieri (IdNegocieriPK, IdClient, IdPLClient, Numar, DataInceput, DataSfarsit, DataCreare, IdOperatorCreare,
374 MomentOperare, IdOperator, [Status], OperatorAprobareRV, DataAprobareRV, OperatorAprobareASM,
375 DataAprobareASM, OperatorRespinsASM, DataRespinsASM, OperatorAprobareMKT, DataAprobareMKT,
376 OperatorRespinsMKT, DataRespinsMKT, OperatorAprobareRSM, DataAprobareRSM, OperatorRespinsRSM,
377 DataRespinsRSM, OperatorAprobareNSM, DataAprobareNSM, OperatorRespinsNSM, DataRespinsNSM,
378 Valoare, NrCrt, ProcDisc, BazaImpozitare, NotApplyAutomaticallyDisc, ToatePL, FacturareAutomata, DiscountApplicationType, IdTipNegociere,
379 ModificaDisc, MotivRefuz, EditareDisc, Realizat, Observatie, IsTermenPlataDiferentiat, ObservatiiMKT,
380 MotivMarjaInsuficienta, IdProducator, FeedBack, IdMasterNegocieri, IdParinteNegocieri, Versiune, IsManualConfirmation)
381 select t0.IdNegocieriPK, t0.IdClient, t0.IdPLClient, t0.Numar, t0.DataInceput, t0.DataSfarsit, t0.DataCreare, t0.IdOperatorCreare,
382 t0.MomentOperare, t0.IdOperator, t0.[Status], t0.OperatorAprobareRV, t0.DataAprobareRV, t0.OperatorAprobareASM,
383 t0.DataAprobareASM, t0.OperatorRespinsASM, t0.DataRespinsASM, t0.OperatorAprobareMKT, t0.DataAprobareMKT,
384 t0.OperatorRespinsMKT, t0.DataRespinsMKT, t0.OperatorAprobareRSM, t0.DataAprobareRSM, t0.OperatorRespinsRSM,
385 t0.DataRespinsRSM, t0.OperatorAprobareNSM, t0.DataAprobareNSM, t0.OperatorRespinsNSM, t0.DataRespinsNSM,
386 t0.Valoare, t0.NrCrt, t0.ProcDisc, t0.BazaImpozitare, t0.NotApplyAutomaticallyDisc, t0.ToatePL, t0.FacturareAutomata, t0.DiscountApplicationType, t0.IdTipNegociere,
387 t0.ModificaDisc, t0.MotivRefuz, t0.EditareDisc, t0.Realizat, t0.Observatie, t0.IsTermenPlataDiferentiat, t0.ObservatiiMKT,
388 t0.MotivMarjaInsuficienta, t0.IdProducator, t0.FeedBack, t0.IdMasterNegocieri, t0.IdParinteNegocieri, t0.Versiune, t0.IsManualConfirmation
389 from #Negocieri t0
390
391 insert into @SaveActiuniNegocieri (IdActiuniNegocieri,IdNegocieri, IdPersoana, DataOperare, Tip, StatusInitial, StatusFinal, Obs)
392 select t0.IdActiuniNegocieri, t0.IdNegocieri, t0.IdPersoana, t0.DataOperare, t0.Tip, t0.StatusInitial, t0.StatusFinal, t0.Obs from @ActiuniNegocieri t0
393
394 insert into @SaveActiuniNegocieri (IdActiuniNegocieri,IdNegocieri, IdPersoana, DataOperare, Tip, StatusInitial, StatusFinal, Obs)
395 values (-1, @IdNegocieri, @IdOperator, getdate(), @TipRespins, @oldStatus, @Status, @Obs)
396
397 exec dbo.newSP_Negocieri_Save @SaveNegocieri, @ElemNegocieri, @NegocieriXDivizie, @MarjaNegocieri, @TermenPlataDiferentiatNegocieri,
398 @SaveActiuniNegocieri, @ElemNegocieriXBasicTargetDetail, @ManualConfirmationDetailXElemNegocieri, @NegocieriFisier, @OperatorOid,
399 @IdNegocieriPk output
400end try
401begin catch
402 declare @ErrorMessage nvarchar(4000),
403 @ErrorSeverity int,
404 @ErrorState int
405 select @ErrorMessage = error_message(), @ErrorSeverity = error_severity(), @ErrorState = error_state()
406 raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState)
407end catch
408
409
410GO
411
412
413
414
415-------------------------------------------------------------------------------------------------------------------------------------------------------
416-------------------------------------------------------------------------------------------------------------------------------------------------------
417-------------------------------------------------------------------------------------------------------------------------------------------------------
418
419
420CREATE procedure [dbo].[newSP_Negocieri_Aproba]
421 @Negocieri TypeNegocieri readonly,
422 @ElemNegocieri TypeElemNegocieri readonly,
423 @NegocieriXDivizie TypeNegocieriXDivizie readonly,
424 @MarjaNegocieri TypeMarjaNegocieri readonly,
425 @TermenPlataDiferentiatNegocieri TypeTermenPlataDiferentiatNegocieri readonly,
426 @ActiuniNegocieri TypeActiuniNegocieri readonly,
427 @ElemNegocieriXBasicTargetDetail TypeElemNegocieriXBasicTargetDetail readonly,
428 @ManualConfirmationDetailXElemNegocieri TypeManualConfirmationDetailXElemNegocieri readonly,
429 @OperatorOid uniqueidentifier,
430 @Obs varchar(450),
431 @NegocieriFisier TypeNegocieriFisier readonly,
432 @IdNegocieriPk int output
433as
434begin try
435 declare @IsValid bit,
436 @CheckAdditionalDiscountPercentage bit = 1,
437 @IsDebug bit = 1,
438 @CallId uniqueidentifier = newid()
439
440 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step, Info) values (@CallId, sysdatetime(), '010', '@OperatorOid - ' + convert(nvarchar(36), @OperatorOid))
441
442 exec dbo.newSP_Negocieri_Validari @Negocieri, @ElemNegocieri, @MarjaNegocieri, @TermenPlataDiferentiatNegocieri, @ActiuniNegocieri, @IsValid output, @CheckAdditionalDiscountPercentage
443
444 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step, Info) values (@CallId, sysdatetime(), '020', '@IsValid - ' + convert(nvarchar, @IsValid))
445
446 if @IsValid = 0
447 return
448
449 declare @PragMarja decimal = isnull((select cast(t0.Valoare as float) from dbo.InitInfo (nolock) t0 where t0.Nume = 'PragMotivMarjaRealaNegocieri'), 0),
450 --@StatusNegocieri int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 where t0.Nume = 'StatusNegocieri'),
451 @AprobareASM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare ASM'),
452 @RespinsASM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Respins ASM'),
453 @AprobareMKT int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare MKT'),
454 @RespinsMKT int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Respins MKT'),
455 @AprobareRSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare RSM'),
456 @RespinsRSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Respins RSM'),
457 @AprobareNSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare NSM'),
458 @RespinsNSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Respins NSM'),
459 @AprobataIntegral int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobata Integral'),
460 @GeneratRV_RVT int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Generat RV/RVT'),
461 @NegociereAnulata int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Negociere Anulata'),
462 @IdMotivMarjaInsuficienta int,
463 @ValDiscount decimal,
464 @CosMKT decimal,
465 @Reversare decimal,
466 @ValSupSustinutProducator decimal,
467 @ValCosASM decimal,
468 @ValCos decimal,
469 @ValNonRX decimal,
470 @ValSustinutMDP decimal,
471 @ValOferteRebate decimal,
472 @Status int,
473 @Numar int,
474 @NumeClient varchar(200),
475 @IdOperator int,
476 @OldStatus int,
477 @TempIdOperator int,
478 @IdOperatorMKT int,
479 @IdNegocieri int,
480 @TipAprobare int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'TipActiuneNegocieri' and t0.Nume = 'Aprobare'),
481 @TipUserASM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'ASM' and t1.Nume = 'CustomUserType'),
482 @TipUserMKT int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'MKT' and t1.Nume = 'CustomUserType'),
483 @TipUserRSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'RSM' and t1.Nume = 'CustomUserType'),
484 @TipUserNSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'NSM' and t1.Nume = 'CustomUserType'),
485 @Marja decimal,
486 @profile_name varchar(60) = 'Negocieri',
487 @body_format varchar(60) = 'TEXT',
488 @Key uniqueidentifier = newid(),
489 --@Produse varchar(max) = '',
490 @WorkflowServerAddress varchar(100) = (select t0.Valoare from dbo.InitInfo (nolock) t0 where t0.Nume = 'WorkFlowServer'),
491 @NumeAsm varchar(201),
492 @NumeProducator varchar(200),
493 @GrossSales money,
494 @CAF money,
495 @MargineReala money,
496 @Observatie varchar(450),
497 @MarjaComanda money,
498 @MarjaReala money,
499 @TipNegociereBranduriProprii int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'Branduri Proprii' and t1.Nume = 'TipNegocieri'),
500 @Automated bit = 0;
501
502 if object_id('tempdb..#Negocieri') is not null
503 drop table #Negocieri
504
505 create table #Negocieri (IdNegocieriPK int not null, IdClient int not null, IdPLClient int not null,
506 Numar int null, DataInceput datetime not null, DataSfarsit datetime not null,
507 DataCreare datetime not null, IdOperatorCreare int not null, MomentOperare datetime not null,
508 IdOperator int not null, [Status] int not null, OperatorAprobareRV int null,
509 DataAprobareRV datetime null, OperatorAprobareASM int null, DataAprobareASM datetime null,
510 OperatorRespinsASM int null, DataRespinsASM datetime null, OperatorAprobareMKT int null,
511 DataAprobareMKT datetime null, OperatorRespinsMKT int null, DataRespinsMKT datetime null,
512 OperatorAprobareRSM int null, DataAprobareRSM datetime null, OperatorRespinsRSM int null,
513 DataRespinsRSM datetime null, OperatorAprobareNSM int null, DataAprobareNSM datetime null,
514 OperatorRespinsNSM int null, DataRespinsNSM datetime null, Valoare money not null,
515 NrCrt int not null, ProcDisc money not null, BazaImpozitare money not null,
516 NotApplyAutomaticallyDisc smallint null, ToatePL smallint null, FacturareAutomata smallint null, DiscountApplicationType int null, IdTipNegociere int null,
517 ModificaDisc bit null, MotivRefuz nvarchar(300) null, EditareDisc bit null,
518 Realizat bit null, Observatie varchar(450) null, IsTermenPlataDiferentiat smallint null,
519 ObservatiiMKT varchar(450) null, MotivMarjaInsuficienta int null, IdProducator int null,
520 FeedBack smallint null, IdMasterNegocieri int null, IdParinteNegocieri int null,
521 Versiune int null, IsManualConfirmation bit null,
522 constraint PK_Negocieri_Temp primary key clustered (IdNegocieriPK asc))
523
524 if object_id('tempdb..#ElemNegocieri') is not null
525 drop table #ElemNegocieri
526
527 create table #ElemNegocieri (IdElemNegocieriPK int not null, IdNegocieriPK int not null, IdProdus int not null,
528 CantUV numeric(15, 6) not null, CantUVRamas numeric(15, 6) not null, Pret money not null,
529 ProcDiscTotal money not null, ProcDiscTotalRec money not null, ProcDisc money not null,
530 ProcTVA money not null, Valoare money not null, PretRidicata money null,
531 PretFaraDisc money null, TermenPlata int null, ProcMarja numeric(7, 4) null,
532 IdMarja int null, IsRabat smallint null, CantBax int null,
533 IdDivizie int null, ProcMarjaCalculat numeric(20, 4) null, IdElemOfertaSpeciala int null,
534 ProcDiscOfferDetail money null, ProcDiscOfferHeader money null, IsRabatHeaderOffer smallint null,
535 IsRabatDetailOffer smallint null, ProcDiscRecOfferDetail money null, ProcDiscRecOfferHeader money null,
536 ProcMarjaMinim numeric(18, 0) null, ProcDiscAutomat numeric(7, 4) null, ProcMarjaReal numeric(18, 0) null,
537 FinalSettlementTypeId int null, FinalPaymentTerm int null, DiscountRatePT numeric(6, 3) null,
538 GenerateFinDocMasterPT smallint null, SuportatProducatorOfferDetail money null, SuportatMDPOfferDetail money null,
539 SuportatASMOfferDetail money null, SuportatProducatorOfferHeader money null, SuportatMDPOfferHeader money null,
540 SuportatASMOfferHeader money null, ProcDiscSuplimentar money null, DiscSustinutOSProducator money null,
541 DiscSustinutOSMkt money null, DiscSustinutOSAsm money null, ValDiscSuplimentarProducator money null,
542 DiscSuplimentarProducator money null, ValDiscSustinutCosMkt money null, DiscSustinutCosMkt money null,
543 ValDiscSustinutCosAsm money null, DiscSustinutCosAsm money null, ValDiscSustinutCos money null,
544 DiscSustinutCos money null, ValDiscSustinutReversare money null, DiscSustinutReversare money null,
545 ValDiscSustinutNonRx money null, DiscSustinutNonRx money null, ValDisc money null,
546 ValoareNeta money null, MargineNeta money null, MarjaNeta money null,
547 NrCrt int not null, IdOfertaSpecialaTemp int null, Inlocuit smallint null,
548 DataInlocuit datetime null, ElementInlocuit int null, CantInlocuita int null,
549 MargineReala money null, MargineRealaElInlocuit money null, ElemNegocieriIstoric int null,
550 PretNet money null, SuportatCosMKTOfferDetail money null, SuportatCosMKTOfferHeader money null,
551 DiscSustinutOSCosMKT money null, ValDiscSuplimentarProducatorRec money null, DiscSuplimentarProducatorRec money null,
552 RecuperareRN smallint null, ExistaModificari smallint null, DiscOferteRebate money null,
553 ValDiscOferteRebate money null, DiscSuplimentarSustinutMDP money null, ValDiscSuplimentarSustinutMDP money null,
554 DiscSustinutASPRI money null, ValDiscSustinutASPRI money null, IdTipDecontare int null, IsModificat bit null,
555 constraint PK_ElemNegocieri_Temp primary key clustered (IdElemNegocieriPK asc))
556
557 insert into #Negocieri (IdNegocieriPK, IdClient, IdPLClient, Numar, DataInceput, DataSfarsit, DataCreare, IdOperatorCreare,
558 MomentOperare, IdOperator, [Status], OperatorAprobareRV, DataAprobareRV, OperatorAprobareASM,
559 DataAprobareASM, OperatorRespinsASM, DataRespinsASM, OperatorAprobareMKT, DataAprobareMKT,
560 OperatorRespinsMKT, DataRespinsMKT, OperatorAprobareRSM, DataAprobareRSM, OperatorRespinsRSM,
561 DataRespinsRSM, OperatorAprobareNSM, DataAprobareNSM, OperatorRespinsNSM, DataRespinsNSM,
562 Valoare, NrCrt, ProcDisc, BazaImpozitare, NotApplyAutomaticallyDisc, ToatePL, FacturareAutomata, DiscountApplicationType, IdTipNegociere,
563 ModificaDisc, MotivRefuz, EditareDisc, Realizat, Observatie, IsTermenPlataDiferentiat, ObservatiiMKT,
564 MotivMarjaInsuficienta, IdProducator, FeedBack, IdMasterNegocieri, IdParinteNegocieri, Versiune, IsManualConfirmation)
565 select t0.IdNegocieriPK, t0.IdClient, t0.IdPLClient, t0.Numar, t0.DataInceput, t0.DataSfarsit, t0.DataCreare, t0.IdOperatorCreare,
566 t0.MomentOperare, t0.IdOperator, t0.[Status], t0.OperatorAprobareRV, t0.DataAprobareRV, t0.OperatorAprobareASM,
567 t0.DataAprobareASM, t0.OperatorRespinsASM, t0.DataRespinsASM, t0.OperatorAprobareMKT, t0.DataAprobareMKT,
568 t0.OperatorRespinsMKT, t0.DataRespinsMKT, t0.OperatorAprobareRSM, t0.DataAprobareRSM, t0.OperatorRespinsRSM,
569 t0.DataRespinsRSM, t0.OperatorAprobareNSM, t0.DataAprobareNSM, t0.OperatorRespinsNSM, t0.DataRespinsNSM,
570 t0.Valoare, t0.NrCrt, t0.ProcDisc, t0.BazaImpozitare, t0.NotApplyAutomaticallyDisc, t0.ToatePL, t0.FacturareAutomata, t0.DiscountApplicationType, t0.IdTipNegociere,
571 t0.ModificaDisc, t0.MotivRefuz, t0.EditareDisc, t0.Realizat, t0.Observatie, t0.IsTermenPlataDiferentiat, t0.ObservatiiMKT,
572 t0.MotivMarjaInsuficienta, t0.IdProducator, t0.FeedBack, t0.IdMasterNegocieri, t0.IdParinteNegocieri, t0.Versiune, t0.IsManualConfirmation
573 from @Negocieri t0
574
575 insert into #ElemNegocieri (IdElemNegocieriPK, IdNegocieriPK, IdProdus, CantUV, CantUVRamas, Pret, ProcDiscTotal, ProcDiscTotalRec,
576 ProcDisc, ProcTVA, Valoare, PretRidicata, PretFaraDisc, TermenPlata, ProcMarja, IdMarja, IsRabat, CantBax,
577 IdDivizie, ProcMarjaCalculat, IdElemOfertaSpeciala, ProcDiscOfferDetail, ProcDiscOfferHeader, IsRabatHeaderOffer,
578 IsRabatDetailOffer, ProcDiscRecOfferDetail, ProcDiscRecOfferHeader, ProcMarjaMinim, ProcDiscAutomat, ProcMarjaReal,
579 FinalSettlementTypeId, FinalPaymentTerm, DiscountRatePT, GenerateFinDocMasterPT, SuportatProducatorOfferDetail,
580 SuportatMDPOfferDetail, SuportatASMOfferDetail, SuportatProducatorOfferHeader, SuportatMDPOfferHeader,
581 SuportatASMOfferHeader, ProcDiscSuplimentar, DiscSustinutOSProducator, DiscSustinutOSMkt, DiscSustinutOSAsm,
582 ValDiscSuplimentarProducator, DiscSuplimentarProducator, ValDiscSustinutCosMkt, DiscSustinutCosMkt,
583 ValDiscSustinutCosAsm, DiscSustinutCosAsm, ValDiscSustinutCos, DiscSustinutCos, ValDiscSustinutReversare,
584 DiscSustinutReversare, ValDiscSustinutNonRx, DiscSustinutNonRx, ValDisc, ValoareNeta, MargineNeta, MarjaNeta,
585 NrCrt, IdOfertaSpecialaTemp, Inlocuit, DataInlocuit, ElementInlocuit, CantInlocuita, MargineReala, MargineRealaElInlocuit,
586 ElemNegocieriIstoric, PretNet, SuportatCosMKTOfferDetail, SuportatCosMKTOfferHeader, DiscSustinutOSCosMKT, ValDiscSuplimentarProducatorRec,
587 DiscSuplimentarProducatorRec, RecuperareRN, ExistaModificari, DiscOferteRebate, ValDiscOferteRebate, DiscSuplimentarSustinutMDP,
588 ValDiscSuplimentarSustinutMDP, DiscSustinutASPRI, ValDiscSustinutASPRI, IdTipDecontare, IsModificat)
589 select t0.IdElemNegocieriPK, t0.IdNegocieriPK, t0.IdProdus, t0.CantUV, t0.CantUVRamas, t0.Pret, t0.ProcDiscTotal, t0.ProcDiscTotalRec,
590 t0.ProcDisc, t0.ProcTVA, t0.Valoare, t0.PretRidicata, t0.PretFaraDisc, t0.TermenPlata, t0.ProcMarja, t0.IdMarja, t0.IsRabat, t0.CantBax,
591 t0.IdDivizie, t0.ProcMarjaCalculat, t0.IdElemOfertaSpeciala, t0.ProcDiscOfferDetail, t0.ProcDiscOfferHeader, t0.IsRabatHeaderOffer,
592 t0.IsRabatDetailOffer, t0.ProcDiscRecOfferDetail, t0.ProcDiscRecOfferHeader, t0.ProcMarjaMinim, t0.ProcDiscAutomat, t0.ProcMarjaReal,
593 t0.FinalSettlementTypeId, t0.FinalPaymentTerm, t0.DiscountRatePT, t0.GenerateFinDocMasterPT, t0.SuportatProducatorOfferDetail,
594 t0.SuportatMDPOfferDetail, t0.SuportatASMOfferDetail, t0.SuportatProducatorOfferHeader, t0.SuportatMDPOfferHeader,
595 t0.SuportatASMOfferHeader, t0.ProcDiscSuplimentar, t0.DiscSustinutOSProducator, t0.DiscSustinutOSMkt, t0.DiscSustinutOSAsm,
596 t0.ValDiscSuplimentarProducator, t0.DiscSuplimentarProducator, t0.ValDiscSustinutCosMkt, t0.DiscSustinutCosMkt,
597 t0.ValDiscSustinutCosAsm, t0.DiscSustinutCosAsm, t0.ValDiscSustinutCos, t0.DiscSustinutCos, t0.ValDiscSustinutReversare,
598 t0.DiscSustinutReversare, t0.ValDiscSustinutNonRx, t0.DiscSustinutNonRx, t0.ValDisc, t0.ValoareNeta, t0.MargineNeta, t0.MarjaNeta,
599 t0.NrCrt, t0.IdOfertaSpecialaTemp, t0.Inlocuit, t0.DataInlocuit, t0.ElementInlocuit, t0.CantInlocuita, t0.MargineReala, t0.MargineRealaElInlocuit,
600 t0.ElemNegocieriIstoric, t0.PretNet, t0.SuportatCosMKTOfferDetail, t0.SuportatCosMKTOfferHeader, t0.DiscSustinutOSCosMKT, t0.ValDiscSuplimentarProducatorRec,
601 t0.DiscSuplimentarProducatorRec, t0.RecuperareRN, t0.ExistaModificari, t0.DiscOferteRebate, t0.ValDiscOferteRebate, t0.DiscSuplimentarSustinutMDP,
602 t0.ValDiscSuplimentarSustinutMDP, t0.DiscSustinutASPRI, t0.ValDiscSustinutASPRI, t0.IdTipDecontare, t0.IsModificat
603 from @ElemNegocieri t0
604
605 select @IdMotivMarjaInsuficienta = t0.MotivMarjaInsuficienta, @Status = t0.[Status], @Numar = t0.Numar,
606 @NumeClient = t1.Nume, @OldStatus = isnull(t2.[Status], t0.[Status]), @IdNegocieri = t0.IdNegocieriPK,
607 @Automated = CASE WHEN (ISNULL(t0.IdTipNegociere, 0) = @TipNegociereBranduriProprii) THEN 1 ELSE 0 END
608 from #Negocieri t0 join dbo.Firma (nolock) t1 on t1.IdFirma = t0.IdClient
609 left join dbo.Negocieri t2 on t0.IdNegocieriPK = t2.IdNegocieriPK
610
611 if @Status != @OldStatus
612 raiserror('START_CUSTOM_MESSAGE Negocierea a fost modificata. Te rog sa reincarci negocierea END_CUSTOM_MESSAGE', 18, 1)
613
614 if @IsDebug = 1
615 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step, Info)
616 values (@CallId, sysdatetime(), '030',
617 '@Status - ' + (case when @Status is null then '[null]' else convert(nvarchar, @Status) + ' (' + (select t0.Nume from dbo.ElemDic (nolock) t0 where t0.IdElemDic = @Status) + ')' end) +
618 ', @OldStatus - ' + (case when @OldStatus is null then '[null]' else convert(nvarchar, @OldStatus) + ' (' + (select t0.Nume from dbo.ElemDic (nolock) t0 where t0.IdElemDic = @OldStatus) + ')' end))
619
620 set @Marja = isnull((select t0.MarjaReala from @MarjaNegocieri t0), 0)
621
622 if @PragMarja > @Marja and @IdMotivMarjaInsuficienta is null
623 raiserror('START_CUSTOM_MESSAGE Campul "Motiv marja insuficienta" trebuie completat! END_CUSTOM_MESSAGE', 18, 1)
624
625
626 SELECT @IdOperator = t0.IdUtilizator from dbo.CustomUser (nolock) t0 where t0.Oid = @OperatorOid
627
628 IF (@Automated = 1)
629 BEGIN
630
631 IF (@OldStatus is null or @OldStatus = @GeneratRV_RVT or @OldStatus = @RespinsASM or @OldStatus = @RespinsMKT or @OldStatus = @RespinsRSM or @OldStatus = @RespinsNSM) and
632 (@Status = @GeneratRV_RVT or @Status = @RespinsASM or @Status = @RespinsMKT or @Status = @RespinsRSM or @Status = @RespinsNSM)
633 BEGIN
634 update t0 set t0.OperatorAprobareRV = @IdOperator, t0.DataAprobareRV = getdate(), t0.[Status] = @AprobareASM from #Negocieri t0
635 set @Status = @AprobareASM
636 END
637
638 IF (@Status = @AprobareASM)
639 BEGIN
640 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '060')
641
642 if (isnull(@OldStatus, @GeneratRV_RVT) != @Status)
643 begin
644 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '063')
645 set @TempIdOperator = (select t0.Chief from CustomUserXChief (nolock) t0 where t0.CustomUsers = @OperatorOid)
646 end
647 else
648 begin
649 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '066')
650 set @TempIdOperator = @IdOperator
651 end
652
653 update #Negocieri set OperatorAprobareASM = @TempIdOperator, DataAprobareASM = getdate(), [Status] = @AprobareMKT
654 set @Status = @AprobareMKT
655 END
656 IF (@Status = @AprobareMKT)
657 BEGIN
658 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '080')
659
660 if (isnull(@OldStatus, @GeneratRV_RVT) != @Status)
661 begin
662 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '083')
663 set @TempIdOperator = (select t0.IdUtilizator from dbo.CustomUser (nolock) t0 where t0.IsActive = 1 and t0.UserType = @TipUserMKT and t0.UserName = 'iuliac')
664 end
665 else
666 begin
667 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '086')
668 set @TempIdOperator = @IdOperator
669 end
670
671 update #Negocieri set OperatorAprobareMKT = @TempIdOperator, DataAprobareMKT = getdate(), [Status] = @AprobareRSM
672 set @Status = @AprobareRSM;
673
674 END
675
676 IF (@Status = @AprobareRSM)
677 BEGIN
678 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '092')
679
680 if (isnull(@OldStatus, @GeneratRV_RVT) != @Status)
681 begin
682 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '094')
683 set @TempIdOperator = ( select t0.Chief
684 from dbo.CustomUserXChief (nolock) t0 join dbo.CustomUser (nolock) t1 on t1.Oid = t0.CustomUsers
685 join #Negocieri t2 on t2.OperatorAprobareASM = t1.IdUtilizator
686 where t1.IsActive = 1 )
687 end
688 else
689 begin
690 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '096')
691 set @TempIdOperator = @IdOperator
692 end
693
694 update #Negocieri set OperatorAprobareRSM = @TempIdOperator, DataAprobareRSM = getdate(), [Status] = @AprobareNSM
695 set @Status = @AprobareNSM
696
697 END
698
699 IF (@Status = @AprobareNSM)
700 BEGIN
701 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '100')
702
703 if (isnull(@OldStatus, @GeneratRV_RVT) != @Status)
704 begin
705 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '103')
706 set @TempIdOperator = ( select t0.Chief
707 from dbo.CustomUserXChief (nolock) t0 join dbo.CustomUser (nolock) t1 on t1.Oid = t0.CustomUsers
708 join #Negocieri t2 on t2.OperatorAprobareRSM = t1.IdUtilizator )
709 end
710 else
711 begin
712 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '106')
713 set @TempIdOperator = @IdOperator
714 end
715
716 update #Negocieri set OperatorAprobareNSM = @TempIdOperator, DataAprobareNSM = getdate(), Status = @AprobataIntegral
717 set @Status = @AprobataIntegral
718 END
719 END
720
721 ELSE
722 BEGIN
723
724 select @CosMKT = sum(ValDiscSustinutCosMkt), @Reversare = sum(ValDiscSustinutReversare), @ValSupSustinutProducator = sum(ValDiscSuplimentarProducatorRec),
725 @ValDiscount = sum (ValDisc), @ValCosASM = sum(ValDiscSustinutCosAsm), @ValCos = sum(ValDiscSustinutCos), @ValNonRX = sum(ValDiscSustinutNonRx),
726 @ValSustinutMDP = sum(ValDiscSuplimentarSustinutMDP), @ValOferteRebate = sum(ValDiscOferteRebate)
727 from @ElemNegocieri
728
729 declare @CheckOk bit = 0,
730 @Subject varchar(250),
731 @Body varchar(max),
732 @ToRecipients varchar(250)
733
734 if (@OldStatus is null or @OldStatus = @GeneratRV_RVT or @OldStatus = @RespinsASM or @OldStatus = @RespinsMKT or @OldStatus = @RespinsRSM or @OldStatus = @RespinsNSM) and
735 (@Status = @GeneratRV_RVT or @Status = @RespinsASM or @Status = @RespinsMKT or @Status = @RespinsRSM or @Status = @RespinsNSM)
736 begin
737 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '040')
738
739 update t0 set t0.OperatorAprobareRV = @IdOperator, t0.DataAprobareRV = getdate(), t0.[Status] = @AprobareASM from #Negocieri t0
740 set @Status = @AprobareASM
741
742 exec dbo.newSP_Negocieri_VerificaLimite @TipUserASM, @ValDiscount, @CosMKT,
743 @ValSupSustinutProducator, @Reversare, @ValCosASM,
744 @ValCos, @ValNonRX, @Marja,
745 @ValSustinutMDP, @ValOferteRebate, @CheckOk output
746
747 if (@CheckOk = 0)
748 begin
749 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '050')
750
751 insert into dbo.NegocieriWorkflow ([Key], IdNegocieriPK, OperatorOid)
752 select @Key, t0.IdNegocieriPK, t2.Oid
753 from #Negocieri t0, dbo.CustomUserXChief (nolock) t1 join dbo.CustomUser (nolock) t2 on t1.Chief = t2.IdUtilizator
754 where t1.CustomUsers = @OperatorOid
755
756 select @NumeAsm = isnull(ltrim(rtrim(ltrim(rtrim(isnull(t2.FirstName, ' '))) + ' ' + ltrim(rtrim(isnull(t2.LastName, ' '))))), t2.UserName)
757 from dbo.CustomUserXChief (nolock) t1 join dbo.CustomUser (nolock) t2 on t1.Chief = t2.IdUtilizator
758 where t1.CustomUsers = @OperatorOid
759
760 select top 1 @NumeProducator = t2.Nume
761 from @ElemNegocieri t0 join dbo.Produs (nolock) t1 on t0.IdProdus = t1.IdProdus
762 join dbo.Firma (nolock) t2 on t1.IdProducator = t2.IdFirma
763
764 select @GrossSales = 0, @CAF = 0, @MarjaComanda = 0, @MarjaReala = 0
765 select @GrossSales = sum(isnull(t0.GrossSales, 0)), @CAF = sum(isnull(t0.CAF, 0)), @MarjaComanda = sum(isnull(t0.MarjaComanda, 0)),
766 @MarjaReala = sum(isnull(t0.MarjaReala, 0))
767 from @MarjaNegocieri t0
768
769 set @MargineReala = 0
770 select @MargineReala = sum(isnull(t0.MargineReala, 0)) from @ElemNegocieri t0
771
772 set @Subject = 'Aprobare negociere #' + cast(@Numar as varchar)
773
774 /*set @Produse = ''
775 select @Produse = stuff((
776 select '!lt!tr!gt!!lt!td!gt!' + t2.Nume + '!lt!/td!gt!!lt!td!gt!' + t1.Nume + '!lt!/td!gt!!lt!/tr!gt!'
777 from @ElemNegocieri t0 join dbo.Produs t1 on t0.IdProdus = t1.IdProdus
778 join dbo.Firma t2 on t1.IdProducator = t2.IdFirma
779 for xml path('')), 1, 0, '')*/
780
781 insert into dbo._workflowArgs (PageTitle, ClientName, AsmName,
782 ProducerName, GrossSales, CAF,
783 RealDiff, OrderMargin, RealMargin,
784 Obs, WorkflowServerAddress, [Key],
785 ProductRows)
786 values (@Subject, @NumeClient, @NumeAsm,
787 @NumeProducator, convert(varchar(100), @GrossSales), convert(varchar(100), @CAF),
788 convert(varchar(100), @MargineReala), convert(varchar(100), @MarjaComanda), convert(varchar(100), @MarjaReala),
789 @Obs, @WorkflowServerAddress, @Key,
790 --@Produse)
791 null)
792
793 set @Body = dbo.newFN_GetEmailBodyForAsmAndNsm (@Subject, @NumeClient, @NumeAsm,
794 @NumeProducator, convert(varchar(100), @GrossSales), convert(varchar(100), @CAF),
795 convert(varchar(100), @MargineReala), convert(varchar(100), @MarjaComanda), convert(varchar(100), @MarjaReala),
796 @Obs, @WorkflowServerAddress, convert(nvarchar(36), @Key))
797
798 select @ToRecipients = Email
799 from dbo.CustomUserXChief (nolock) t0 join dbo.CustomUser (nolock) t1 on t0.Chief = t1.IdUtilizator
800 where t0.CustomUsers = @OperatorOid
801
802 set @body_format = 'HTML'
803 exec dbo.newSP_SendEmail @Subject, @Body, @ToRecipients, @profile_name, @body_format
804 end
805 end
806
807 if (@OldStatus = @AprobareASM or (@Status = @AprobareASM and @CheckOk = 1))
808 begin
809 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '060')
810
811 if (isnull(@OldStatus, @GeneratRV_RVT) != @Status)
812 begin
813 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '063')
814 set @TempIdOperator = (select t0.Chief from CustomUserXChief (nolock) t0 where t0.CustomUsers = @OperatorOid)
815 end
816 else
817 begin
818 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '066')
819 set @TempIdOperator = @IdOperator
820 end
821
822 update #Negocieri set OperatorAprobareASM = @TempIdOperator, DataAprobareASM = getdate(), [Status] = @AprobareMKT
823 set @Status = @AprobareMKT
824 exec dbo.newSP_Negocieri_VerificaLimite @TipUserMKT, @ValDiscount, @CosMKT,
825 @ValSupSustinutProducator, @Reversare, @ValCosASM,
826 @ValCos, @ValNonRX, @Marja,
827 @ValSustinutMDP, @ValOferteRebate, @CheckOk output
828
829 --modificare 15.05.2019 Razvan
830 --if (@CheckOk = 0)
831 --begin
832 -- if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '070')
833
834 -- select top 1 @IdOperatorMKT = t0.IdUtilizator, @ToRecipients = t0.Email from dbo.CustomUser (nolock) t0 where t0.IsActive = 1 and t0.UserType = @TipUserMKT
835 -- --chestia asta e mega dubioasa, alege complet random la ce user de marketing sa trimita mail-ul
836
837 -- if (@IdOperatorMKT is not null)
838 -- begin
839 -- if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '075')
840 -- set @Subject = 'Aprobare negociere #' + cast(@Numar as varchar)
841 -- set @Body = 'Client: ' + @NumeClient
842 -- set @Body = @Body + char(13) + char(10) + 'Producator: ' + isnull((select top 1 t2.Nume
843 -- from #ElemNegocieri t0 join dbo.Produs (nolock) t1 on t1.IdProdus = t0.IdProdus
844 -- join dbo.Firma (nolock) t2 on t2.IdFirma = t1.IdProducator), ' ')
845 -- exec dbo.newSP_SendEmail @Subject, @Body, @ToRecipients, @profile_name, @body_format
846 -- end
847 --end
848 end
849
850 if ((@OldStatus = @AprobareMKT) or (@Status = @AprobareMKT and @CheckOk = 1))
851 begin
852 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '080')
853
854 if (isnull(@OldStatus, @GeneratRV_RVT) != @Status)
855 begin
856 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '083')
857 set @TempIdOperator = (select t0.IdUtilizator from dbo.CustomUser (nolock) t0 where t0.IsActive = 1 and t0.UserType = @TipUserMKT and t0.UserName = 'iuliac')
858 end
859 else
860 begin
861 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '086')
862 set @TempIdOperator = @IdOperator
863 end
864
865 update #Negocieri set OperatorAprobareMKT = @TempIdOperator, DataAprobareMKT = getdate(), [Status] = @AprobareRSM
866 set @Status = @AprobareRSM;
867 exec dbo.newSP_Negocieri_VerificaLimite @TipUserRSM, @ValDiscount, @CosMKT,
868 @ValSupSustinutProducator, @Reversare, @ValCosASM,
869 @ValCos, @ValNonRX, @Marja,
870 @ValSustinutMDP, @ValOferteRebate, @CheckOk output
871 if (@CheckOk = 0)
872 begin
873 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '090')
874
875 set @Subject = 'Aprobare negociere #' + cast(@Numar as varchar)
876 set @Body = 'Client: ' + @NumeClient
877 set @ToRecipients = ( select Email
878 from CustomUserXChief (nolock) t0 join CustomUser (nolock) t1 on t1.Oid = t0.CustomUsers
879 join #Negocieri t2 on t2.OperatorAprobareASM = t1.IdUtilizator
880 where t1.IsActive = 1 )
881 exec dbo.newSP_SendEmail @Subject, @Body, @ToRecipients, @profile_name, @body_format
882 end
883 end
884
885 if ((@OldStatus = @AprobareRSM) or (@Status = @AprobareRSM and @CheckOk = 1))
886 begin
887 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '092')
888
889 if (isnull(@OldStatus, @GeneratRV_RVT) != @Status)
890 begin
891 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '094')
892 set @TempIdOperator = ( select t0.Chief
893 from dbo.CustomUserXChief (nolock) t0 join dbo.CustomUser (nolock) t1 on t1.Oid = t0.CustomUsers
894 join #Negocieri t2 on t2.OperatorAprobareASM = t1.IdUtilizator
895 where t1.IsActive = 1 )
896 end
897 else
898 begin
899 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '096')
900 set @TempIdOperator = @IdOperator
901 end
902
903 update #Negocieri set OperatorAprobareRSM = @TempIdOperator, DataAprobareRSM = getdate(), [Status] = @AprobareNSM
904 set @Status = @AprobareNSM
905 exec dbo.newSP_Negocieri_VerificaLimite @TipUserNSM, @ValDiscount, @CosMKT,
906 @ValSupSustinutProducator, @Reversare, @ValCosASM,
907 @ValCos, @ValNonRX, @Marja,
908 @ValSustinutMDP, @ValOferteRebate, @CheckOk output
909
910 if (@CheckOk = 0)
911 begin
912 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '098')
913
914 insert into dbo.NegocieriWorkflow ([Key], IdNegocieriPK, OperatorOid)
915 select @Key, t0.IdNegocieriPK, t2.Oid
916 from dbo.CustomUserXChief (nolock) t1 join dbo.CustomUser (nolock) t2 on t2.Oid = t1.CustomUsers
917 join #Negocieri t0 on t0.OperatorAprobareRSM = t2.IdUtilizator
918 where t2.IsActive = 1
919
920 select @NumeAsm = isnull(ltrim(rtrim(ltrim(rtrim(isnull(t2.FirstName, ' '))) + ' ' + ltrim(rtrim(isnull(t2.LastName, ' '))))), t2.UserName)
921 from dbo.CustomUser (nolock) t2 join #Negocieri t3 on t3.OperatorAprobareASM = t2.IdUtilizator
922
923 select top 1 @NumeProducator = t2.Nume
924 from @ElemNegocieri t0 join dbo.Produs (nolock) t1 on t0.IdProdus = t1.IdProdus
925 join dbo.Firma (nolock) t2 on t1.IdProducator = t2.IdFirma
926
927 select @GrossSales = 0, @CAF = 0, @MarjaComanda = 0, @MarjaReala = 0
928 select @GrossSales = sum(isnull(t0.GrossSales, 0)), @CAF = sum(isnull(t0.CAF, 0)), @MarjaComanda = sum(isnull(t0.MarjaComanda, 0)),
929 @MarjaReala = sum(isnull(t0.MarjaReala, 0))
930 from @MarjaNegocieri t0
931
932 set @MargineReala = 0
933 select @MargineReala = sum(isnull(t0.MargineReala, 0)) from @ElemNegocieri t0
934
935 set @Subject = 'Aprobare negociere #' + cast(@Numar as varchar)
936
937 /*set @Produse = ''
938 select @Produse = stuff((
939 select '!lt!tr!gt!!lt!td!gt!' + t2.Nume + '!lt!/td!gt!!lt!td!gt!' + t1.Nume + '!lt!/td!gt!!lt!/tr!gt!'
940 from @ElemNegocieri t0 join dbo.Produs t1 on t0.IdProdus = t1.IdProdus
941 join dbo.Firma t2 on t1.IdProducator = t2.IdFirma
942 for xml path('')), 1, 0, '')*/
943
944 insert into dbo._workflowArgs (PageTitle, ClientName, AsmName,
945 ProducerName, GrossSales, CAF,
946 RealDiff, OrderMargin, RealMargin,
947 Obs, WorkflowServerAddress, [Key],
948 ProductRows)
949 values (@Subject, @NumeClient, @NumeAsm,
950 @NumeProducator, @GrossSales, @CAF,
951 @MargineReala, @MarjaComanda, @MarjaReala,
952 @Obs, @WorkflowServerAddress, @Key,
953 --@Produse)
954 null)
955 set @Body = dbo.newFN_GetEmailBodyForAsmAndNsm (@Subject, @NumeClient, @NumeAsm,
956 @NumeProducator, convert(varchar(100), @GrossSales), convert(varchar(100), @CAF),
957 convert(varchar(100), @MargineReala), convert(varchar(100), @MarjaComanda), convert(varchar(100), @MarjaReala),
958 @Obs, @WorkflowServerAddress, convert(nvarchar(36), @Key)/*,
959 @Produse*/)
960
961 /*select @ToRecipients = t1.Email
962 from dbo.CustomUser t1 (nolock) join #Negocieri t2 on t2.OperatorAprobareRSM = t1.IdUtilizator
963 where t1.IsActive = 1*/
964 set @ToRecipients = (select Email
965 from dbo.CustomUserXChief (nolock) t0 join dbo.CustomUser (nolock) t1 on t1.Oid = t0.CustomUsers
966 join #Negocieri t2 on t2.OperatorAprobareRSM = t1.IdUtilizator
967 where t1.IsActive = 1)
968
969 set @body_format = 'HTML'
970 exec dbo.newSP_SendEmail @Subject, @Body, @ToRecipients, @profile_name, @body_format
971 end
972 end
973
974 if ((@OldStatus = @AprobareNSM) or (@Status = @AprobareNSM and @CheckOk = 1))
975 begin
976 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '100')
977
978 if (isnull(@OldStatus, @GeneratRV_RVT) != @Status)
979 begin
980 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '103')
981 set @TempIdOperator = ( select t0.Chief
982 from dbo.CustomUserXChief (nolock) t0 join dbo.CustomUser (nolock) t1 on t1.Oid = t0.CustomUsers
983 join #Negocieri t2 on t2.OperatorAprobareRSM = t1.IdUtilizator )
984 end
985 else
986 begin
987 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '106')
988 set @TempIdOperator = @IdOperator
989 end
990
991 update #Negocieri set OperatorAprobareNSM = @TempIdOperator, DataAprobareNSM = getdate(), Status = @AprobataIntegral
992 set @Status = @AprobataIntegral
993 end
994 END
995
996 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Aproba_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '110')
997
998 declare @SaveNegocieri TypeNegocieri, @SaveActiuniNegocieri TypeActiuniNegocieri
999
1000 insert into @SaveNegocieri
1001 (IdNegocieriPK, IdClient, IdPLClient, Numar, DataInceput, DataSfarsit, DataCreare, IdOperatorCreare,
1002 MomentOperare, IdOperator, [Status], OperatorAprobareRV, DataAprobareRV, OperatorAprobareASM,
1003 DataAprobareASM, OperatorRespinsASM, DataRespinsASM, OperatorAprobareMKT, DataAprobareMKT,
1004 OperatorRespinsMKT, DataRespinsMKT, OperatorAprobareRSM, DataAprobareRSM, OperatorRespinsRSM,
1005 DataRespinsRSM, OperatorAprobareNSM, DataAprobareNSM, OperatorRespinsNSM, DataRespinsNSM,
1006 Valoare, NrCrt, ProcDisc, BazaImpozitare, NotApplyAutomaticallyDisc, ToatePL, FacturareAutomata, DiscountApplicationType, IdTipNegociere,
1007 ModificaDisc, MotivRefuz, EditareDisc, Realizat, Observatie, IsTermenPlataDiferentiat, ObservatiiMKT,
1008 MotivMarjaInsuficienta, IdProducator, FeedBack, IdMasterNegocieri, IdParinteNegocieri, Versiune, IsManualConfirmation)
1009 select t0.IdNegocieriPK, t0.IdClient, t0.IdPLClient, t0.Numar, t0.DataInceput, t0.DataSfarsit, t0.DataCreare, t0.IdOperatorCreare,
1010 t0.MomentOperare, t0.IdOperator, t0.[Status], t0.OperatorAprobareRV, t0.DataAprobareRV, t0.OperatorAprobareASM,
1011 t0.DataAprobareASM, t0.OperatorRespinsASM, t0.DataRespinsASM, t0.OperatorAprobareMKT, t0.DataAprobareMKT,
1012 t0.OperatorRespinsMKT, t0.DataRespinsMKT, t0.OperatorAprobareRSM, t0.DataAprobareRSM, t0.OperatorRespinsRSM,
1013 t0.DataRespinsRSM, t0.OperatorAprobareNSM, t0.DataAprobareNSM, t0.OperatorRespinsNSM, t0.DataRespinsNSM,
1014 t0.Valoare, t0.NrCrt, t0.ProcDisc, t0.BazaImpozitare, t0.NotApplyAutomaticallyDisc, t0.ToatePL, t0.FacturareAutomata, t0.DiscountApplicationType, t0.IdTipNegociere,
1015 t0.ModificaDisc, t0.MotivRefuz, t0.EditareDisc, t0.Realizat, t0.Observatie, t0.IsTermenPlataDiferentiat, t0.ObservatiiMKT,
1016 t0.MotivMarjaInsuficienta, t0.IdProducator, t0.FeedBack, t0.IdMasterNegocieri, t0.IdParinteNegocieri, t0.Versiune, t0.IsManualConfirmation
1017 from #Negocieri t0
1018
1019 insert into @SaveActiuniNegocieri (IdActiuniNegocieri, IdNegocieri, IdPersoana, DataOperare, Tip, StatusInitial, StatusFinal, Obs)
1020 select t0.IdActiuniNegocieri, t0.IdNegocieri, t0.IdPersoana, t0.DataOperare, t0.Tip, t0.StatusInitial, t0.StatusFinal, t0.Obs
1021 from @ActiuniNegocieri t0
1022
1023 insert into @SaveActiuniNegocieri (IdActiuniNegocieri, IdNegocieri, IdPersoana, DataOperare, Tip, StatusInitial, StatusFinal, Obs)
1024 values (-1, isnull(@IdNegocieri, 0), @IdOperator, getdate(), @TipAprobare, isnull(@OldStatus, @GeneratRV_RVT), @Status, @Obs)
1025
1026 exec dbo.newSP_Negocieri_Save @SaveNegocieri, @ElemNegocieri, @NegocieriXDivizie, @MarjaNegocieri, @TermenPlataDiferentiatNegocieri,
1027 @SaveActiuniNegocieri, @ElemNegocieriXBasicTargetDetail, @ManualConfirmationDetailXElemNegocieri, @NegocieriFisier, @OperatorOid,
1028 @IdNegocieriPk output
1029
1030 update t0 set t0.IdNegocieriPK = @IdNegocieriPk from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key
1031
1032 if @IsDebug = 1 update t0 set t0.IdNegocieriPK = @IdNegocieriPk from dbo._newSP_Negocieri_Aproba_Calls t0 where t0.Id = @CallId
1033end try
1034begin catch
1035 declare @ErrorMessage nvarchar(4000),
1036 @ErrorSeverity int,
1037 @ErrorState int
1038 select @ErrorMessage = error_message(), @ErrorSeverity = error_severity(), @ErrorState = error_state()
1039 raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState)
1040end catch
1041
1042
1043GO
1044
1045
1046
1047-------------------------------------------------------------------------------------------------------------------------------------------------------
1048-------------------------------------------------------------------------------------------------------------------------------------------------------
1049-------------------------------------------------------------------------------------------------------------------------------------------------------
1050
1051
1052
1053CREATE procedure [dbo].[newSP_Negocieri_Save]
1054 @Negocieri TypeNegocieri readonly,
1055 @ElemNegocieri TypeElemNegocieri readonly,
1056 @NegocieriXDivizie TypeNegocieriXDivizie readonly,
1057 @MarjaNegocieri TypeMarjaNegocieri readonly,
1058 @TermenPlataDiferentiatNegocieri TypeTermenPlataDiferentiatNegocieri readonly,
1059 @ActiuniNegocieri TypeActiuniNegocieri readonly,
1060 @ElemNegocieriXBasicTargetDetail TypeElemNegocieriXBasicTargetDetail readonly,
1061 @ManualConfirmationDetailXElemNegocieri TypeManualConfirmationDetailXElemNegocieri readonly,
1062 @NegocieriFisier TypeNegocieriFisier readonly,
1063 @OperatorOid uniqueidentifier,
1064 @IdNegocieriPk int output
1065as
1066begin try
1067 declare @IsValid bit,
1068 @IsDebug bit = 1,
1069 @CallId uniqueidentifier = newid(),
1070 @CheckAdditionalDiscountPercentage bit = 0,
1071 @MomentOperareNegociere datetime,
1072 @OperatorOperareNegociere int
1073
1074 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Save_Calls (Id, [Time], Step, Info) values (@CallId, sysdatetime(), '010', '@OperatorOid - ' + convert(nvarchar(36), @OperatorOid))
1075
1076 exec dbo.newSP_Negocieri_Validari @Negocieri, @ElemNegocieri, @MarjaNegocieri, @TermenPlataDiferentiatNegocieri, @ActiuniNegocieri, @IsValid output, @CheckAdditionalDiscountPercentage
1077
1078 if @IsValid = 0
1079 begin
1080 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Save_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '020')
1081 return
1082 end
1083
1084 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Save_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '030')
1085
1086 declare @ASM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'ASM' and t1.Nume = 'CustomUserType'),
1087 @MKT int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'MKT' and t1.Nume = 'CustomUserType'),
1088 @RSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'RSM' and t1.Nume = 'CustomUserType'),
1089 @NSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'NSM' and t1.Nume = 'CustomUserType'),
1090 @RV int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'RV' and t1.Nume = 'CustomUserType'),
1091 @RVT int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'RVT' and t1.Nume = 'CustomUserType'),
1092 @RVTR int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'RVTR' and t1.Nume = 'CustomUserType'),
1093 @AprobareASM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare ASM'),
1094 @RespinsASM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Respins ASM'),
1095 @AprobareMKT int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare MKT'),
1096 @RespinsMKT int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Respins MKT'),
1097 @AprobareRSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare RSM'),
1098 @RespinsRSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Respins RSM'),
1099 @AprobareNSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare NSM'),
1100 @RespinsNSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Respins NSM'),
1101 @GeneratRV_RVT int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Generat RV/RVT'),
1102 @TipOperator int = (select t0.UserType from dbo.CustomUser (nolock) t0 where t0.Oid = @OperatorOid),
1103 @OldStatus int = (select t1.[Status] from @Negocieri t0 join dbo.Negocieri t1 on t0.IdNegocieriPK = t1.IdNegocieriPK),
1104 @NewStatus int = (select t0.[Status] from @Negocieri t0)
1105
1106 select @IdNegocieriPK = t0.IdNegocieriPK, @MomentOperareNegociere = t1.MomentOperare, @OperatorOperareNegociere = t1.IdOperator from dbo.Negocieri (nolock) t0 join @Negocieri t1 on t1.IdNegocieriPK = t0.IdNegocieriPK
1107
1108 if @IsDebug = 1
1109 insert into dbo._newSP_Negocieri_Save_Calls (Id, [Time], Step, Info)
1110 values (@CallId, sysdatetime(), '040',
1111 '@IdNegocieriPK - ' + (case when @IdNegocieriPK is null then '[null]' else convert(nvarchar, @IdNegocieriPK) end) +
1112 ', @OldStatus - ' + (case when @OldStatus is null then '[null]' else convert(nvarchar, @OldStatus) + ' (' + (select t0.Nume from dbo.ElemDic (nolock) t0 where t0.IdElemDic = @OldStatus) + ')' end) +
1113 ', @NewStatus - ' + (case when @NewStatus is null then '[null]' else convert(nvarchar, @NewStatus) + ' (' + (select t0.Nume from dbo.ElemDic (nolock) t0 where t0.IdElemDic = @NewStatus) + ')' end) +
1114 ', @TipOperator - ' + (case when @TipOperator is null then '[null]' else convert(nvarchar, @TipOperator) + ' (' + (select t0.Nume from dbo.ElemDic (nolock) t0 where t0.IdElemDic = @TipOperator) + ')' end))
1115
1116 if @OldStatus is null or
1117 ((@TipOperator = @RV or @TipOperator = @RVT or @TipOperator = @RVTR) and
1118 (@OldStatus = @GeneratRV_RVT or @OldStatus = @RespinsASM or @OldStatus = @RespinsMKT or @OldStatus = @RespinsNSM or @OldStatus = @RespinsRSM)) or
1119 (@TipOperator = @ASM and @OldStatus = @AprobareASM) or
1120 (@TipOperator = @MKT and @OldStatus = @AprobareMKT) or
1121 (@TipOperator = @NSM and @OldStatus = @AprobareNSM) or
1122 (@TipOperator = @RSM and @OldStatus = @AprobareRSM)
1123 begin
1124 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Save_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '050')
1125 if @IdNegocieriPK is null
1126 begin
1127 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Save_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '060')
1128
1129 insert into Negocieri (IdClient, IdPLClient, Numar, DataInceput, DataSfarsit, DataCreare, IdOperatorCreare, MomentOperare, IdOperator, [Status], OperatorAprobareRV, DataAprobareRV, OperatorAprobareASM,
1130 DataAprobareASM, OperatorRespinsASM, DataRespinsASM, OperatorAprobareMKT, DataAprobareMKT, OperatorRespinsMKT, DataRespinsMKT, OperatorAprobareRSM, DataAprobareRSM, OperatorRespinsRSM,
1131 DataRespinsRSM, OperatorAprobareNSM, DataAprobareNSM, OperatorRespinsNSM, DataRespinsNSM, Valoare, NrCrt, ProcDisc, BazaImpozitare, NotApplyAutomaticallyDisc, ToatePL, FacturareAutomata, DiscountApplicationType, IdTipNegociere,
1132 ModificaDisc, MotivRefuz, EditareDisc, Realizat, Observatie, IsTermenPlataDiferentiat, ObservatiiMKT, MotivMarjaInsuficienta, IdProducator, FeedBack, IdMasterNegocieri, IdParinteNegocieri, Versiune, UltimaVersiune, IsManualConfirmation)
1133 select IdClient, IdPLClient, Numar, DataInceput, DataSfarsit, DataCreare, IdOperatorCreare, MomentOperare, IdOperator, [Status], OperatorAprobareRV, DataAprobareRV, OperatorAprobareASM,
1134 DataAprobareASM, OperatorRespinsASM, DataRespinsASM, OperatorAprobareMKT, DataAprobareMKT, OperatorRespinsMKT, DataRespinsMKT, OperatorAprobareRSM, DataAprobareRSM, OperatorRespinsRSM,
1135 DataRespinsRSM, OperatorAprobareNSM, DataAprobareNSM, OperatorRespinsNSM, DataRespinsNSM, Valoare, NrCrt, ProcDisc, BazaImpozitare, NotApplyAutomaticallyDisc, isnull(ToatePL,0), isnull(FacturareAutomata,0), DiscountApplicationType, IdTipNegociere,
1136 ModificaDisc, MotivRefuz, EditareDisc, Realizat, Observatie, IsTermenPlataDiferentiat, ObservatiiMKT, MotivMarjaInsuficienta, IdProducator, FeedBack, IdMasterNegocieri, IdParinteNegocieri, Versiune, 1, IsManualConfirmation
1137 from @Negocieri
1138
1139 set @IdNegocieriPK = @@IDENTITY
1140
1141 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Save_Calls (Id, [Time], Step, Info) values (@CallId, sysdatetime(), '070', '@IdNegocieriPK - ' + convert(nvarchar, @IdNegocieriPK))
1142
1143 insert into NegocieriXDivizie (IdNegocieriPK, Divisions, OID, Marja, Margine, Valoare, BazaImpozitare, ValScontare)
1144 select @IdNegocieriPk, Divisions, OID, Marja, Margine, Valoare, BazaImpozitare, ValScontare
1145 from @NegocieriXDivizie
1146
1147 insert into MarjaNegocieri (IdNegocieriPK, GrossSales, DiscounturiPeLinie, DiscountPeComanda, DiscountFinanciarPeComanda, CAF, COGS, DiscounturiRecuperate, DiscounturiRecuperateReal, COGSNet, COGSNetReal,
1148 MargineComanda, MargineReala, MarjaComanda, MarjaReala, AvantajClient, BazaImpozitareRX, BazaImpozitareNonRX, CreditNotaPeComanda, ValScontare)
1149 select @IdNegocieriPK, GrossSales, DiscounturiPeLinie, DiscountPeComanda, DiscountFinanciarPeComanda, CAF, COGS, DiscounturiRecuperate, DiscounturiRecuperateReal, COGSNet, COGSNetReal,
1150 MargineComanda, MargineReala, MarjaComanda, MarjaReala, AvantajClient, BazaImpozitareRX, BazaImpozitareNonRX, CreditNotaPeComanda, ValScontare
1151 from @MarjaNegocieri
1152
1153 insert into TermenPlataDiferentiatNegocieri (IdTipDecontareProdus, IdNegocieriPK, IdTipDecontareComanda, TPProdus, TPComanda, ProcDisc, Valoare, CNFinanciara, DiferentaNrZile, NoOfDaysPerMonth, NoOfMonths,
1154 DefaultDiscountRatePerMonth, CalculatedDiscountRate, AplicaProcDiscImplicit, DefaultDiscountRatePerMonthIni)
1155 select IdTipDecontareProdus, @IdNegocieriPK, IdTipDecontareComanda, TPProdus, TPComanda, ProcDisc, Valoare, CNFinanciara, DiferentaNrZile, NoOfDaysPerMonth, NoOfMonths,
1156 DefaultDiscountRatePerMonth, CalculatedDiscountRate, AplicaProcDiscImplicit, DefaultDiscountRatePerMonthIni
1157 from @TermenPlataDiferentiatNegocieri
1158
1159 insert into ActiuniNegocieri (IdNegocieri, IdPersoana, DataOperare, Tip, StatusInitial, StatusFinal, Obs)
1160 select @IdNegocieriPK, IdPersoana, DataOperare, Tip, StatusInitial, StatusFinal, Obs
1161 from @ActiuniNegocieri
1162 end
1163 else
1164 begin
1165 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Save_Calls (Id, [Time], Step, Info) values (@CallId, sysdatetime(), '080', '@IdNegocieriPK - ' + convert(nvarchar, @IdNegocieriPK))
1166
1167 update t0
1168 set t0.IdClient = t1.IdClient, t0.IdPLClient = t1.IdPLClient, t0.Numar = t1.Numar,
1169 t0.DataInceput = t1.DataInceput, t0.DataSfarsit = t1.DataSfarsit, t0.DataCreare = t1.DataCreare,
1170 t0.IdOperatorCreare = t1.IdOperatorCreare, t0.MomentOperare = t1.MomentOperare, t0.IdOperator = t1.IdOperator,
1171 t0.[Status] = t1.[Status], t0.OperatorAprobareRV = t1.OperatorAprobareRV, t0.DataAprobareRV = t1.DataAprobareRV,
1172 t0.OperatorAprobareASM = t1.OperatorAprobareASM, t0.DataAprobareASM = t1.DataAprobareASM, t0.OperatorRespinsASM = t1.OperatorRespinsASM,
1173 t0.DataRespinsASM = t1.DataRespinsASM, t0.OperatorAprobareMKT = t1.OperatorAprobareMKT, t0.DataAprobareMKT = t1.DataAprobareMKT,
1174 t0.OperatorRespinsMKT = t1.OperatorRespinsMKT, t0.DataRespinsMKT = t1.DataRespinsMKT, t0.OperatorAprobareRSM = t1.OperatorAprobareRSM,
1175 t0.DataAprobareRSM = t1.DataAprobareRSM, t0.OperatorRespinsRSM = t1.OperatorRespinsRSM, t0.DataRespinsRSM = t1.DataRespinsRSM,
1176 t0.OperatorAprobareNSM = t1.OperatorAprobareNSM, t0.DataAprobareNSM = t1.DataAprobareNSM, t0.OperatorRespinsNSM = t1.OperatorRespinsNSM,
1177 t0.DataRespinsNSM = t1.DataRespinsNSM, t0.Valoare = t1.Valoare, t0.NrCrt = t1.NrCrt,
1178 t0.ProcDisc = t1.ProcDisc, t0.BazaImpozitare = t1.BazaImpozitare, t0.NotApplyAutomaticallyDisc = t1.NotApplyAutomaticallyDisc,
1179 t0.ToatePL = isnull(t1.ToatePL, 0), t0.FacturareAutomata = isnull(t1.FacturareAutomata, 0), t0.DiscountApplicationType = t1.DiscountApplicationType,
1180 t0.IdTipNegociere = t1.IdTipNegociere ,t0.ModificaDisc = t1.ModificaDisc,
1181 t0.MotivRefuz = t1.MotivRefuz, t0.EditareDisc = t1.EditareDisc, t0.Realizat = t1.Realizat,
1182 t0.Observatie = t1.Observatie, t0.IsTermenPlataDiferentiat = t1.IsTermenPlataDiferentiat, t0.ObservatiiMKT = t1.ObservatiiMKT,
1183 t0.MotivMarjaInsuficienta = t1.MotivMarjaInsuficienta, t0.IdProducator = t1.IdProducator/*, t0.FeedBack = t1.FeedBack*/,
1184 t0.IdMasterNegocieri = t1.IdMasterNegocieri, t0.IdParinteNegocieri = t1.IdParinteNegocieri, t0.Versiune = t1.Versiune, t0.IsManualConfirmation = t1.IsManualConfirmation
1185 from dbo.Negocieri t0 join @Negocieri t1 on t1.IdNegocieriPK = t0.IdNegocieriPK
1186
1187 delete t0
1188 from dbo.NegocieriXDivizie t0 left join @NegocieriXDivizie t1 on t0.OID = t1.OID
1189 where t0.IdNegocieriPK = @IdNegocieriPK and t1.OID is null
1190
1191 delete t0
1192 from dbo.TermenPlataDiferentiatNegocieri t0 left join @TermenPlataDiferentiatNegocieri t1 on t0.IdTermenPlataDiferentiatNegocieriPK = t1.IdTermenPlataDiferentiatNegocieriPK
1193 where t0.IdNegocieriPK = @IdNegocieriPK and t1.IdTermenPlataDiferentiatNegocieriPK is null
1194
1195 update t0
1196 set t0.GrossSales = t1.GrossSales, t0.DiscounturiPeLinie = t1.DiscounturiPeLinie, t0.DiscountPeComanda = t1.DiscountPeComanda,
1197 t0.DiscountFinanciarPeComanda = t1.DiscountFinanciarPeComanda, t0.CAF = t1.CAF, t0.COGS = t1.COGS,
1198 t0.DiscounturiRecuperate = t1.DiscounturiRecuperate, t0.DiscounturiRecuperateReal = t1.DiscounturiRecuperateReal, t0.COGSNet = t1.COGSNet,
1199 t0.COGSNetReal = t1.COGSNetReal, t0.MargineComanda = t1.MargineComanda, t0.MargineReala = t1.MargineReala,
1200 t0.MarjaComanda = t1.MarjaComanda, t0.MarjaReala = t1.MarjaReala, t0.AvantajClient = t1.AvantajClient,
1201 t0.BazaImpozitareRX = t1.BazaImpozitareRX, t0.BazaImpozitareNonRX = t1.BazaImpozitareNonRX, t0.CreditNotaPeComanda = t1.CreditNotaPeComanda,
1202 t0.ValScontare = t1.ValScontare
1203 from dbo.MarjaNegocieri t0 join @MarjaNegocieri t1 on t1.IdMarjaNegocieriPK = t0.IdMarjaNegocieriPK
1204
1205 update t0
1206 set t0.IdTipDecontareProdus = t1.IdTipDecontareProdus, t0.IdNegocieriPK = t1.IdNegocieriPK, t0.IdTipDecontareComanda = t1.IdTipDecontareComanda,
1207 t0.TPProdus = t1.TPProdus, t0.TPComanda = t1.TPComanda, t0.ProcDisc = t1.ProcDisc,
1208 t0.Valoare = t1.Valoare, t0.CNFinanciara = t1.CNFinanciara, t0.DiferentaNrZile = t1.DiferentaNrZile,
1209 t0.NoOfDaysPerMonth = t1.NoOfDaysPerMonth, t0.NoOfMonths = t1.NoOfMonths, t0.DefaultDiscountRatePerMonth = t1.DefaultDiscountRatePerMonth,
1210 t0.CalculatedDiscountRate = t1.CalculatedDiscountRate, t0.AplicaProcDiscImplicit = t1.AplicaProcDiscImplicit, t0.DefaultDiscountRatePerMonthIni = t1.DefaultDiscountRatePerMonthIni
1211 from dbo.TermenPlataDiferentiatNegocieri t0 join @TermenPlataDiferentiatNegocieri t1 on t1.IdTermenPlataDiferentiatNegocieriPK = t0.IdTermenPlataDiferentiatNegocieriPK
1212
1213 update t0
1214 set t0.Marja = t1.Marja, t0.Margine = t1.Margine, t0.Valoare = t1.Valoare,
1215 t0.BazaImpozitare = t1.BazaImpozitare, t0.ValScontare = t1.ValScontare
1216 from dbo.NegocieriXDivizie t0 join @NegocieriXDivizie t1 on t0.OID = t1.OID
1217
1218 insert into dbo.NegocieriXDivizie (IdNegocieriPK, Divisions, OID, Marja, Margine, Valoare, BazaImpozitare, ValScontare)
1219 select @IdNegocieriPk, t0.Divisions, t0.OID, t0.Marja, t0.Margine, t0.Valoare, t0.BazaImpozitare, t0.ValScontare
1220 from @NegocieriXDivizie t0 left join dbo.NegocieriXDivizie t1 on t1.OID = t0.OID
1221 where t1.OID is null
1222
1223 insert into dbo.TermenPlataDiferentiatNegocieri (IdTipDecontareProdus, IdNegocieriPK, IdTipDecontareComanda, TPProdus, TPComanda, ProcDisc, Valoare, CNFinanciara, DiferentaNrZile, NoOfDaysPerMonth, NoOfMonths,
1224 DefaultDiscountRatePerMonth, CalculatedDiscountRate, AplicaProcDiscImplicit, DefaultDiscountRatePerMonthIni)
1225 select t0.IdTipDecontareProdus, @IdNegocieriPK, t0.IdTipDecontareComanda, t0.TPProdus, t0.TPComanda, t0.ProcDisc, t0.Valoare, t0.CNFinanciara, t0.DiferentaNrZile, t0.NoOfDaysPerMonth, t0.NoOfMonths,
1226 t0.DefaultDiscountRatePerMonth, t0.CalculatedDiscountRate, t0.AplicaProcDiscImplicit, t0.DefaultDiscountRatePerMonthIni
1227 from @TermenPlataDiferentiatNegocieri t0 left join dbo.TermenPlataDiferentiatNegocieri t1 on t0.IdTermenPlataDiferentiatNegocieriPK = t1.IdTermenPlataDiferentiatNegocieriPK
1228 where t1.IdTermenPlataDiferentiatNegocieriPK is null
1229
1230 insert into dbo.ActiuniNegocieri (IdNegocieri, IdPersoana, DataOperare, Tip, StatusInitial, StatusFinal, Obs)
1231 select @IdNegocieriPK, t0.IdPersoana, t0.DataOperare, t0.Tip, t0.StatusInitial, t0.StatusFinal, t0.Obs
1232 from @ActiuniNegocieri t0 left join dbo.ActiuniNegocieri t1 on t1.IdActiuniNegocieri = t0.IdActiuniNegocieri
1233 where t1.IdActiuniNegocieri is null
1234 end
1235
1236 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Save_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '090')
1237
1238 delete t0
1239 from dbo.ElemNegocieriXBasicTargetDetail t0 join dbo.ElemNegocieri t1 on t1.IdElemNegocieriPK = t0.IdElemNegocieri
1240 left join @ElemNegocieriXBasicTargetDetail t2 on t2.ElemNegocieriXBasicTargetDetailId = t0.ElemNegocieriXBasicTargetDetailId
1241 where t2.IdElemNegocieri is null
1242 and t1.IdNegocieriPK = @IdNegocieriPK
1243
1244 delete t0
1245 from dbo.ManualConfirmationDetailXElemNegocieri t0 join dbo.ElemNegocieri t1 on t1.IdElemNegocieriPK = t0.IdElemNegocieri
1246 left join @ManualConfirmationDetailXElemNegocieri t2 on t2.ManualConfirmationDetailXElemNegocieriId = t0.ManualConfirmationDetailXElemNegocieriId
1247 where t2.IdElemNegocieri is null
1248 and t1.IdNegocieriPK = @IdNegocieriPK
1249
1250 delete t0
1251 from dbo.ElemNegocieri t0 left join @ElemNegocieri t1 on t1.IdElemNegocieriPK = t0.IdElemNegocieriPK
1252 where t0.IdNegocieriPK = @IdNegocieriPK
1253 and t1.IdElemNegocieriPK is null
1254
1255 if object_id('tempdb..#ElemNegocieriBind') is not null
1256 drop table #ElemNegocieriBind
1257
1258 create table dbo.#ElemNegocieriBind (
1259 IdInserted int null,
1260 Id int not null)
1261
1262 update t0 set t0.IdProdus = t1.IdProdus, t0.CantUV = t1.CantUV, t0.CantUVRamas = t1.CantUVRamas,
1263 t0.Pret = t1.Pret, t0.PretFurnizor = t1.PretFurnizor, t0.ValoarePretFurnizor = t1.ValoarePretFurnizor, t0.IdValuta = t1.IdValuta,
1264 t0.IdCursValutar = t1.IdCursValutar,
1265 t0.ProcDiscTotal = t1.ProcDiscTotal, t0.ProcDiscTotalRec = t1.ProcDiscTotalRec,
1266 t0.ProcDisc = t1.ProcDisc, t0.ProcTVA = t1.ProcTVA, t0.Valoare = t1.Valoare,
1267 t0.PretRidicata = t1.PretRidicata, t0.PretFaraDisc = t1.PretFaraDisc, t0.TermenPlata = t1.TermenPlata,
1268 t0.ProcMarja = t1.ProcMarja, t0.IdMarja = t1.IdMarja, t0.IsRabat = t1.IsRabat,
1269 t0.CantBax = t1.CantBax, t0.IdDivizie = t1.IdDivizie, t0.ProcMarjaCalculat = t1.ProcMarjaCalculat,
1270 t0.IdElemOfertaSpeciala = t1.IdElemOfertaSpeciala, t0.ProcDiscOfferDetail = t1.ProcDiscOfferDetail, t0.ProcDiscOfferHeader = t1.ProcDiscOfferHeader,
1271 t0.IsRabatHeaderOffer = t1.IsRabatHeaderOffer, t0.IsRabatDetailOffer = t1.IsRabatDetailOffer, t0.ProcDiscRecOfferDetail = t1.ProcDiscRecOfferDetail,
1272 t0.ProcDiscRecOfferHeader = t1.ProcDiscRecOfferHeader, t0.ProcMarjaMinim = t1.ProcMarjaMinim, t0.ProcDiscAutomat = t1.ProcDiscAutomat,
1273 t0.ProcMarjaReal = t1.ProcMarjaReal, t0.FinalSettlementTypeId = t1.FinalSettlementTypeId, t0.FinalPaymentTerm = t1.FinalPaymentTerm,
1274 t0.DiscountRatePT = t1.DiscountRatePT, t0.GenerateFinDocMasterPT = t1.GenerateFinDocMasterPT, t0.SuportatProducatorOfferDetail = t1.SuportatProducatorOfferDetail,
1275 t0.SuportatMDPOfferDetail = t1.SuportatMDPOfferDetail, t0.SuportatASMOfferDetail = t1.SuportatASMOfferDetail, t0.SuportatProducatorOfferHeader = t1.SuportatProducatorOfferHeader,
1276 t0.SuportatMDPOfferHeader = t1.SuportatMDPOfferHeader, t0.SuportatASMOfferHeader = t1.SuportatASMOfferHeader, t0.ProcDiscSuplimentar = t1.ProcDiscSuplimentar,
1277 t0.DiscSustinutOSProducator = t1.DiscSustinutOSProducator, t0.DiscSustinutOSMkt = t1.DiscSustinutOSMkt, t0.DiscSustinutOSAsm = t1.DiscSustinutOSAsm,
1278 t0.ValDiscSuplimentarProducator = t1.ValDiscSuplimentarProducator, t0.DiscSuplimentarProducator = t1.DiscSuplimentarProducator, t0.ValDiscSustinutCosMkt = t1.ValDiscSustinutCosMkt,
1279 t0.DiscSustinutCosMkt = t1.DiscSustinutCosMkt, t0.ValDiscSustinutCosAsm = t1.ValDiscSustinutCosAsm, t0.DiscSustinutCosAsm = t1.DiscSustinutCosAsm,
1280 t0.ValDiscSustinutCos = t1.ValDiscSustinutCos, t0.DiscSustinutCos = t1.DiscSustinutCos, t0.ValDiscSustinutReversare = t1.ValDiscSustinutReversare,
1281 t0.DiscSustinutReversare = t1.DiscSustinutReversare, t0.ValDiscSustinutNonRx = t1.ValDiscSustinutNonRx, t0.DiscSustinutNonRx = t1.DiscSustinutNonRx,
1282 t0.ValDisc = t1.ValDisc, t0.ValoareNeta = t1.ValoareNeta, t0.MargineNeta = t1.MargineNeta,
1283 t0.MarjaNeta = t1.MarjaNeta, t0.NrCrt = t1.NrCrt, t0.IdOfertaSpecialaTemp = t1.IdOfertaSpecialaTemp,
1284 t0.Inlocuit = t1.Inlocuit, t0.DataInlocuit = t1.DataInlocuit, t0.ElementInlocuit = t1.ElementInlocuit,
1285 t0.CantInlocuita = t1.CantInlocuita, t0.MargineReala = t1.MargineReala, t0.MargineRealaElInlocuit = t1.MargineRealaElInlocuit,
1286 t0.ElemNegocieriIstoric = t1.ElemNegocieriIstoric, t0.PretNet = t1.PretNet, t0.SuportatCosMKTOfferDetail = t1.SuportatCosMKTOfferDetail,
1287 t0.SuportatCosMKTOfferHeader = t1.SuportatCosMKTOfferHeader, t0.DiscSustinutOSCosMKT = t1.DiscSustinutOSCosMKT, t0.ValDiscSuplimentarProducatorRec = t1.ValDiscSuplimentarProducatorRec,
1288 t0.DiscSuplimentarProducatorRec = t1.DiscSuplimentarProducatorRec, t0.RecuperareRN = t1.RecuperareRN, t0.ExistaModificari = t1.ExistaModificari,
1289 t0.DiscOferteRebate = t1.DiscOferteRebate, t0.ValDiscOferteRebate = t1.ValDiscOferteRebate, t0.DiscSuplimentarSustinutMDP = t1.DiscSuplimentarSustinutMDP,
1290 t0.ValDiscSuplimentarSustinutMDP = t1.ValDiscSuplimentarSustinutMDP, t0.IdPrag = t1.IdPrag, t0.IdPragElem = t1.IdPragElem,
1291 t0.DiscSuplimentarProducatorRecOld = t1.DiscSuplimentarProducatorRecOld, t0.ProcDiscRecOfferDetailOld = t1.ProcDiscRecOfferDetailOld, t0.ProcDiscRecOfferHeaderOld = t1.ProcDiscRecOfferHeaderOld,
1292 t0.ValScontare = t1.ValScontare, t0.ProcentScontare = t1.ProcentScontare, t0.ValScontareOld = t1.ValScontareOld,
1293 t0.IdElemNegocieriPKParinte = t1.IdElemNegocieriPKParinte, t0.DiscSuplimentarSustinutMDPOld = t1.DiscSuplimentarSustinutMDPOld, t0.DiscSustinutCosMktOld = t1.DiscSustinutCosMktOld,
1294 t0.DiscSustinutASPRI = t1.DiscSustinutASPRI, t0.ValDiscSustinutASPRI = t1.ValDiscSustinutASPRI, t0.IdTipDecontare = t1.IdTipDecontare , t0.IsModificat = t1.IsModificat
1295 from dbo.ElemNegocieri t0 join @ElemNegocieri t1 on t0.IdElemNegocieriPK = t1.IdElemNegocieriPK
1296
1297 insert into dbo.ElemNegocieri (IdNegocieriPK, IdProdus, CantUV, CantUVRamas, Pret, PretFurnizor, ValoarePretFurnizor, IdValuta, IdCursValutar, ProcDiscTotal, ProcDiscTotalRec, ProcDisc, ProcTVA, Valoare, PretRidicata, PretFaraDisc, TermenPlata, ProcMarja, IdMarja,
1298 IsRabat, CantBax, IdDivizie, ProcMarjaCalculat, IdElemOfertaSpeciala, ProcDiscOfferDetail, ProcDiscOfferHeader, IsRabatHeaderOffer, IsRabatDetailOffer, ProcDiscRecOfferDetail, ProcDiscRecOfferHeader,
1299 ProcMarjaMinim, ProcDiscAutomat, ProcMarjaReal, FinalSettlementTypeId, FinalPaymentTerm, DiscountRatePT, GenerateFinDocMasterPT, SuportatProducatorOfferDetail, SuportatMDPOfferDetail,
1300 SuportatASMOfferDetail, SuportatProducatorOfferHeader, SuportatMDPOfferHeader, SuportatASMOfferHeader, ProcDiscSuplimentar, DiscSustinutOSProducator, DiscSustinutOSMkt, DiscSustinutOSAsm,
1301 ValDiscSuplimentarProducator, DiscSuplimentarProducator, ValDiscSustinutCosMkt, DiscSustinutCosMkt, ValDiscSustinutCosAsm, DiscSustinutCosAsm, ValDiscSustinutCos, DiscSustinutCos,
1302 ValDiscSustinutReversare, DiscSustinutReversare, ValDiscSustinutNonRx, DiscSustinutNonRx, ValDisc, ValoareNeta, MargineNeta, MarjaNeta, NrCrt, IdOfertaSpecialaTemp, Inlocuit, DataInlocuit,
1303 ElementInlocuit, CantInlocuita, MargineReala, MargineRealaElInlocuit, ElemNegocieriIstoric, PretNet, SuportatCosMKTOfferDetail, SuportatCosMKTOfferHeader, DiscSustinutOSCosMKT,
1304 ValDiscSuplimentarProducatorRec, DiscSuplimentarProducatorRec, RecuperareRN, ExistaModificari, DiscOferteRebate, ValDiscOferteRebate, DiscSuplimentarSustinutMDP, ValDiscSuplimentarSustinutMDP, IdPrag, IdPragElem,
1305 DiscSuplimentarProducatorRecOld, ProcDiscRecOfferDetailOld, ProcDiscRecOfferHeaderOld, ValScontare, ProcentScontare, ValScontareOld, IdElemNegocieriPKParinte, TempIdElemNegocieriPK,
1306 DiscSuplimentarSustinutMDPOld, DiscSustinutCosMktOld, DiscSustinutASPRI, ValDiscSustinutASPRI, IdTipDecontare, IsModificat)
1307 output inserted.IdElemNegocieriPK, inserted.TempIdElemNegocieriPK into #ElemNegocieriBind
1308 select @IdNegocieriPK, t1.IdProdus, t1.CantUV, t1.CantUVRamas, t1.Pret, t1.PretFurnizor, t1.ValoarePretFurnizor, t1.IdValuta, t1.IdCursValutar, t1.ProcDiscTotal, t1.ProcDiscTotalRec, t1.ProcDisc, t1.ProcTVA, t1.Valoare, t1.PretRidicata, t1.PretFaraDisc, t1.TermenPlata, t1.ProcMarja, t1.IdMarja,
1309 t1.IsRabat, t1.CantBax, t1.IdDivizie, t1.ProcMarjaCalculat, t1.IdElemOfertaSpeciala, t1.ProcDiscOfferDetail, t1.ProcDiscOfferHeader, t1.IsRabatHeaderOffer, t1.IsRabatDetailOffer, t1.ProcDiscRecOfferDetail, t1.ProcDiscRecOfferHeader,
1310 t1.ProcMarjaMinim, t1.ProcDiscAutomat, t1.ProcMarjaReal, t1.FinalSettlementTypeId, t1.FinalPaymentTerm, t1.DiscountRatePT, t1.GenerateFinDocMasterPT, t1.SuportatProducatorOfferDetail, t1.SuportatMDPOfferDetail,
1311 t1.SuportatASMOfferDetail, t1.SuportatProducatorOfferHeader, t1.SuportatMDPOfferHeader, t1.SuportatASMOfferHeader, t1.ProcDiscSuplimentar, t1.DiscSustinutOSProducator, t1.DiscSustinutOSMkt, t1.DiscSustinutOSAsm,
1312 t1.ValDiscSuplimentarProducator, t1.DiscSuplimentarProducator, t1.ValDiscSustinutCosMkt, t1.DiscSustinutCosMkt, t1.ValDiscSustinutCosAsm, t1.DiscSustinutCosAsm, t1.ValDiscSustinutCos, t1.DiscSustinutCos,
1313 t1.ValDiscSustinutReversare, t1.DiscSustinutReversare, t1.ValDiscSustinutNonRx, t1.DiscSustinutNonRx, t1.ValDisc, t1.ValoareNeta, t1.MargineNeta, t1.MarjaNeta, t1.NrCrt, t1.IdOfertaSpecialaTemp, t1.Inlocuit, t1.DataInlocuit,
1314 t1.ElementInlocuit, t1.CantInlocuita, t1.MargineReala, t1.MargineRealaElInlocuit, t1.ElemNegocieriIstoric, t1.PretNet, t1.SuportatCosMKTOfferDetail, t1.SuportatCosMKTOfferHeader, t1.DiscSustinutOSCosMKT,
1315 t1.ValDiscSuplimentarProducatorRec, t1.DiscSuplimentarProducatorRec, t1.RecuperareRN, t1.ExistaModificari, t1.DiscOferteRebate, t1.ValDiscOferteRebate, t1.DiscSuplimentarSustinutMDP, t1.ValDiscSuplimentarSustinutMDP, t1.IdPrag, t1.IdPragElem,
1316 t1.DiscSuplimentarProducatorRecOld, t1.ProcDiscRecOfferDetailOld, t1.ProcDiscRecOfferHeaderOld, t1.ValScontare, t1.ProcentScontare, t1.ValScontareOld, t1.IdElemNegocieriPKParinte, t1.IdElemNegocieriPK,
1317 t1.DiscSuplimentarSustinutMDPOld, t1.DiscSustinutCosMktOld, t1.DiscSustinutASPRI, t1.ValDiscSustinutASPRI, t1.IdTipDecontare , t1.IsModificat
1318 from dbo.ElemNegocieri t0 right join @ElemNegocieri t1 on t0.IdElemNegocieriPK = t1.IdElemNegocieriPK
1319 where t1.IdElemNegocieriPK <= 0
1320 and t0.IdElemNegocieriPK is null
1321
1322 insert into dbo.ElemNegocieriXBasicTargetDetail (IdElemNegocieri, BasicTargetDetailId, [Target], Discount, DataOperare, IdOperator, Realizat)
1323 select t1.IdInserted, t0.BasicTargetDetailId, t0.[Target], t0.Discount, t0.DataOperare, t0.IdOperator, t0.Realizat
1324 from @ElemNegocieriXBasicTargetDetail t0 inner join #ElemNegocieriBind t1 on t0.IdElemNegocieri = t1.Id
1325 where t0.ElemNegocieriXBasicTargetDetailId <=0
1326
1327 insert into ManualConfirmationDetailXElemNegocieri (SiteId, ManualConfirmationDetailId, IdElemNegocieri, Quantity)
1328 select t0.SiteId, t0.ManualConfirmationDetailId, t1.IdInserted, t0.Quantity
1329 from @ManualConfirmationDetailXElemNegocieri t0 join #ElemNegocieriBind t1 on t0.IdElemNegocieri = t1.Id
1330 where t0.ManualConfirmationDetailXElemNegocieriId <= 0
1331
1332 declare @IdMasterNegocieri int = (select t0.IdMasterNegocieri from @Negocieri t0)
1333
1334 if (@IdMasterNegocieri is not null)
1335 begin
1336 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Save_Calls (Id, [Time], Step, Info) values (@CallId, sysdatetime(), '100', '@IdMasterNegocieri - ' + convert(nvarchar, @IdMasterNegocieri))
1337 update t0
1338 set t0.UltimaVersiune = 0
1339 from dbo.Negocieri t0
1340 where t0.IdMasterNegocieri = @IdMasterNegocieri
1341 and t0.IdNegocieriPK != @IdNegocieriPk
1342 end
1343 end
1344
1345 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Save_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '110')
1346
1347 update t0
1348 set t0.Obs = t1.Obs
1349 from dbo.NegocieriFisier t0 join @NegocieriFisier t1 on t0.IdNegocieriFisierPK = t1.IdNegocieriFisierPK
1350 where t1.IdNegocieriFisierPK > 0
1351
1352 if @IdNegocieriPK is not null
1353 begin
1354
1355--cerinta 26.07.2019, sa se poata sterge fisierele din negociere
1356
1357 UPDATE T0 SET T0.IsDeleted = 1, t0.DeleteTime = @MomentOperareNegociere, t0.DeleteOperatorId = @OperatorOperareNegociere
1358 FROM NegocieriFisier T0
1359 LEFT JOIN @NegocieriFisier T1 ON T1.IdNegocieriFisierPK = T0.IdNegocieriFisierPK
1360
1361 WHERE T0.IdNegocieriFisierPK > 0 -- <0 sunt fisiere noi
1362 AND T1.IdNegocieriFisierPK IS NULL AND T0.IdNegocieriPK = @IdNegocieriPk
1363
1364 if @IsDebug = 1 insert into dbo._newSP_Negocieri_Save_Calls (Id, [Time], Step) values (@CallId, sysdatetime(), '120')
1365 if @IsDebug = 1 update t0 set t0.IdNegocieriPK = @IdNegocieriPk from dbo._newSP_Negocieri_Save_Calls t0 where t0.Id = @CallId
1366
1367 insert into dbo.FileData (Oid, size, [FileName], [Content], OptimisticLockField)
1368 select t0.FileOID, t0.FileSize, t0.[FileName], t0.FileBody, 0 OptimisticLockField
1369 from @NegocieriFisier t0
1370 where t0.IdNegocieriFisierPK < 0
1371
1372 insert into dbo.NegocieriFisier (IdNegocieriPK, FileOID, CreateTime, OperatorId, Obs)
1373 select @IdNegocieriPK, t0.FileOID, t0.CreateTime, t0.OperatorId, t0.Obs
1374 from @NegocieriFisier t0
1375 where t0.IdNegocieriFisierPK < 0
1376
1377 end
1378end try
1379begin catch
1380 declare @ErrorMessage nvarchar(4000),
1381 @ErrorSeverity int,
1382 @ErrorState int
1383 select @ErrorMessage = error_message(), @ErrorSeverity = error_severity(), @ErrorState = error_state()
1384 raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState)
1385end catch
1386
1387
1388
1389GO
1390
1391
1392
1393
1394-------------------------------------------------------------------------------------------------------------------------------------------------------
1395-------------------------------------------------------------------------------------------------------------------------------------------------------
1396-------------------------------------------------------------------------------------------------------------------------------------------------------
1397
1398
1399CREATE PROCEDURE [dbo].[newSP_Negocieri_EditareDisc]
1400 --@ElemNegocieri TypeElemNegocieri readonly,
1401 @Negocieri TypeNegocieri readonly
1402 --@OperatorOid uniqueidentifier
1403AS
1404BEGIN
1405
1406Update N set EditareDisc = 0 from Negocieri N
1407inner join @Negocieri U on U.IdNegocieriPK = N.IdNegocieriPK
1408
1409END
1410
1411
1412GO
1413
1414
1415-------------------------------------------------------------------------------------------------------------------------------------------------------
1416-------------------------------------------------------------------------------------------------------------------------------------------------------
1417-------------------------------------------------------------------------------------------------------------------------------------------------------
1418
1419
1420
1421CREATE PROCEDURE [dbo].[newSP_Negocieri_EditareProdus]
1422 --@ElemNegocieri TypeElemNegocieri readonly,
1423 @Negocieri TypeNegocieri readonly
1424 --@OperatorOid uniqueidentifier
1425
1426AS
1427BEGIN
1428
1429Update N set EditareDisc = 0 from Negocieri N
1430inner join @Negocieri U on U.IdNegocieriPK = N.IdNegocieriPK
1431
1432
1433END
1434
1435
1436GO
1437
1438
1439-------------------------------------------------------------------------------------------------------------------------------------------------------
1440-------------------------------------------------------------------------------------------------------------------------------------------------------
1441-------------------------------------------------------------------------------------------------------------------------------------------------------
1442
1443
1444CREATE procedure [dbo].[newSP_Negocieri_Anuleaza]
1445 @Negocieri TypeNegocieri readonly,
1446 @ElemNegocieri TypeElemNegocieri readonly,
1447 @OperatorOid uniqueidentifier
1448as
1449begin
1450declare @Messages varchar(max) = '',
1451 @Produse varchar(max) = '';
1452
1453 --Exista elemente facturate cu discount producator de recuperat
1454 if exists(select 1 from @ElemNegocieri t0 where t0.DiscSuplimentarProducatorRec > 0 and t0.CantUV <> t0.CantUVRamas)
1455 begin
1456 set @Produse = ''
1457 select @Produse = stuff((
1458 select ', ' + t1.Nume
1459 from @ElemNegocieri t0
1460 join dbo.Produs (nolock) t1 on t0.IdProdus = t1.IdProdus
1461 where t0.DiscSuplimentarProducatorRec > 0 and t0.CantUV <> t0.CantUVRamas
1462 for xml path('')), 1, 2, '')
1463 set @Messages = @Messages + 'Negocierea nu poate fi anulata, exista elemente facturate cu DiscSuplimentarProducatorDeRecuperat > 0 pentru produsele: ' + replace(@Produse, '%', '') + '!' + char(13) + char(10)
1464 end
1465
1466 if(len(@Messages) > 0)
1467 begin
1468 raiserror(@Messages, 18, 1)
1469 --RAISERROR ( { msg_str | @local_variable } { ,severity ,state } [ ,argument [ ,...n ] ] )
1470 --@local_variable trebuie sa fie char sau varchar - nu mai ?ncerca sa pui nvarchar :(
1471 end
1472
1473 declare @Status int =
1474 ( select t0.IdElemDic
1475 from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic
1476 where t0.Nume = 'Negociere Anulata'
1477 and t1.Nume = 'StatusNegocieri'),
1478 @IdNegocieriPK int = (select top 1 t0.IdNegocieriPk from @Negocieri t0)
1479
1480 update t0 set t0.[Status] = @Status from dbo.Negocieri t0 where t0.IdNegocieriPK = @IdNegocieriPK
1481end
1482
1483
1484GO
1485
1486
1487
1488
1489-------------------------------------------------------------------------------------------------------------------------------------------------------
1490-------------------------------------------------------------------------------------------------------------------------------------------------------
1491-------------------------------------------------------------------------------------------------------------------------------------------------------
1492
1493
1494CREATE procedure [dbo].[newSP_Negocieri_CereFeedBack]
1495 @Negocieri TypeNegocieri readonly,
1496 @ElemNegocieri TypeElemNegocieri readonly,
1497 @OperatorOid uniqueidentifier,
1498 @Obs varchar(450)
1499as
1500begin
1501 declare @IdNegocieri int,
1502 @IdOperator int,
1503 @TipCereFeedback int,
1504 @Status int,
1505 @Numar int,
1506 @NumeClient varchar(200),
1507 @Email varchar(200),
1508 @Subject varchar(250),
1509 @Body varchar(max),
1510 @ToRecipients varchar(250),
1511 @profile_name varchar(60) = 'Negocieri',
1512 @body_format varchar(60) = 'TEXT'
1513
1514 select @IdNegocieri = t0.IdNegocieriPk, @Status = t0.[Status], @Numar = t0.Numar, @NumeClient = t1.Nume, @Email = t2.Email
1515 from @Negocieri t0 join dbo.Firma (nolock) t1 on t1.IdFirma = t0.IdClient
1516 join dbo.CustomUser (nolock) t2 on t2.IdUtilizator = t0.OperatorAprobareRV and isnull(t2.IsActive,0) = 1
1517
1518 update t0 set t0.FeedBack = 1 from dbo.Negocieri t0 where t0.IdNegocieriPK = @IdNegocieri
1519
1520 select @IdOperator = IdUtilizator from dbo.CustomUser (nolock) t0 where t0.IsActive = 1 and t0.Oid = @OperatorOid
1521 set @TipCereFeedback =
1522 ( select t0.IdElemDic
1523 from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic
1524 where t0.nume = 'Feed-Back'
1525 and t1.Nume = 'TipActiuneNegocieri')
1526
1527 insert into dbo.ActiuniNegocieri (IdNegocieri, IdPersoana, DataOperare, Tip, StatusInitial, StatusFinal, Obs)
1528 values (@IdNegocieri, @IdOperator, getdate(), @TipCereFeedback, @Status, @Status, @Obs)
1529
1530 set @Subject = 'FeedBack Negociere: ' + cast(@Numar as varchar)
1531 set @Body = 'Client: ' + @NumeClient + CHAR(13) + @Obs
1532 set @ToRecipients = @Email
1533
1534 exec dbo.newSP_SendEmail @Subject, @Body, @ToRecipients, @profile_name, @body_format
1535end
1536
1537
1538GO
1539
1540
1541-------------------------------------------------------------------------------------------------------------------------------------------------------
1542-------------------------------------------------------------------------------------------------------------------------------------------------------
1543-------------------------------------------------------------------------------------------------------------------------------------------------------
1544
1545
1546
1547CREATE procedure [dbo].[newSP_Negocieri_Prelungeste]
1548 @Negocieri TypeNegocieri readonly,
1549 @ElemNegocieri TypeElemNegocieri readonly,
1550 @OperatorOid uniqueidentifier,
1551 @Data Date
1552as
1553begin
1554 if @Data is null or @Data < cast(Getdate()as Date)
1555 raiserror('START_CUSTOM_MESSAGE Campul "Data trebuie sa fie mai mare decat data curenta! END_CUSTOM_MESSAGE', 18, 1)
1556 else
1557 update t0 set t0.DataSfarsit = @Data from dbo.Negocieri t0 inner join @Negocieri t1 on t1.IdNegocieriPK = t0.IdNegocieriPK
1558end
1559
1560
1561
1562GO
1563
1564
1565
1566-------------------------------------------------------------------------------------------------------------------------------------------------------
1567-------------------------------------------------------------------------------------------------------------------------------------------------------
1568-------------------------------------------------------------------------------------------------------------------------------------------------------
1569
1570
1571
1572CREATE procedure [dbo].[newSP_Negocieri_RaspunsFeedBack]
1573 @Negocieri TypeNegocieri readonly,
1574 @ElemNegocieri TypeElemNegocieri readonly,
1575 @OperatorOid uniqueidentifier,
1576 @Obs varchar(450)
1577as
1578begin
1579 declare @IdNegocieri int,
1580 @IdOperator int = (select t0.IdUtilizator from dbo.CustomUser (nolock) t0 where t0.IsActive = 1 and t0.Oid = @OperatorOid),
1581 @RaspunsTipFeedback int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.Nume = 'Raspuns Feed-Back' and t1.Nume = 'TipActiuneNegocieri'),
1582 @CerereTipFeedback int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.Nume = 'Feed-Back' and t1.Nume = 'TipActiuneNegocieri'),
1583 @Status int,
1584 @Numar int,
1585 @NumeClient varchar(200),
1586 @Email varchar(200),
1587 @Subject varchar(250),
1588 @Body varchar(max),
1589 @ToRecipients varchar(250),
1590 @profile_name varchar(60) = 'Negocieri',
1591 @body_format varchar(60) = 'TEXT',
1592 @TipUserASM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'ASM' and t1.Nume = 'CustomUserType'),
1593 @TipUserNSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'NSM' and t1.Nume = 'CustomUserType'),
1594 @TipUserDest int,
1595 @NumeAsm varchar(201),
1596 @NumeProducator varchar(200),
1597 @GrossSales money = 0,
1598 @CAF money,
1599 @MargineReala money,
1600 @MarjaComanda money,
1601 @MarjaReala money,
1602 @WorkflowServerAddress varchar(100) = (select t0.Valoare from dbo.InitInfo (nolock) t0 where t0.Nume = 'WorkFlowServer'),
1603 @Key uniqueidentifier = newid(),
1604 @OperatorOidDest uniqueidentifier
1605
1606 select @IdNegocieri = t0.IdNegocieriPk, @Status = t0.[Status], @Numar = t0.Numar, @NumeClient = t1.Nume
1607 from @Negocieri t0 join dbo.Firma (nolock) t1 on t1.IdFirma = t0.IdClient
1608
1609 update t0 set t0.FeedBack = null from dbo.Negocieri t0 where t0.IdNegocieriPK = @IdNegocieri
1610
1611 select top 1 @ToRecipients = t1.Email, @TipUserDest = t1.UserType, @OperatorOidDest = t1.Oid
1612 from dbo.ActiuniNegocieri (nolock) t0 join dbo.CustomUser (nolock) t1 on t1.IdUtilizator = t0.IdPersoana
1613 where t0.IdNegocieri = @IdNegocieri
1614 and t0.Tip = @CerereTipFeedback
1615 order by t0.IdActiuniNegocieri desc
1616
1617 insert into dbo.ActiuniNegocieri (IdNegocieri, IdPersoana, DataOperare,
1618 Tip, StatusInitial, StatusFinal,
1619 Obs)
1620 values (@IdNegocieri, @IdOperator, getdate(),
1621 @RaspunsTipFeedback, @Status, @Status,
1622 @Obs)
1623
1624 if (@TipUserDest = @TipUserASM or @TipUserDest = @TipUserNSM)
1625 begin
1626 insert into dbo.NegocieriWorkflow ([Key], IdNegocieriPK, OperatorOid)
1627 select @Key, t0.IdNegocieriPK, @OperatorOidDest
1628 from @Negocieri t0
1629
1630 set @Subject = 'Aprobare negociere #' + cast(@Numar as varchar)
1631 set @body_format = 'HTML'
1632
1633 select @NumeAsm = isnull(ltrim(rtrim(ltrim(rtrim(isnull(t2.FirstName, ' '))) + ' ' + ltrim(rtrim(isnull(t2.LastName, ' '))))), t2.UserName)
1634 from dbo.CustomUserXChief t1 join dbo.CustomUser t2 on t1.Chief = t2.IdUtilizator
1635 where t1.CustomUsers = @OperatorOid
1636
1637 select top 1 @NumeProducator = t2.Nume
1638 from @ElemNegocieri t0 join dbo.Produs t1 on t0.IdProdus = t1.IdProdus
1639 join dbo.Firma t2 on t1.IdProducator = t2.IdFirma
1640
1641 select @GrossSales = sum(isnull(t0.GrossSales, 0)), @CAF = sum(isnull(t0.CAF, 0)), @MarjaComanda = sum(isnull(t0.MarjaComanda, 0)),
1642 @MarjaReala = sum(isnull(t0.MarjaReala, 0))
1643 from dbo.MarjaNegocieri (nolock) t0 join @Negocieri t1 on t0.IdNegocieriPK = t1.IdNegocieriPK
1644
1645 select @MargineReala = sum(isnull(t0.MargineReala, 0)) from @ElemNegocieri t0
1646
1647 insert into dbo._workflowArgs (PageTitle, ClientName, AsmName,
1648 ProducerName, GrossSales, CAF,
1649 RealDiff, OrderMargin, RealMargin,
1650 Obs, WorkflowServerAddress, [Key],
1651 ProductRows)
1652 values (@Subject, @NumeClient, @NumeAsm,
1653 @NumeProducator, convert(varchar(100), @GrossSales), convert(varchar(100), @CAF),
1654 convert(varchar(100), @MargineReala), convert(varchar(100), @MarjaComanda), convert(varchar(100), @MarjaReala),
1655 @Obs, @WorkflowServerAddress, @Key,
1656 --@Produse)
1657 null)
1658
1659 set @Body = dbo.newFN_GetEmailBodyForAsmAndNsm (@Subject, @NumeClient, @NumeAsm,
1660 @NumeProducator, convert(varchar(100), @GrossSales), convert(varchar(100), @CAF),
1661 convert(varchar(100), @MargineReala), convert(varchar(100), @MarjaComanda), convert(varchar(100), @MarjaReala),
1662 @Obs, @WorkflowServerAddress, convert(nvarchar(36), @Key))
1663 exec dbo.newSP_SendEmail @Subject, @Body, @ToRecipients, @profile_name, @body_format
1664 end
1665 else
1666 begin
1667 set @Subject = 'Raspuns FeedBack Negociere: ' + cast(@Numar as varchar)
1668 set @Body = 'Client: ' + @NumeClient + CHAR(13) + @Obs
1669 exec dbo.newSP_SendEmail @Subject, @Body, @ToRecipients, @profile_name, @body_format
1670 end
1671end
1672
1673
1674GO
1675
1676
1677
1678
1679-------------------------------------------------------------------------------------------------------------------------------------------------------
1680-------------------------------------------------------------------------------------------------------------------------------------------------------
1681-------------------------------------------------------------------------------------------------------------------------------------------------------
1682
1683
1684
1685CREATE procedure [dbo].[newSP_Negocieri_Validari]
1686 @Negocieri TypeNegocieri readonly,
1687 @ElemNegocieri TypeElemNegocieri readonly,
1688 @MarjaNegocieri TypeMarjaNegocieri readonly,
1689 @TermenPlataDiferentiatNegocieri TypeTermenPlataDiferentiatNegocieri readonly,
1690 @ActiuniNegocieri TypeActiuniNegocieri readonly,
1691 @IsValid bit output,
1692 @CheckAdditionalDiscountPercentage bit = 0
1693as
1694begin
1695 declare @Messages varchar(max) = '',
1696 @Produse varchar(max) = '',
1697 @PragMarja money = (select cast(t0.Valoare as money) from dbo.InitInfo (nolock) t0 where t0.Nume = 'PragMotivMarjaRealaNegocieri'),
1698 @MarjaReala money = (select top 1 t0.MarjaReala from @MarjaNegocieri t0),
1699 @TipUserRV int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'RV' and t1.Nume = 'CustomUserType'),
1700 @TipUserRVT int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'RVT' and t1.Nume = 'CustomUserType'),
1701 @TipUserRVTR int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'RVTR' and t1.Nume = 'CustomUserType'),
1702 @TipUserASM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'ASM' and t1.Nume = 'CustomUserType'),
1703 @TipUserRSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'RSM' and t1.Nume = 'CustomUserType'),
1704 @TipUserNSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'NSM' and t1.Nume = 'CustomUserType'),
1705 @TipOperator int,
1706 @TempUserOid uniqueidentifier,
1707 @ChiefUserId int
1708
1709 set @IsValid = 1
1710
1711 --Nu ati completat Motiv Marja Insuficienta
1712 if exists(select 1 from @Negocieri t0 where t0.MotivMarjaInsuficienta is null and @PragMarja > @MarjaReala)
1713 set @Messages = @Messages + 'Nu ati completat Motiv Marja Insuficienta!' + char(13) + char(10)
1714
1715 --Procentul de discount trebuie sa fie intre 0 si 100
1716 if exists(select 1 from @Negocieri t0 where t0.ProcDisc > 100 or t0.ProcDisc < 0)
1717 set @Messages = @Messages + 'Procentul de discount trebuie sa fie intre 0 si 100!' + char(13) + char(10)
1718
1719 --Negocierea poate sa inceapa cel mai devreme azi
1720 if exists(select 1 from @Negocieri t0 left join dbo.Negocieri (nolock) t1 on t1.IdMasterNegocieri = t0.IdMasterNegocieri where t0.DataInceput < cast(getdate()-1 as Date) and t1.IdNegocieriPK is null)
1721 set @Messages = @Messages + 'Negocierea poate sa inceapa cel mai devreme cu o zi in urma!' + char(13) + char(10)
1722
1723 --Data Sfarsit trebuie sa fie mai mare sau egala cu Data Inceput
1724 if exists(select 1 from @Negocieri t0 where t0.DataInceput > t0.DataSfarsit)
1725 set @Messages = @Messages + 'Data Sfarsit trebuie sa fie mai mare sau egala cu Data Inceput!' + char(13) + char(10)
1726
1727 --Procentul de discount total trebuie sa fie intre 0 si 100
1728 if exists(select 1 from @ElemNegocieri t0 where t0.ProcDiscTotal > 100 or t0.ProcDiscTotal < 0)
1729 begin
1730 set @Produse = ''
1731 select @Produse = stuff((
1732 select ', ' + t1.Nume
1733 from @ElemNegocieri t0 join dbo.Produs (nolock) t1 on t0.IdProdus = t1.IdProdus
1734 where t0.ProcDiscTotal > 100 or t0.ProcDiscTotal < 0
1735 for xml path('')), 1, 2, '')
1736 set @Messages = @Messages + 'Procentul de discount total trebuie sa fie intre 0 si 100 pentru produsele: ' + replace(@Produse, '%', '') + '!' + char(13) + char(10)
1737 end
1738
1739 --Procentul de discount suplimentar trebuie sa fie intre 0 si 100
1740 if @CheckAdditionalDiscountPercentage = 1 and exists (select 1 from @ElemNegocieri t0 where t0.ProcDiscSuplimentar > 100 or t0.ProcDiscSuplimentar < 0)
1741 begin
1742 set @Produse = ''
1743 select @Produse = stuff((
1744 select ', ' + t1.Nume
1745 from @ElemNegocieri t0 join dbo.Produs (nolock) t1 on t0.IdProdus = t1.IdProdus
1746 where t0.ProcDiscSuplimentar > 100 or t0.ProcDiscSuplimentar < 0
1747 for xml path('')), 1, 2, '')
1748 set @Messages = @Messages + 'Procentul de discount suplimentar trebuie sa fie ?ntre 0 si 100 pentru produsele: ' + replace(@Produse, '%', '') + '!' + char(13) + char(10)
1749 end
1750
1751 --Procentul de discount total trebuie sa fie <> 100 daca DiscOferteRebate > 0
1752 if exists (select 1 from @ElemNegocieri t0 where t0.DiscOferteRebate > 0 and t0.ProcDiscTotal = 100)
1753 begin
1754 set @Produse = ''
1755 select @Produse = stuff((
1756 select ', ' + t1.Nume
1757 from @ElemNegocieri t0 join dbo.Produs (nolock) t1 on t0.IdProdus = t1.IdProdus
1758 where t0.DiscOferteRebate > 0 or t0.ProcDiscTotal = 100
1759 for xml path('')), 1, 2, '')
1760 set @Messages = @Messages + 'Procentul de discount total trebuie sa fie <> 100 daca DiscOferteRebate > 0 pentru produsele: ' + replace(@Produse, '%', '') + '!' + char(13) + char(10)
1761 end
1762
1763 --Procentul de discount suplimentar rec trebuie sa fie intre 0 si 100-ProcMarja
1764 if @CheckAdditionalDiscountPercentage = 1 and exists (select 1 from @ElemNegocieri t0 where t0.DiscSuplimentarProducatorRec > 100.0-ProcMarja or t0.DiscSuplimentarProducatorRec < 0)
1765 begin
1766 set @Produse = ''
1767 select @Produse = stuff((
1768 select ', ' + t1.Nume
1769 from @ElemNegocieri t0 join dbo.Produs (nolock) t1 on t0.IdProdus = t1.IdProdus
1770 where t0.DiscSuplimentarProducatorRec > 100.0 - t0.ProcMarja or t0.DiscSuplimentarProducatorRec < 0
1771 for xml path('')), 1, 2, '')
1772 set @Messages = @Messages + 'Procentul de discount suplimentar rec trebuie sa fie intre 0 si 100-ProcMarja pentru produsele: ' + replace(@Produse, '%', '') + '!' + char(13) + char(10)
1773 end
1774
1775 --verificare ierarhie
1776 select @TempUserOid = t1.Oid, @TipOperator = t1.UserType from @Negocieri t0 join dbo.CustomUser (nolock) t1 on t0.IdOperator = t1.IdUtilizator
1777
1778 if @TipOperator = @TipUserRV or @TipOperator = @TipUserRVT or @TipOperator = @TipUserRVTR
1779 begin
1780 select @ChiefUserId = t0.Chief from dbo.CustomUserXChief (nolock) t0 where t0.CustomUsers = @TempUserOid
1781 if @ChiefUserId is null
1782 set @Messages = @Messages + 'Nu am gasit in ierarhie seful pentru operatorul de tip RV/RVT/RVTR!' + char(13) + char(10)
1783 else
1784 select @TempUserOid = t0.Oid, @TipOperator = t0.UserType from dbo.CustomUser (nolock) t0 where t0.IdUtilizator = @ChiefUserId
1785 end
1786
1787 if @TipOperator = @TipUserASM
1788 begin
1789 select @ChiefUserId = t0.Chief from dbo.CustomUserXChief (nolock) t0 where t0.CustomUsers = @TempUserOid
1790 if @ChiefUserId is null
1791 set @Messages = @Messages + 'Nu am gasit in ierarhie seful pentru operatorul de tip ASM!' + char(13) + char(10)
1792 else
1793 select @TempUserOid = t0.Oid, @TipOperator = t0.UserType from dbo.CustomUser (nolock) t0 where t0.IdUtilizator = @ChiefUserId
1794 end
1795
1796 if @TipOperator = @TipUserRSM
1797 begin
1798 select @ChiefUserId = t0.Chief from dbo.CustomUserXChief (nolock) t0 where t0.CustomUsers = @TempUserOid
1799 if @ChiefUserId is null
1800 set @Messages = @Messages + 'Nu am gasit in ierarhie seful pentru operatorul de tip RSM!' + char(13) + char(10)
1801 else
1802 select @TempUserOid = t0.Oid, @TipOperator = t0.UserType from dbo.CustomUser (nolock) t0 where t0.IdUtilizator = @ChiefUserId
1803 end
1804
1805 if @TipOperator = @TipUserNSM
1806 begin
1807 select @ChiefUserId = t0.Chief from dbo.CustomUserXChief (nolock) t0 where t0.CustomUsers = @TempUserOid
1808 if @ChiefUserId is null
1809 set @Messages = @Messages + 'Nu am gasit in ierarhie seful pentru operatorul de tip NSM!' + char(13) + char(10)
1810 else
1811 select @TempUserOid = t0.Oid, @TipOperator = t0.UserType from dbo.CustomUser (nolock) t0 where t0.IdUtilizator = @ChiefUserId
1812 end
1813
1814 if(len(@Messages) > 0)
1815 begin
1816 set @IsValid = 0
1817 raiserror(@Messages, 18, 1)
1818 --RAISERROR ( { msg_str | @local_variable } { ,severity ,state } [ ,argument [ ,...n ] ] )
1819 --@local_variable trebuie sa fie char sau varchar - nu mai ?ncerca sa pui nvarchar :(
1820 end
1821end
1822
1823
1824GO
1825
1826
1827-------------------------------------------------------------------------------------------------------------------------------------------------------
1828-------------------------------------------------------------------------------------------------------------------------------------------------------
1829-------------------------------------------------------------------------------------------------------------------------------------------------------
1830
1831CREATE procedure [dbo].[newSP_Negocieri_Workflow_Respinge]
1832 @Key uniqueidentifier,
1833 @Observatii varchar(450)
1834as
1835begin try
1836 if not exists(select 1 from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key)
1837 begin
1838 raiserror(N'START_CUSTOM_MESSAGE Nu am gasit detaliile workflow-ului. END_CUSTOM_MESSAGE', 18, 1)
1839 return
1840 end
1841
1842 declare @IsProcessed bit = (select t0.IsProcessed from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key)
1843
1844 if @IsProcessed = 1
1845 begin
1846 raiserror(N'START_CUSTOM_MESSAGE Acest workflow a fost deja procesat. END_CUSTOM_MESSAGE', 18, 1)
1847 return
1848 end
1849
1850 declare @ASM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'ASM' and t1.Nume = 'CustomUserType'),
1851 @NSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'NSM' and t1.Nume = 'CustomUserType'),
1852 @AprobareASM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare ASM'),
1853 @AprobareNSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare NSM'),
1854 @TipOperator int = (select t0.UserType from dbo.CustomUser (nolock) t0 join dbo.NegocieriWorkflow t1 on t0.Oid = t1.OperatorOid where t1.[Key] = @Key),
1855 @OldStatus int = (select t1.[Status] from dbo.NegocieriWorkflow t0 join dbo.Negocieri (nolock) t1 on t0.IdNegocieriPK = t1.IdNegocieriPK where t0.[Key] = @Key)
1856
1857 if @TipOperator <> @ASM and @TipOperator <> @NSM
1858 begin
1859 raiserror(N'START_CUSTOM_MESSAGE Tip-ul userului trebuie sa fie ASM sau NSM. END_CUSTOM_MESSAGE', 18, 1)
1860 return
1861 end
1862
1863 if @TipOperator = @ASM and @OldStatus <> @AprobareASM
1864 begin
1865 raiserror(N'START_CUSTOM_MESSAGE Tip-ul userului este ASM dar negocierea nu mai e ?n status Aprobare ASM. END_CUSTOM_MESSAGE', 18, 1)
1866 return
1867 end
1868
1869 if @TipOperator = @NSM and @OldStatus <> @AprobareNSM
1870 begin
1871 raiserror(N'START_CUSTOM_MESSAGE Tip-ul userului este NSM dar negocierea nu mai e ?n status Aprobare NSM. END_CUSTOM_MESSAGE', 18, 1)
1872 return
1873 end
1874
1875 declare @IdNegocieriPK int = (select t0.IdNegocieriPK from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key),
1876 @OperatorOid uniqueidentifier = (select t0.OperatorOid from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key),
1877 @Negocieri TypeNegocieri,
1878 @ElemNegocieri TypeElemNegocieri,
1879 @NegocieriXDivizie TypeNegocieriXDivizie,
1880 @MarjaNegocieri TypeMarjaNegocieri,
1881 @TermenPlataDiferentiatNegocieri TypeTermenPlataDiferentiatNegocieri,
1882 @ActiuniNegocieri TypeActiuniNegocieri,
1883 @ElemNegocieriXBasicTargetDetail TypeElemNegocieriXBasicTargetDetail,
1884 @ManualConfirmationDetailXElemNegocieri TypeManualConfirmationDetailXElemNegocieri,
1885 @NegocieriFisier TypeNegocieriFisier
1886
1887 insert into @Negocieri (IdNegocieriPK, IdClient, IdPLClient, Numar,
1888 DataInceput, DataSfarsit, DataCreare, IdOperatorCreare,
1889 MomentOperare, IdOperator, [Status], OperatorAprobareRV, DataAprobareRV,
1890 OperatorAprobareASM, DataAprobareASM, OperatorRespinsASM, DataRespinsASM,
1891 OperatorAprobareMKT, DataAprobareMKT, OperatorRespinsMKT, DataRespinsMKT,
1892 OperatorAprobareRSM, DataAprobareRSM, OperatorRespinsRSM, DataRespinsRSM,
1893 OperatorAprobareNSM, DataAprobareNSM, OperatorRespinsNSM, DataRespinsNSM,
1894 Valoare, NrCrt, ProcDisc, BazaImpozitare,
1895 NotApplyAutomaticallyDisc, ToatePL, FacturareAutomata, DiscountApplicationType, IdTipNegociere, ModificaDisc,
1896 MotivRefuz, EditareDisc, Realizat, Observatie,
1897 IsTermenPlataDiferentiat, ObservatiiMKT, MotivMarjaInsuficienta, IdProducator,
1898 FeedBack, IdMasterNegocieri, IdParinteNegocieri, Versiune, UltimaVersiune, IsManualConfirmation)
1899 select t0.IdNegocieriPK, t0.IdClient, t0.IdPLClient, t0.Numar,
1900 t0.DataInceput, t0.DataSfarsit, t0.DataCreare, t0.IdOperatorCreare,
1901 t0.MomentOperare, t0.IdOperator, t0.[Status], t0.OperatorAprobareRV, t0.DataAprobareRV,
1902 t0.OperatorAprobareASM, t0.DataAprobareASM, t0.OperatorRespinsASM, t0.DataRespinsASM,
1903 t0.OperatorAprobareMKT, t0.DataAprobareMKT, t0.OperatorRespinsMKT, t0.DataRespinsMKT,
1904 t0.OperatorAprobareRSM, t0.DataAprobareRSM, t0.OperatorRespinsRSM, t0.DataRespinsRSM,
1905 t0.OperatorAprobareNSM, t0.DataAprobareNSM, t0.OperatorRespinsNSM, t0.DataRespinsNSM,
1906 t0.Valoare, t0.NrCrt, t0.ProcDisc, t0.BazaImpozitare,
1907 t0.NotApplyAutomaticallyDisc, t0.ToatePL, t0.FacturareAutomata, t0.DiscountApplicationType, t0.IdTipNegociere, t0.ModificaDisc,
1908 t0.MotivRefuz, t0.EditareDisc, t0.Realizat, t0.Observatie,
1909 t0.IsTermenPlataDiferentiat, t0.ObservatiiMKT, t0.MotivMarjaInsuficienta, t0.IdProducator,
1910 t0.FeedBack, t0.IdMasterNegocieri, t0.IdParinteNegocieri, t0.Versiune, t0.UltimaVersiune, t0.IsManualConfirmation
1911 from dbo.Negocieri t0
1912 where t0.IdNegocieriPK = @IdNegocieriPK
1913
1914 insert into @ElemNegocieri (IdElemNegocieriPK, IdNegocieriPK, IdProdus, CantUV,
1915 CantUVRamas, Pret, ProcDiscTotal, ProcDiscTotalRec,
1916 ProcDisc, ProcTVA, Valoare, PretRidicata,
1917 PretFaraDisc, TermenPlata, ProcMarja, IdMarja,
1918 IsRabat, CantBax, IdDivizie, ProcMarjaCalculat,
1919 IdElemOfertaSpeciala, ProcDiscOfferDetail, ProcDiscOfferHeader, IsRabatHeaderOffer,
1920 IsRabatDetailOffer, ProcDiscRecOfferDetail, ProcDiscRecOfferHeader, ProcMarjaMinim,
1921 ProcDiscAutomat, ProcMarjaReal, FinalSettlementTypeId, FinalPaymentTerm,
1922 DiscountRatePT, GenerateFinDocMasterPT, SuportatProducatorOfferDetail, SuportatMDPOfferDetail,
1923 SuportatASMOfferDetail, SuportatProducatorOfferHeader, SuportatMDPOfferHeader, SuportatASMOfferHeader,
1924 ProcDiscSuplimentar, DiscSustinutOSProducator, DiscSustinutOSMkt, DiscSustinutOSAsm,
1925 ValDiscSuplimentarProducator, DiscSuplimentarProducator, ValDiscSustinutCosMkt, DiscSustinutCosMkt,
1926 ValDiscSustinutCosAsm, DiscSustinutCosAsm, ValDiscSustinutCos, DiscSustinutCos,
1927 ValDiscSustinutReversare, DiscSustinutReversare, ValDiscSustinutNonRx, DiscSustinutNonRx,
1928 ValDisc, ValoareNeta, MargineNeta, MarjaNeta,
1929 NrCrt, IdOfertaSpecialaTemp, Inlocuit, DataInlocuit,
1930 ElementInlocuit, CantInlocuita, MargineReala, MargineRealaElInlocuit,
1931 ElemNegocieriIstoric, PretNet, SuportatCosMKTOfferDetail, SuportatCosMKTOfferHeader,
1932 DiscSustinutOSCosMKT, ValDiscSuplimentarProducatorRec, DiscSuplimentarProducatorRec, RecuperareRN,
1933 ExistaModificari, DiscOferteRebate, ValDiscOferteRebate, DiscSuplimentarSustinutMDP,
1934 ValDiscSuplimentarSustinutMDP, IdPrag, IdPragElem, ProcDiscRecOfferDetailOld,
1935 ProcDiscRecOfferHeaderOld, ValScontare, ProcentScontare, ValScontareOld,
1936 IdElemNegocieriPKParinte, DiscSuplimentarProducatorRecOld, DiscSuplimentarSustinutMDPOld, DiscSustinutCosMktOld,
1937 DiscSustinutASPRI, ValDiscSustinutASPRI, IdTipDecontare, IsModificat)
1938 select t0.IdElemNegocieriPK, t0.IdNegocieriPK, t0.IdProdus, t0.CantUV,
1939 t0.CantUVRamas, t0.Pret, t0.ProcDiscTotal, t0.ProcDiscTotalRec,
1940 t0.ProcDisc, t0.ProcTVA, t0.Valoare, t0.PretRidicata,
1941 t0.PretFaraDisc, t0.TermenPlata, t0.ProcMarja, t0.IdMarja,
1942 t0.IsRabat, t0.CantBax, t0.IdDivizie, t0.ProcMarjaCalculat,
1943 t0.IdElemOfertaSpeciala, t0.ProcDiscOfferDetail, t0.ProcDiscOfferHeader, t0.IsRabatHeaderOffer,
1944 t0.IsRabatDetailOffer, t0.ProcDiscRecOfferDetail, t0.ProcDiscRecOfferHeader, t0.ProcMarjaMinim,
1945 t0.ProcDiscAutomat, t0.ProcMarjaReal, t0.FinalSettlementTypeId, t0.FinalPaymentTerm,
1946 t0.DiscountRatePT, t0.GenerateFinDocMasterPT, t0.SuportatProducatorOfferDetail, t0.SuportatMDPOfferDetail,
1947 t0.SuportatASMOfferDetail, t0.SuportatProducatorOfferHeader, t0.SuportatMDPOfferHeader, t0.SuportatASMOfferHeader,
1948 t0.ProcDiscSuplimentar, t0.DiscSustinutOSProducator, t0.DiscSustinutOSMkt, t0.DiscSustinutOSAsm,
1949 t0.ValDiscSuplimentarProducator, t0.DiscSuplimentarProducator, t0.ValDiscSustinutCosMkt, t0.DiscSustinutCosMkt,
1950 t0.ValDiscSustinutCosAsm, t0.DiscSustinutCosAsm, t0.ValDiscSustinutCos, t0.DiscSustinutCos,
1951 t0.ValDiscSustinutReversare, t0.DiscSustinutReversare, t0.ValDiscSustinutNonRx, t0.DiscSustinutNonRx,
1952 t0.ValDisc, t0.ValoareNeta, t0.MargineNeta, t0.MarjaNeta,
1953 t0.NrCrt, t0.IdOfertaSpecialaTemp, t0.Inlocuit, t0.DataInlocuit,
1954 t0.ElementInlocuit, t0.CantInlocuita, t0.MargineReala, t0.MargineRealaElInlocuit,
1955 t0.ElemNegocieriIstoric, t0.PretNet, t0.SuportatCosMKTOfferDetail, t0.SuportatCosMKTOfferHeader,
1956 t0.DiscSustinutOSCosMKT, t0.ValDiscSuplimentarProducatorRec, t0.DiscSuplimentarProducatorRec, t0.RecuperareRN,
1957 t0.ExistaModificari, t0.DiscOferteRebate, t0.ValDiscOferteRebate, t0.DiscSuplimentarSustinutMDP,
1958 t0.ValDiscSuplimentarSustinutMDP, t0.IdPrag, t0.IdPragElem, t0.ProcDiscRecOfferDetailOld,
1959 t0.ProcDiscRecOfferHeaderOld, t0.ValScontare, t0.ProcentScontare, t0.ValScontareOld,
1960 t0.IdElemNegocieriPKParinte, t0.DiscSuplimentarProducatorRecOld, t0.DiscSuplimentarSustinutMDPOld, t0.DiscSustinutCosMktOld,
1961 t0.DiscSustinutASPRI, t0.ValDiscSustinutASPRI, t0.IdTipDecontare, t0.IsModificat
1962 from dbo.ElemNegocieri t0
1963 where t0.IdNegocieriPK = @IdNegocieriPK
1964
1965 insert into @NegocieriXDivizie (IdNegocieriPK, Divisions, OID, Marja, Margine, Valoare, BazaImpozitare, ValScontare)
1966 select t0.IdNegocieriPK, t0.Divisions, t0.OID, t0.Marja, t0.Margine, t0.Valoare, t0.BazaImpozitare, t0.ValScontare
1967 from dbo.NegocieriXDivizie t0
1968 where t0.IdNegocieriPK = @IdNegocieriPK
1969
1970 insert into @MarjaNegocieri (IdMarjaNegocieriPK, IdNegocieriPK, GrossSales, DiscounturiPeLinie,
1971 DiscountPeComanda, DiscountFinanciarPeComanda, CAF, COGS,
1972 DiscounturiRecuperate, DiscounturiRecuperateReal, COGSNet, COGSNetReal,
1973 MargineComanda, MargineReala, MarjaComanda, MarjaReala,
1974 AvantajClient, BazaImpozitareRX, BazaImpozitareNonRX, CreditNotaPeComanda, ValScontare)
1975 select t0.IdMarjaNegocieriPK, t0.IdNegocieriPK, t0.GrossSales, t0.DiscounturiPeLinie,
1976 t0.DiscountPeComanda, t0.DiscountFinanciarPeComanda, t0.CAF, t0.COGS,
1977 t0.DiscounturiRecuperate, t0.DiscounturiRecuperateReal, t0.COGSNet, t0.COGSNetReal,
1978 t0.MargineComanda, t0.MargineReala, t0.MarjaComanda, t0.MarjaReala,
1979 t0.AvantajClient, t0.BazaImpozitareRX, t0.BazaImpozitareNonRX, t0.CreditNotaPeComanda, t0.ValScontare
1980 from dbo.MarjaNegocieri t0
1981 where t0.IdNegocieriPK = @IdNegocieriPK
1982
1983 insert into @TermenPlataDiferentiatNegocieri (IdTermenPlataDiferentiatNegocieriPK, IdTipDecontareProdus, IdNegocieriPK,
1984 IdTipDecontareComanda, TPProdus, TPComanda, ProcDisc,
1985 Valoare, CNFinanciara, DiferentaNrZile, NoOfDaysPerMonth,
1986 NoOfMonths, DefaultDiscountRatePerMonth, CalculatedDiscountRate, AplicaProcDiscImplicit,
1987 DefaultDiscountRatePerMonthIni)
1988 select t0.IdTermenPlataDiferentiatNegocieriPK, t0.IdTipDecontareProdus, t0.IdNegocieriPK,
1989 t0.IdTipDecontareComanda, t0.TPProdus, t0.TPComanda, t0.ProcDisc,
1990 t0.Valoare, t0.CNFinanciara, t0.DiferentaNrZile, t0.NoOfDaysPerMonth,
1991 t0.NoOfMonths, t0.DefaultDiscountRatePerMonth, t0.CalculatedDiscountRate, t0.AplicaProcDiscImplicit,
1992 t0.DefaultDiscountRatePerMonthIni
1993 from dbo.TermenPlataDiferentiatNegocieri t0
1994 where t0.IdNegocieriPK = @IdNegocieriPK
1995
1996 insert into @ActiuniNegocieri (IdActiuniNegocieri, IdNegocieri, IdPersoana, DataOperare,
1997 Tip, StatusInitial, StatusFinal, Obs)
1998 select t0.IdActiuniNegocieri, t0.IdNegocieri, t0.IdPersoana, t0.DataOperare,
1999 t0.Tip, t0.StatusInitial, t0.StatusFinal, t0.Obs
2000 from dbo.ActiuniNegocieri t0
2001 where t0.IdNegocieri = @IdNegocieriPK
2002
2003 insert into @ElemNegocieriXBasicTargetDetail (ElemNegocieriXBasicTargetDetailId, IdElemNegocieri, BasicTargetDetailId,
2004 [Target], Discount, DataOperare, IdOperator)
2005 select t0.ElemNegocieriXBasicTargetDetailId, t0.IdElemNegocieri, t0.BasicTargetDetailId,
2006 t0.[Target], t0.Discount, t0.DataOperare, t0.IdOperator
2007 from dbo.ElemNegocieriXBasicTargetDetail t0 join dbo.ElemNegocieri t1 on t0.IdElemNegocieri = t1.IdElemNegocieriPK
2008 where t1.IdNegocieriPK = @IdNegocieriPK
2009
2010 insert into @ManualConfirmationDetailXElemNegocieri (ManualConfirmationDetailXElemNegocieriId, SiteId,
2011 ManualConfirmationDetailId, IdElemNegocieri, Quantity)
2012 select t0.ManualConfirmationDetailXElemNegocieriId, t0.SiteId,
2013 t0.ManualConfirmationDetailId, t0.IdElemNegocieri, t0.Quantity
2014 from dbo.ManualConfirmationDetailXElemNegocieri t0 join dbo.ElemNegocieri t1 on t0.IdElemNegocieri = t1.IdElemNegocieriPK
2015 where t1.IdNegocieriPK = @IdNegocieriPK
2016
2017 insert into @NegocieriFisier (IdNegocieriFisierPK, IdNegocieriPK, FileOID,
2018 CreateTime, OperatorId, Obs)
2019 select t0.IdNegocieriFisierPK, t0.IdNegocieriPK, t0.FileOID,
2020 t0.CreateTime, t0.OperatorId, t0.Obs
2021 from dbo.NegocieriFisier t0
2022 where t0.IdNegocieriPK = @IdNegocieriPK
2023
2024 exec dbo.newSP_Negocieri_Respinge
2025 @Negocieri,
2026 @ElemNegocieri,
2027 @NegocieriXDivizie,
2028 @MarjaNegocieri,
2029 @TermenPlataDiferentiatNegocieri,
2030 @ActiuniNegocieri,
2031 @ElemNegocieriXBasicTargetDetail,
2032 @ManualConfirmationDetailXElemNegocieri,
2033 @OperatorOid,
2034 @Observatii,
2035 @NegocieriFisier
2036
2037 update t0 set t0.IsProcessed = 1, ProcessedOn = sysdatetime() from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key
2038end try
2039begin catch
2040 declare @ErrorMessage nvarchar(4000),
2041 @ErrorSeverity int,
2042 @ErrorState int
2043 select @ErrorMessage = error_message(), @ErrorSeverity = error_severity(), @ErrorState = error_state()
2044 raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState)
2045end catch
2046
2047
2048
2049
2050GO
2051
2052-------------------------------------------------------------------------------------------------------------------------------------------------------
2053-------------------------------------------------------------------------------------------------------------------------------------------------------
2054-------------------------------------------------------------------------------------------------------------------------------------------------------
2055
2056
2057
2058CREATE procedure [dbo].[newSP_Negocieri_Workflow_SolicitaFeedback]
2059 @Key uniqueidentifier,
2060 @Observatii varchar(450)
2061as
2062begin
2063begin try
2064 if not exists(select 1 from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key)
2065 begin
2066 raiserror(N'START_CUSTOM_MESSAGE Nu am gasit detaliile workflow-ului. END_CUSTOM_MESSAGE', 18, 1)
2067 return
2068 end
2069
2070 declare @IsProcessed bit = (select t0.IsProcessed from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key)
2071
2072 if @IsProcessed = 1
2073 begin
2074 raiserror('START_CUSTOM_MESSAGE Acest workflow a fost deja procesat. END_CUSTOM_MESSAGE', 18, 1)
2075 return
2076 end
2077
2078 declare @IdNegocieriPK int = (select t0.IdNegocieriPK from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key),
2079 /*@OperatorOid uniqueidentifier = ( select t2.Oid
2080 from dbo.NegocieriWorkflow t0 join dbo.Negocieri t1 on t0.IdNegocieriPK = t1.IdNegocieriPK
2081 join dbo.CustomUser t2 on t1.IdOperator = t2.IdUtilizator
2082 where t0.[Key] = @Key),*/
2083 @OperatorOid uniqueidentifier = (select t0.OperatorOid from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key),
2084 @Negocieri TypeNegocieri,
2085 @ElemNegocieri TypeElemNegocieri
2086
2087 insert into @Negocieri (IdNegocieriPK, IdClient, IdPLClient, Numar,
2088 DataInceput, DataSfarsit, DataCreare, IdOperatorCreare,
2089 MomentOperare, IdOperator, [Status], OperatorAprobareRV, DataAprobareRV,
2090 OperatorAprobareASM, DataAprobareASM, OperatorRespinsASM, DataRespinsASM,
2091 OperatorAprobareMKT, DataAprobareMKT, OperatorRespinsMKT, DataRespinsMKT,
2092 OperatorAprobareRSM, DataAprobareRSM, OperatorRespinsRSM, DataRespinsRSM,
2093 OperatorAprobareNSM, DataAprobareNSM, OperatorRespinsNSM, DataRespinsNSM,
2094 Valoare, NrCrt, ProcDisc, BazaImpozitare,
2095 NotApplyAutomaticallyDisc, ToatePL, FacturareAutomata, DiscountApplicationType, ModificaDisc,
2096 MotivRefuz, EditareDisc, Realizat, Observatie,
2097 IsTermenPlataDiferentiat, ObservatiiMKT, MotivMarjaInsuficienta, IdProducator,
2098 FeedBack, IdMasterNegocieri, IdParinteNegocieri, Versiune, UltimaVersiune, IsManualConfirmation)
2099 select t0.IdNegocieriPK, t0.IdClient, t0.IdPLClient, t0.Numar,
2100 t0.DataInceput, t0.DataSfarsit, t0.DataCreare, t0.IdOperatorCreare,
2101 t0.MomentOperare, t0.IdOperator, t0.[Status], t0.OperatorAprobareRV, t0.DataAprobareRV,
2102 t0.OperatorAprobareASM, t0.DataAprobareASM, t0.OperatorRespinsASM, t0.DataRespinsASM,
2103 t0.OperatorAprobareMKT, t0.DataAprobareMKT, t0.OperatorRespinsMKT, t0.DataRespinsMKT,
2104 t0.OperatorAprobareRSM, t0.DataAprobareRSM, t0.OperatorRespinsRSM, t0.DataRespinsRSM,
2105 t0.OperatorAprobareNSM, t0.DataAprobareNSM, t0.OperatorRespinsNSM, t0.DataRespinsNSM,
2106 t0.Valoare, t0.NrCrt, t0.ProcDisc, t0.BazaImpozitare,
2107 t0.NotApplyAutomaticallyDisc, t0.ToatePL, t0.FacturareAutomata, t0.DiscountApplicationType, t0.ModificaDisc,
2108 t0.MotivRefuz, t0.EditareDisc, t0.Realizat, t0.Observatie,
2109 t0.IsTermenPlataDiferentiat, t0.ObservatiiMKT, t0.MotivMarjaInsuficienta, t0.IdProducator,
2110 t0.FeedBack, t0.IdMasterNegocieri, t0.IdParinteNegocieri, t0.Versiune, t0.UltimaVersiune, t0.IsManualConfirmation
2111 from dbo.Negocieri t0
2112 where t0.IdNegocieriPK = @IdNegocieriPK
2113
2114 insert into @ElemNegocieri (IdElemNegocieriPK, IdNegocieriPK, IdProdus, CantUV,
2115 CantUVRamas, Pret, ProcDiscTotal, ProcDiscTotalRec,
2116 ProcDisc, ProcTVA, Valoare, PretRidicata,
2117 PretFaraDisc, TermenPlata, ProcMarja, IdMarja,
2118 IsRabat, CantBax, IdDivizie, ProcMarjaCalculat,
2119 IdElemOfertaSpeciala, ProcDiscOfferDetail, ProcDiscOfferHeader, IsRabatHeaderOffer,
2120 IsRabatDetailOffer, ProcDiscRecOfferDetail, ProcDiscRecOfferHeader, ProcMarjaMinim,
2121 ProcDiscAutomat, ProcMarjaReal, FinalSettlementTypeId, FinalPaymentTerm,
2122 DiscountRatePT, GenerateFinDocMasterPT, SuportatProducatorOfferDetail, SuportatMDPOfferDetail,
2123 SuportatASMOfferDetail, SuportatProducatorOfferHeader, SuportatMDPOfferHeader, SuportatASMOfferHeader,
2124 ProcDiscSuplimentar, DiscSustinutOSProducator, DiscSustinutOSMkt, DiscSustinutOSAsm,
2125 ValDiscSuplimentarProducator, DiscSuplimentarProducator, ValDiscSustinutCosMkt, DiscSustinutCosMkt,
2126 ValDiscSustinutCosAsm, DiscSustinutCosAsm, ValDiscSustinutCos, DiscSustinutCos,
2127 ValDiscSustinutReversare, DiscSustinutReversare, ValDiscSustinutNonRx, DiscSustinutNonRx,
2128 ValDisc, ValoareNeta, MargineNeta, MarjaNeta,
2129 NrCrt, IdOfertaSpecialaTemp, Inlocuit, DataInlocuit,
2130 ElementInlocuit, CantInlocuita, MargineReala, MargineRealaElInlocuit,
2131 ElemNegocieriIstoric, PretNet, SuportatCosMKTOfferDetail, SuportatCosMKTOfferHeader,
2132 DiscSustinutOSCosMKT, ValDiscSuplimentarProducatorRec, DiscSuplimentarProducatorRec, RecuperareRN,
2133 ExistaModificari, DiscOferteRebate, ValDiscOferteRebate, DiscSuplimentarSustinutMDP,
2134 ValDiscSuplimentarSustinutMDP, IdPrag, IdPragElem, ProcDiscRecOfferDetailOld,
2135 ProcDiscRecOfferHeaderOld, ValScontare, ProcentScontare, ValScontareOld,
2136 IdElemNegocieriPKParinte, DiscSuplimentarProducatorRecOld, DiscSuplimentarSustinutMDPOld, DiscSustinutCosMktOld,
2137 DiscSustinutASPRI, ValDiscSustinutASPRI, IdTipDecontare, IsModificat)
2138 select t0.IdElemNegocieriPK, t0.IdNegocieriPK, t0.IdProdus, t0.CantUV,
2139 t0.CantUVRamas, t0.Pret, t0.ProcDiscTotal, t0.ProcDiscTotalRec,
2140 t0.ProcDisc, t0.ProcTVA, t0.Valoare, t0.PretRidicata,
2141 t0.PretFaraDisc, t0.TermenPlata, t0.ProcMarja, t0.IdMarja,
2142 t0.IsRabat, t0.CantBax, t0.IdDivizie, t0.ProcMarjaCalculat,
2143 t0.IdElemOfertaSpeciala, t0.ProcDiscOfferDetail, t0.ProcDiscOfferHeader, t0.IsRabatHeaderOffer,
2144 t0.IsRabatDetailOffer, t0.ProcDiscRecOfferDetail, t0.ProcDiscRecOfferHeader, t0.ProcMarjaMinim,
2145 t0.ProcDiscAutomat, t0.ProcMarjaReal, t0.FinalSettlementTypeId, t0.FinalPaymentTerm,
2146 t0.DiscountRatePT, t0.GenerateFinDocMasterPT, t0.SuportatProducatorOfferDetail, t0.SuportatMDPOfferDetail,
2147 t0.SuportatASMOfferDetail, t0.SuportatProducatorOfferHeader, t0.SuportatMDPOfferHeader, t0.SuportatASMOfferHeader,
2148 t0.ProcDiscSuplimentar, t0.DiscSustinutOSProducator, t0.DiscSustinutOSMkt, t0.DiscSustinutOSAsm,
2149 t0.ValDiscSuplimentarProducator, t0.DiscSuplimentarProducator, t0.ValDiscSustinutCosMkt, t0.DiscSustinutCosMkt,
2150 t0.ValDiscSustinutCosAsm, t0.DiscSustinutCosAsm, t0.ValDiscSustinutCos, t0.DiscSustinutCos,
2151 t0.ValDiscSustinutReversare, t0.DiscSustinutReversare, t0.ValDiscSustinutNonRx, t0.DiscSustinutNonRx,
2152 t0.ValDisc, t0.ValoareNeta, t0.MargineNeta, t0.MarjaNeta,
2153 t0.NrCrt, t0.IdOfertaSpecialaTemp, t0.Inlocuit, t0.DataInlocuit,
2154 t0.ElementInlocuit, t0.CantInlocuita, t0.MargineReala, t0.MargineRealaElInlocuit,
2155 t0.ElemNegocieriIstoric, t0.PretNet, t0.SuportatCosMKTOfferDetail, t0.SuportatCosMKTOfferHeader,
2156 t0.DiscSustinutOSCosMKT, t0.ValDiscSuplimentarProducatorRec, t0.DiscSuplimentarProducatorRec, t0.RecuperareRN,
2157 t0.ExistaModificari, t0.DiscOferteRebate, t0.ValDiscOferteRebate, t0.DiscSuplimentarSustinutMDP,
2158 t0.ValDiscSuplimentarSustinutMDP, t0.IdPrag, t0.IdPragElem, t0.ProcDiscRecOfferDetailOld,
2159 t0.ProcDiscRecOfferHeaderOld, t0.ValScontare, t0.ProcentScontare, t0.ValScontareOld,
2160 t0.IdElemNegocieriPKParinte, t0.DiscSuplimentarProducatorRecOld, t0.DiscSuplimentarSustinutMDPOld, t0.DiscSustinutCosMktOld,
2161 t0.DiscSustinutASPRI, t0.ValDiscSustinutASPRI, IdTipDecontare, t0.IsModificat
2162 from dbo.ElemNegocieri t0
2163 where t0.IdNegocieriPK = @IdNegocieriPK
2164
2165 exec dbo.newSP_Negocieri_CereFeedBack
2166 @Negocieri,
2167 @ElemNegocieri,
2168 @OperatorOid,
2169 @Observatii
2170
2171 update t0 set t0.IsProcessed = 1, ProcessedOn = sysdatetime() from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key
2172end try
2173begin catch
2174 declare @ErrorMessage nvarchar(4000),
2175 @ErrorSeverity int,
2176 @ErrorState int
2177 select @ErrorMessage = error_message(), @ErrorSeverity = error_severity(), @ErrorState = error_state()
2178 raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState)
2179end catch
2180end
2181
2182
2183GO
2184
2185
2186
2187-------------------------------------------------------------------------------------------------------------------------------------------------------
2188-------------------------------------------------------------------------------------------------------------------------------------------------------
2189-------------------------------------------------------------------------------------------------------------------------------------------------------
2190
2191
2192
2193
2194CREATE procedure [dbo].[newSP_Negocieri_Workflow_Valideaza]
2195 @Key uniqueidentifier
2196as
2197begin try
2198 if not exists(select 1 from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key)
2199 begin
2200 raiserror(N'START_CUSTOM_MESSAGE Nu am gasit detaliile workflow-ului. END_CUSTOM_MESSAGE', 18, 1)
2201 return --teoretic aceasta linie nu ar trebui sa se mai execute deoarece suntem ?ntr-un try catch, dar misterioase sunt caile programatorilor
2202 end
2203
2204 declare @IsProcessed bit = (select t0.IsProcessed from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key)
2205
2206 if @IsProcessed = 1
2207 begin
2208 raiserror(N'START_CUSTOM_MESSAGE Acest workflow a fost deja procesat. END_CUSTOM_MESSAGE', 18, 1)
2209 return
2210 end
2211
2212 declare @ASM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'ASM' and t1.Nume = 'CustomUserType'),
2213 @NSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t0.nume = 'NSM' and t1.Nume = 'CustomUserType'),
2214 @AprobareASM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare ASM'),
2215 @AprobareNSM int = (select t0.IdElemDic from dbo.ElemDic (nolock) t0 join dbo.Dic (nolock) t1 on t0.IdDic = t1.IdDic where t1.Nume = 'StatusNegocieri' and t0.Nume = 'Aprobare NSM'),
2216 @TipOperator int = (select t0.UserType from dbo.CustomUser (nolock) t0 join dbo.NegocieriWorkflow t1 on t0.Oid = t1.OperatorOid where t1.[Key] = @Key),
2217 @OldStatus int = (select t1.[Status] from dbo.NegocieriWorkflow t0 join dbo.Negocieri (nolock) t1 on t0.IdNegocieriPK = t1.IdNegocieriPK where t0.[Key] = @Key)
2218
2219 if @TipOperator <> @ASM and @TipOperator <> @NSM
2220 begin
2221 raiserror(N'START_CUSTOM_MESSAGE Tip-ul userului trebuie sa fie ASM sau NSM. END_CUSTOM_MESSAGE', 18, 1)
2222 return
2223 end
2224
2225 if @TipOperator = @ASM and @OldStatus <> @AprobareASM
2226 begin
2227 raiserror(N'START_CUSTOM_MESSAGE Tip-ul userului este ASM dar negocierea nu mai e ?n status Aprobare ASM. END_CUSTOM_MESSAGE', 18, 1)
2228 return
2229 end
2230
2231 if @TipOperator = @NSM and @OldStatus <> @AprobareNSM
2232 begin
2233 raiserror(N'START_CUSTOM_MESSAGE Tip-ul userului este NSM dar negocierea nu mai e ?n status Aprobare NSM. END_CUSTOM_MESSAGE', 18, 1)
2234 return
2235 end
2236
2237 declare @IdNegocieriPK int = (select t0.IdNegocieriPK from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key),
2238 @OperatorOid uniqueidentifier = (select t0.OperatorOid from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key),
2239 @Obs varchar(450) = '',
2240 @Negocieri TypeNegocieri,
2241 @ElemNegocieri TypeElemNegocieri,
2242 @NegocieriXDivizie TypeNegocieriXDivizie,
2243 @MarjaNegocieri TypeMarjaNegocieri,
2244 @TermenPlataDiferentiatNegocieri TypeTermenPlataDiferentiatNegocieri,
2245 @ActiuniNegocieri TypeActiuniNegocieri,
2246 @ElemNegocieriXBasicTargetDetail TypeElemNegocieriXBasicTargetDetail,
2247 @ManualConfirmationDetailXElemNegocieri TypeManualConfirmationDetailXElemNegocieri,
2248 @NegocieriFisier TypeNegocieriFisier
2249
2250 insert into @Negocieri (IdNegocieriPK, IdClient, IdPLClient, Numar,
2251 DataInceput, DataSfarsit, DataCreare, IdOperatorCreare,
2252 MomentOperare, IdOperator, [Status], OperatorAprobareRV, DataAprobareRV,
2253 OperatorAprobareASM, DataAprobareASM, OperatorRespinsASM, DataRespinsASM,
2254 OperatorAprobareMKT, DataAprobareMKT, OperatorRespinsMKT, DataRespinsMKT,
2255 OperatorAprobareRSM, DataAprobareRSM, OperatorRespinsRSM, DataRespinsRSM,
2256 OperatorAprobareNSM, DataAprobareNSM, OperatorRespinsNSM, DataRespinsNSM,
2257 Valoare, NrCrt, ProcDisc, BazaImpozitare,
2258 NotApplyAutomaticallyDisc, ToatePL, FacturareAutomata, DiscountApplicationType, IdTipNegociere, ModificaDisc,
2259 MotivRefuz, EditareDisc, Realizat, Observatie,
2260 IsTermenPlataDiferentiat, ObservatiiMKT, MotivMarjaInsuficienta, IdProducator,
2261 FeedBack, IdMasterNegocieri, IdParinteNegocieri, Versiune, UltimaVersiune, IsManualConfirmation)
2262 select t0.IdNegocieriPK, t0.IdClient, t0.IdPLClient, t0.Numar,
2263 t0.DataInceput, t0.DataSfarsit, t0.DataCreare, t0.IdOperatorCreare,
2264 t0.MomentOperare, t0.IdOperator, t0.[Status], t0.OperatorAprobareRV, t0.DataAprobareRV,
2265 t0.OperatorAprobareASM, t0.DataAprobareASM, t0.OperatorRespinsASM, t0.DataRespinsASM,
2266 t0.OperatorAprobareMKT, t0.DataAprobareMKT, t0.OperatorRespinsMKT, t0.DataRespinsMKT,
2267 t0.OperatorAprobareRSM, t0.DataAprobareRSM, t0.OperatorRespinsRSM, t0.DataRespinsRSM,
2268 t0.OperatorAprobareNSM, t0.DataAprobareNSM, t0.OperatorRespinsNSM, t0.DataRespinsNSM,
2269 t0.Valoare, t0.NrCrt, t0.ProcDisc, t0.BazaImpozitare,
2270 t0.NotApplyAutomaticallyDisc, t0.ToatePL, t0.FacturareAutomata, t0.DiscountApplicationType, t0.IdTipNegociere, t0.ModificaDisc,
2271 t0.MotivRefuz, t0.EditareDisc, t0.Realizat, t0.Observatie,
2272 t0.IsTermenPlataDiferentiat, t0.ObservatiiMKT, t0.MotivMarjaInsuficienta, t0.IdProducator,
2273 t0.FeedBack, t0.IdMasterNegocieri, t0.IdParinteNegocieri, t0.Versiune, t0.UltimaVersiune, t0.IsManualConfirmation
2274 from dbo.Negocieri t0
2275 where t0.IdNegocieriPK = @IdNegocieriPK
2276
2277 insert into @ElemNegocieri (IdElemNegocieriPK, IdNegocieriPK, IdProdus, CantUV,
2278 CantUVRamas, Pret, ProcDiscTotal, ProcDiscTotalRec,
2279 ProcDisc, ProcTVA, Valoare, PretRidicata,
2280 PretFaraDisc, TermenPlata, ProcMarja, IdMarja,
2281 IsRabat, CantBax, IdDivizie, ProcMarjaCalculat,
2282 IdElemOfertaSpeciala, ProcDiscOfferDetail, ProcDiscOfferHeader, IsRabatHeaderOffer,
2283 IsRabatDetailOffer, ProcDiscRecOfferDetail, ProcDiscRecOfferHeader, ProcMarjaMinim,
2284 ProcDiscAutomat, ProcMarjaReal, FinalSettlementTypeId, FinalPaymentTerm,
2285 DiscountRatePT, GenerateFinDocMasterPT, SuportatProducatorOfferDetail, SuportatMDPOfferDetail,
2286 SuportatASMOfferDetail, SuportatProducatorOfferHeader, SuportatMDPOfferHeader, SuportatASMOfferHeader,
2287 ProcDiscSuplimentar, DiscSustinutOSProducator, DiscSustinutOSMkt, DiscSustinutOSAsm,
2288 ValDiscSuplimentarProducator, DiscSuplimentarProducator, ValDiscSustinutCosMkt, DiscSustinutCosMkt,
2289 ValDiscSustinutCosAsm, DiscSustinutCosAsm, ValDiscSustinutCos, DiscSustinutCos,
2290 ValDiscSustinutReversare, DiscSustinutReversare, ValDiscSustinutNonRx, DiscSustinutNonRx,
2291 ValDisc, ValoareNeta, MargineNeta, MarjaNeta,
2292 NrCrt, IdOfertaSpecialaTemp, Inlocuit, DataInlocuit,
2293 ElementInlocuit, CantInlocuita, MargineReala, MargineRealaElInlocuit,
2294 ElemNegocieriIstoric, PretNet, SuportatCosMKTOfferDetail, SuportatCosMKTOfferHeader,
2295 DiscSustinutOSCosMKT, ValDiscSuplimentarProducatorRec, DiscSuplimentarProducatorRec, RecuperareRN,
2296 ExistaModificari, DiscOferteRebate, ValDiscOferteRebate, DiscSuplimentarSustinutMDP,
2297 ValDiscSuplimentarSustinutMDP, IdPrag, IdPragElem, ProcDiscRecOfferDetailOld,
2298 ProcDiscRecOfferHeaderOld, ValScontare, ProcentScontare, ValScontareOld,
2299 IdElemNegocieriPKParinte, DiscSuplimentarProducatorRecOld, DiscSuplimentarSustinutMDPOld, DiscSustinutCosMktOld,
2300 DiscSustinutASPRI, ValDiscSustinutASPRI, IdTipDecontare, IsModificat)
2301 select t0.IdElemNegocieriPK, t0.IdNegocieriPK, t0.IdProdus, t0.CantUV,
2302 t0.CantUVRamas, t0.Pret, t0.ProcDiscTotal, t0.ProcDiscTotalRec,
2303 t0.ProcDisc, t0.ProcTVA, t0.Valoare, t0.PretRidicata,
2304 t0.PretFaraDisc, t0.TermenPlata, t0.ProcMarja, t0.IdMarja,
2305 t0.IsRabat, t0.CantBax, t0.IdDivizie, t0.ProcMarjaCalculat,
2306 t0.IdElemOfertaSpeciala, t0.ProcDiscOfferDetail, t0.ProcDiscOfferHeader, t0.IsRabatHeaderOffer,
2307 t0.IsRabatDetailOffer, t0.ProcDiscRecOfferDetail, t0.ProcDiscRecOfferHeader, t0.ProcMarjaMinim,
2308 t0.ProcDiscAutomat, t0.ProcMarjaReal, t0.FinalSettlementTypeId, t0.FinalPaymentTerm,
2309 t0.DiscountRatePT, t0.GenerateFinDocMasterPT, t0.SuportatProducatorOfferDetail, t0.SuportatMDPOfferDetail,
2310 t0.SuportatASMOfferDetail, t0.SuportatProducatorOfferHeader, t0.SuportatMDPOfferHeader, t0.SuportatASMOfferHeader,
2311 t0.ProcDiscSuplimentar, t0.DiscSustinutOSProducator, t0.DiscSustinutOSMkt, t0.DiscSustinutOSAsm,
2312 t0.ValDiscSuplimentarProducator, t0.DiscSuplimentarProducator, t0.ValDiscSustinutCosMkt, t0.DiscSustinutCosMkt,
2313 t0.ValDiscSustinutCosAsm, t0.DiscSustinutCosAsm, t0.ValDiscSustinutCos, t0.DiscSustinutCos,
2314 t0.ValDiscSustinutReversare, t0.DiscSustinutReversare, t0.ValDiscSustinutNonRx, t0.DiscSustinutNonRx,
2315 t0.ValDisc, t0.ValoareNeta, t0.MargineNeta, t0.MarjaNeta,
2316 t0.NrCrt, t0.IdOfertaSpecialaTemp, t0.Inlocuit, t0.DataInlocuit,
2317 t0.ElementInlocuit, t0.CantInlocuita, t0.MargineReala, t0.MargineRealaElInlocuit,
2318 t0.ElemNegocieriIstoric, t0.PretNet, t0.SuportatCosMKTOfferDetail, t0.SuportatCosMKTOfferHeader,
2319 t0.DiscSustinutOSCosMKT, t0.ValDiscSuplimentarProducatorRec, t0.DiscSuplimentarProducatorRec, t0.RecuperareRN,
2320 t0.ExistaModificari, t0.DiscOferteRebate, t0.ValDiscOferteRebate, t0.DiscSuplimentarSustinutMDP,
2321 t0.ValDiscSuplimentarSustinutMDP, t0.IdPrag, t0.IdPragElem, t0.ProcDiscRecOfferDetailOld,
2322 t0.ProcDiscRecOfferHeaderOld, t0.ValScontare, t0.ProcentScontare, t0.ValScontareOld,
2323 t0.IdElemNegocieriPKParinte, t0.DiscSuplimentarProducatorRecOld, t0.DiscSuplimentarSustinutMDPOld, t0.DiscSustinutCosMktOld,
2324 t0.DiscSustinutASPRI, t0.ValDiscSustinutASPRI, t0.IdTipDecontare, t0.IsModificat
2325 from dbo.ElemNegocieri t0
2326 where t0.IdNegocieriPK = @IdNegocieriPK
2327
2328 insert into @NegocieriXDivizie (IdNegocieriPK, Divisions, OID, Marja, Margine, Valoare, BazaImpozitare, ValScontare)
2329 select t0.IdNegocieriPK, t0.Divisions, t0.OID, t0.Marja, t0.Margine, t0.Valoare, t0.BazaImpozitare, t0.ValScontare
2330 from dbo.NegocieriXDivizie t0
2331 where t0.IdNegocieriPK = @IdNegocieriPK
2332
2333 insert into @MarjaNegocieri (IdMarjaNegocieriPK, IdNegocieriPK, GrossSales, DiscounturiPeLinie,
2334 DiscountPeComanda, DiscountFinanciarPeComanda, CAF, COGS,
2335 DiscounturiRecuperate, DiscounturiRecuperateReal, COGSNet, COGSNetReal,
2336 MargineComanda, MargineReala, MarjaComanda, MarjaReala,
2337 AvantajClient, BazaImpozitareRX, BazaImpozitareNonRX, CreditNotaPeComanda, ValScontare)
2338 select t0.IdMarjaNegocieriPK, t0.IdNegocieriPK, t0.GrossSales, t0.DiscounturiPeLinie,
2339 t0.DiscountPeComanda, t0.DiscountFinanciarPeComanda, t0.CAF, t0.COGS,
2340 t0.DiscounturiRecuperate, t0.DiscounturiRecuperateReal, t0.COGSNet, t0.COGSNetReal,
2341 t0.MargineComanda, t0.MargineReala, t0.MarjaComanda, t0.MarjaReala,
2342 t0.AvantajClient, t0.BazaImpozitareRX, t0.BazaImpozitareNonRX, t0.CreditNotaPeComanda, t0.ValScontare
2343 from dbo.MarjaNegocieri t0
2344 where t0.IdNegocieriPK = @IdNegocieriPK
2345
2346 insert into @TermenPlataDiferentiatNegocieri (IdTermenPlataDiferentiatNegocieriPK, IdTipDecontareProdus, IdNegocieriPK,
2347 IdTipDecontareComanda, TPProdus, TPComanda, ProcDisc,
2348 Valoare, CNFinanciara, DiferentaNrZile, NoOfDaysPerMonth,
2349 NoOfMonths, DefaultDiscountRatePerMonth, CalculatedDiscountRate, AplicaProcDiscImplicit,
2350 DefaultDiscountRatePerMonthIni)
2351 select t0.IdTermenPlataDiferentiatNegocieriPK, t0.IdTipDecontareProdus, t0.IdNegocieriPK,
2352 t0.IdTipDecontareComanda, t0.TPProdus, t0.TPComanda, t0.ProcDisc,
2353 t0.Valoare, t0.CNFinanciara, t0.DiferentaNrZile, t0.NoOfDaysPerMonth,
2354 t0.NoOfMonths, t0.DefaultDiscountRatePerMonth, t0.CalculatedDiscountRate, t0.AplicaProcDiscImplicit,
2355 t0.DefaultDiscountRatePerMonthIni
2356 from dbo.TermenPlataDiferentiatNegocieri t0
2357 where t0.IdNegocieriPK = @IdNegocieriPK
2358
2359 insert into @ActiuniNegocieri (IdActiuniNegocieri, IdNegocieri, IdPersoana, DataOperare,
2360 Tip, StatusInitial, StatusFinal, Obs)
2361 select t0.IdActiuniNegocieri, t0.IdNegocieri, t0.IdPersoana, t0.DataOperare,
2362 t0.Tip, t0.StatusInitial, t0.StatusFinal, t0.Obs
2363 from dbo.ActiuniNegocieri t0
2364 where t0.IdNegocieri = @IdNegocieriPK
2365
2366 insert into @ElemNegocieriXBasicTargetDetail (ElemNegocieriXBasicTargetDetailId, IdElemNegocieri, BasicTargetDetailId,
2367 [Target], Discount, DataOperare, IdOperator)
2368 select t0.ElemNegocieriXBasicTargetDetailId, t0.IdElemNegocieri, t0.BasicTargetDetailId,
2369 t0.[Target], t0.Discount, t0.DataOperare, t0.IdOperator
2370 from dbo.ElemNegocieriXBasicTargetDetail t0 join dbo.ElemNegocieri t1 on t0.IdElemNegocieri = t1.IdElemNegocieriPK
2371 where t1.IdNegocieriPK = @IdNegocieriPK
2372
2373 insert into @ManualConfirmationDetailXElemNegocieri (ManualConfirmationDetailXElemNegocieriId, SiteId,
2374 ManualConfirmationDetailId, IdElemNegocieri, Quantity)
2375 select t0.ManualConfirmationDetailXElemNegocieriId, t0.SiteId,
2376 t0.ManualConfirmationDetailId, t0.IdElemNegocieri, t0.Quantity
2377 from dbo.ManualConfirmationDetailXElemNegocieri t0 join dbo.ElemNegocieri t1 on t0.IdElemNegocieri = t1.IdElemNegocieriPK
2378 where t1.IdNegocieriPK = @IdNegocieriPK
2379
2380 insert into @NegocieriFisier (IdNegocieriFisierPK, IdNegocieriPK, FileOID,
2381 CreateTime, OperatorId, Obs)
2382 select t0.IdNegocieriFisierPK, t0.IdNegocieriPK, t0.FileOID,
2383 t0.CreateTime, t0.OperatorId, t0.Obs
2384 from dbo.NegocieriFisier t0
2385 where t0.IdNegocieriPK = @IdNegocieriPK
2386
2387 exec dbo.newSP_Negocieri_Aproba
2388 @Negocieri,
2389 @ElemNegocieri,
2390 @NegocieriXDivizie,
2391 @MarjaNegocieri,
2392 @TermenPlataDiferentiatNegocieri,
2393 @ActiuniNegocieri,
2394 @ElemNegocieriXBasicTargetDetail,
2395 @ManualConfirmationDetailXElemNegocieri,
2396 @OperatorOid,
2397 @Obs,
2398 @NegocieriFisier,
2399 @IdNegocieriPk output
2400
2401 update t0 set t0.IsProcessed = 1, ProcessedOn = sysdatetime() from dbo.NegocieriWorkflow t0 where t0.[Key] = @Key
2402end try
2403begin catch
2404 declare @ErrorMessage nvarchar(4000),
2405 @ErrorSeverity int,
2406 @ErrorState int
2407 select @ErrorMessage = error_message(), @ErrorSeverity = error_severity(), @ErrorState = error_state()
2408 raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState)
2409end catch
2410
2411
2412GO
2413
2414
2415
2416
2417-------------------------------------------------------------------------------------------------------------------------------------------------------
2418-------------------------------------------------------------------------------------------------------------------------------------------------------
2419-------------------------------------------------------------------------------------------------------------------------------------------------------
2420
2421
2422CREATE PROCEDURE [dbo].[newSP_GetNegocieri_Detail]
2423@IdNegocieriPK int
2424AS
2425BEGIN
2426
2427select
2428N.IdNegocieriPK,
2429N.IdClient,
2430N.IdPLClient,
2431N.Numar,
2432N.DataInceput,
2433N.DataSfarsit,
2434N.DataCreare,
2435N.IdOperatorCreare,
2436N.MomentOperare,
2437N.IdOperator,
2438N.Status,
2439N.OperatorAprobareRV,
2440N.DataAprobareRV,
2441N.OperatorAprobareASM,
2442N.DataAprobareASM,
2443N.OperatorRespinsASM,
2444N.DataRespinsASM,
2445N.OperatorAprobareMKT,
2446N.DataAprobareMKT,
2447N.OperatorRespinsMKT,
2448N.DataRespinsMKT,
2449N.OperatorAprobareRSM,
2450N.DataAprobareRSM,
2451N.OperatorRespinsRSM,
2452N.DataRespinsRSM,
2453N.OperatorAprobareNSM,
2454N.DataAprobareNSM,
2455N.OperatorRespinsNSM,
2456N.DataRespinsNSM,
2457N.Valoare,
2458N.NrCrt,
2459N.ProcDisc,
2460N.BazaImpozitare,
2461N.NotApplyAutomaticallyDisc,
2462N.ToatePL,
2463N.FacturareAutomata,
2464N.DiscountApplicationType,
2465N.IdTipNegociere,
2466N.ModificaDisc,
2467N.MotivRefuz,
2468N.EditareDisc,
2469N.Realizat,
2470N.Observatie,
2471N.IsTermenPlataDiferentiat,
2472N.ObservatiiMKT,
2473N.MotivMarjaInsuficienta,
2474N.IdProducator,
2475N.FeedBack,
2476N.IdMasterNegocieri,
2477N.IdParinteNegocieri,
2478N.Versiune,
2479N.UltimaVersiune,
2480N.IsManualConfirmation,
2481P.Nume as NumeIdOperator,
2482P2.Nume as NumeIdOperatorCreare,
2483P3.Nume as NumeOperatorAprobareRV,
2484P4.Nume as NumeOperatorAprobareASM,
2485P5.Nume as NumeOperatorAprobareMKT,
2486P6.Nume as NumeOperatorRespinsMKT,
2487P7.Nume as NumeOperatorAprobareRSM,
2488P8.Nume as NumeOperatorRespinsRSM,
2489P9.Nume as NumeOperatorAprobareNSM,
2490P10.Nume as NumeOperatorRespinsNSM,
2491ED.Nume as NumeDiscountApplicationType,
2492ED.Nume as NumeMotivMarjaInsuficienta,
2493F.Nume as NumeClient,
2494PL.Nume as NumePunctLucru,
2495ED4.Nume as NumeTipNegociere
2496from Negocieri N
2497inner join Firma F with (nolock)
2498 on F.IdFirma = N.IdClient
2499inner join PunctLucru PL with (nolock)
2500 on PL.IdPunctLucru = N.IdPLClient
2501left join Persoana P with (nolock)
2502 on P.IdPersoana = N.IdOperator
2503left join Persoana P2 with (nolock)
2504 on P2.IdPersoana = N.IdOperatorCreare
2505left join Persoana P3 with (nolock)
2506 on P3.IdPersoana = N.OperatorAprobareRV
2507left join Persoana P4 with (nolock)
2508 on P4.IdPersoana = N.OperatorAprobareASM
2509left join Persoana P5 with (nolock)
2510 on P5.IdPersoana = N.OperatorAprobareMKT
2511left join Persoana P6 with (nolock)
2512 on P6.IdPersoana = N.OperatorRespinsMKT
2513left join Persoana P7 with (nolock)
2514 on P7.IdPersoana = N.OperatorAprobareRSM
2515left join Persoana P8 with (nolock)
2516 on P8.IdPersoana = N.OperatorRespinsRSM
2517left join Persoana P9 with (nolock)
2518 on P9.IdPersoana = N.OperatorAprobareNSM
2519left join Persoana P10 with (nolock)
2520 on P10.IdPersoana = N.OperatorRespinsNSM
2521inner join ElemDic ED with (nolock)
2522 on ED.IdElemDic = N.DiscountApplicationType
2523inner join ElemDic ED2 with (nolock)
2524 on ED2.IdElemDic = N.Status
2525left join ElemDic ED3 with (nolock)
2526 on ED3.IdElemDic = N.MotivMarjaInsuficienta
2527left join ElemDic ED4 with (nolock)
2528 on ED4.IdElemDic = N.IdTipNegociere
2529where N.IdNegocieriPK = @IdNegocieriPK
2530END
2531
2532
2533GO