· 6 years ago · Nov 18, 2019, 11:54 AM
1
2SET NOCOUNT ON;
3
4
5DECLARE @tbl_databases TABLE (DBNAME NVARCHAR(MAX))
6INSERT @tbl_databases
7SELECT DISTINCT db FROM syserp.erp.implementation WHERE IsLocalDB = 1
8
9
10DECLARE @spcodestoexecute TABLE (code NVARCHAR(MAX), executed BIT, issys bit, keycol INT IDENTITY(1,1))
11
12
13INSERT @spcodestoexecute(issys, code)
14SELECT
15 0 as issys,
16 ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''Rapoarte_OferteClientiHTML1'') AND type in (N''P''))
17 DROP PROCEDURE Rapoarte_OferteClientiHTML1
18; '
19INSERT @spcodestoexecute(issys, code)
20SELECT
21 0 as issys,
22 '
23
24
25
26
27CREATE PROCEDURE [dbo].[Rapoarte_OferteClientiHTML1]
28 @OfertaClientID INT = NULL
29, @SYS_UNITID INT = NULL
30, @SYS_DIVID INT = NULL
31--, @SYS_ROOTURL NVARCHAR(MAX) = NULL
32, @SYS_INSTANCE NVARCHAR(100) = NULL
33, @SYS_LANGID NVARCHAR(2) = NULL
34, @ImageId INT = NULL
35, @DocImageFDID INT = NULL
36
37AS
38
39 BEGIN
40 SET NOCOUNT ON ;
41 declare @SYS_ROOTURL NVARCHAR(MAX) = NULL
42 SELECT @OfertaClientID = ISNULL(@OfertaClientID, 50)
43 , @SYS_UNITID = ISNULL(@SYS_UNITID, 2091)
44 , @SYS_DIVID = ISNULL(@SYS_DIVID, 1)
45 , @SYS_ROOTURL = ISNULL(@SYS_ROOTURL, ''https://dev.plurivaerp.com/'')
46 , @SYS_INSTANCE = ISNULL(@SYS_INSTANCE, ''DevPluriva'')
47
48 DECLARE @Nevalidat NVARCHAR(100)
49 , @NeAprobat NVARCHAR(100)
50 , @Refuzat nvarchar(100)
51 , @Validat nvarchar(100)
52 , @PreturiTva_dict nvarchar(400)
53 , @PreturiFaraTva_dict nvarchar(400)
54 , @Contacte nvarchar(max) = null
55 , @LanguageId NVARCHAR(10)
56 , @CuTva BIT
57 , @LanguageId2 NVARCHAR(10) = NULL
58
59 SELECT @Nevalidat=ISNULL(SysErp.ERP.textdictionary(''Oferta nevalidata'',@SYS_LANGID),''Oferta nevalidata'')
60 , @NeAprobat=ISNULL(SysErp.ERP.textdictionary(''Oferta neaprobata'',@SYS_LANGID),''Oferta neaprobata'')
61 , @Refuzat=ISNULL(syserp.erp.textdictionary(''Oferta refuzata'',@SYS_LANGID),''Oferta refuzata'')
62 , @Validat=ISNULL(syserp.erp.textdictionary(''Oferta validata'',@SYS_LANGID),''Oferta validata'')
63 , @PreturiTva_dict=SysErp.ERP.textdictionary(''Preturile contin Tva'',@SYS_LANGID)
64 , @PreturiFaraTva_dict=SysErp.ERP.textdictionary(''Preturile nu contin Tva'',@SYS_LANGID)
65
66 SELECT @LanguageId = LanguageId
67 FROM DocImaginiFD (nolock)
68 WHERE ImagineId = @ImageId
69 AND DocImageFDID = @DocImageFDID
70
71
72 select @LanguageId2=c.LanguageId from tblContact c
73 inner join tblUnitate u on c.ContactID=u.ContactDefaultId
74 inner join tblOferteClienti o on u.UnitateID=o.ClientId
75 where o.OfertaClientId=@OfertaClientID
76
77 if @LanguageId2 is null
78
79 select @LanguageId2=c.LanguageId from tblContact c
80 inner join tblLead u on c.ContactID=u.ContactDefaultId
81 inner join tblOferteClienti o on u.LeadId=o.LeadId
82 where o.OfertaClientId=@OfertaClientID
83
84 select @LanguageId=isnull(@LanguageId2, @LanguageId)
85
86 SELECT @Contacte = ISNULL(@Contacte + '', <br>'','''') + c.DenumireContact
87 , @CuTva = ISNULL(oc.PretTva,0)
88 FROM dbo.tblOferteClienti oc (nolock)
89 LEFT JOIN dbo.tblContact c (nolock)
90 on c.contactid = oc.ContactId
91 where OfertaClientId = @OfertaClientID
92
93 SELECT @Contacte = ISNULL(@Contacte + '', <br>'','''') + c.DenumireContact
94 FROM dbo.tf_IDs_from_a_string((select ContactIds from tblOferteClienti (nolock) where OfertaClientId = @OfertaClientID)) n
95 JOIN tblContact c (nolock)
96 on c.contactid = n.Numbers
97
98 -- Insert statements for procedure here
99 DECLARE @setare350 NVARCHAR(10) = dbo.Valoaresetare(350, @SYS_UNITID, @SYS_DIVID)
100 ,@setare247 NVARCHAR(10) = dbo.Valoaresetare(247, @SYS_UNITID, @SYS_DIVID)
101 ,@Setare391 INT = dbo.ValoareSetare(391, @SYS_UNITID, @SYS_DIVID)
102 ,@setare422 INT = dbo.ValoareSetare(422, @SYS_UNITID, @SYS_DIVID)
103 ,@setare377 INT = dbo.ValoareSetare(377, @SYS_UNITID, @SYS_DIVID)
104 ,@Setare370 NVARCHAR(300) = dbo.Valoaresetare(370, @SYS_UNITID, @SYS_DIVID)
105 ,@Setare425 BIT = dbo.Valoaresetare(425, @SYS_UNITID, @SYS_DIVID)
106
107 DECLARE @TermenPlataPartener INT
108 , @TermenClient INT
109 , @TermenAgentId INT
110 , @ClientId INT
111 , @AgentId INT
112 , @TermenPlataId INT
113 , @ValoareProduse DECIMAL(18, 5)
114 , @ValoareServicii DECIMAL(18, 5)
115 , @valutaDefaultID CHAR(3)
116 , @CursCap DECIMAL(18, 5)
117 , @ValutaCap CHAR(3)
118
119 SELECT
120 P.ProdusId,
121 SUM(P.Stoc) as Stoc
122 into #Stoc
123 FROM
124 inv.Stoc P (nolock)
125 INNER join dbo.tblProduse SOP (nolock) ON P.ProdusId = SOP.ProdusId
126 INNER join dbo.tblGestiuni G (nolock) ON P.GestiuneId=G.GestiuneId
127
128 where
129 (P.ClientCustodieId is NULL or p.ClientCustodieId= @ClientId)
130 --and P.GestiuneId in (SELECT GestiuneId from dbo.DrepturiGestiuni(@SYS_USERID))
131 and P.FirmaId=@SYS_UNITID
132 and P.DivizieId=@SYS_DIVID
133 and ISNULL(G.OrdineVanzare,0)>0
134 GROUP BY P.ProdusId
135
136 DECLARE @ValoareDiscountMeniu DECIMAL(18,2) = NULL
137 SELECT @ValoareDiscountMeniu = SUM(Cant * CASE WHEN @CuTva = 1 THEN (ISNULL(p.PretAmanunt,p.PretValuta * (1+t.tva/100.00)) - ISNULL(p.PretAmanuntRedus, p.PretValutaRedus* (1+t.tva/100.00)))
138 ELSE (PretValuta - PretValutaRedus)
139 END)
140 FROM dbo.tblOferteClientiPoz2 p (NOLOCK)
141 inner join tblproduse pr (NOLOCK)
142 on pr.produsid = p.produsid
143 inner join tbltva t (NOLOCK)
144 on t.tvaid = pr.TvaId
145 WHERE OfertaClientId = @OfertaClientID
146
147
148 SELECT ROW_NUMBER() OVER (ORDER BY oc.OfertaClientId) AS RowNumber
149 , oc.NumarOferta AS OfferNumber
150 , CONVERT(VARCHAR(10), oc.DataOferta, 103) AS OfferDate
151 , cast(oc.NumarOferta as varchar(15)) + '' '' +
152 case when day(oc.DataOferta) < 10 then ''0'' + cast( day(oc.DataOferta) as varchar(15))
153 else
154 cast( day(oc.DataOferta) as varchar(15)) end +
155 case when month(oc.DataOferta) < 10 then ''0'' + cast(month(oc.DataOferta) as varchar(15))
156 else
157 cast(month(oc.DataOferta) as varchar(15)) end
158 + cast(year(oc.DataOferta) as varchar(15)) as NumarDate
159
160 , CONVERT(VARCHAR(10), oc.DataExpirare, 103) AS OfferExpirationDate
161 , coalesce(oc.DenumireClient,uc.DenumireUnitate, l.Denumire) AS Customer
162 , ISNULL(mp.DenumireModPlata,'''') AS ModPlata
163 , convert(varchar(10),oc.DataLivrare,103) as DataLivrare
164 , convert(varchar(10),oc.DataLivrare,108) as OraLivrare
165 , oc.Observatii1 AS OfferRemarks1
166 , oc.Observatii2 AS OfferRemarks2
167 , oc.Observatii1 AS Explicatii1
168 , isnull(oc.Observatii2 + ''<br>'','''')
169 + CASE WHEN ISNULL(oc.PretTva,0)=0 then ISNULL(@PreturiFaraTva_dict ,'''')
170 ELSE ISNULL(@PreturiTva_dict,'''') END AS Explicatii2
171
172 , REPLACE(''<b>'' + CASE WHEN @Setare391 = 0 OR (@setare391 in (1,2) AND @setare422 = 0)
173 THEN COALESCE(ocp.DenumireProdusAfisare, ocp.DenumireProdus, p.DenumireProdus, '''')
174 WHEN @Setare391 = 1 AND @Setare422 = 1
175 THEN COALESCE(ocp.CodProdusAfisare + '' '', p.Cod + '' '', '''')
176 + COALESCE(ocp.DenumireProdusAfisare, ocp.DenumireProdus, p.DenumireProdus, '''')
177 WHEN @Setare391 = 2 AND @Setare422 = 1
178 THEN COALESCE(ocp.DenumireProdusAfisare, ocp.DenumireProdus, p.DenumireProdus, '''')
179 + COALESCE('' '' + ocp.CodProdusAfisare, '' '' + p.Cod, '''') END + ''</b>''
180 + ISNULL(''<br><b>Brand: '' + brand.BrandName + ''</b>'', '''')
181 + COALESCE(''<br><b>Cod: '' + ocp.CodProdusFabricant
182 + CASE WHEN ISNULL(ocp.CodProdusFabricant, '''') <> ISNULL(ocp.CodProdusFurnizor, '''')
183 THEN ISNULL(ocp.CodProdusFurnizor, '''')
184 ELSE ''''
185 END + ''</b>'', ''<br><b>Cod: '' + p.Cod + ''</b>'', '''')
186 + ISNULL(''<br><b>Producator: '' + COALESCE(fab.DenumireFabricant,furnimpl.denumireunitate,vp.DenumireUnitate) + ''</b>'', '''') + ISNULL(''<br>'' + ocp.Explicatii1, '''')
187 + CASE WHEN ISNULL(oc.Echipamente, 0) = 1
188 THEN ISNULL(''<br><b>'' + A.Asset + ''</b>'', '''')
189 + ISNULL('' <b>'' + A.SerieIntrare + ''</b>'','''')
190 ELSE '''' END
191 + ISNULL(''<br>''+ocp.Descriere,'''')
192 --+ COALESCE(''<br>'' + p.Descriere, ''<br>'' + ocp.Descriere, '''')
193 + ISNULL(''<br>'' + ocp.Explicatii2, '''')
194 + ISNULL(''<br><b>Garantie: </b>'' + CAST(ISNULL(ocp.LuniGarantie, p.luniGarantie) AS NVARCHAR(20)) + '' Luni'',
195 '''') + ISNULL(''<br><b>Termen livrare: </b>'' + ocp.TermenLivrare, ''''),CHAR(10),''<br>'') AS Product
196
197 , REPLACE(
198 ''<b>'' + coalesce(ocp.denumireprodusafisare, ocp.DenumireProdus, p.DenumireProdus)
199 + ''</b>''
200 + ISNULL(''<br><b>Brand: '' + brand.BrandName + ''</b>'', '''')
201 + COALESCE(''<br><b>Cod: ''
202 + ocp.CodProdusFabricant
203 + CASE WHEN ISNULL(ocp.CodProdusFabricant, '''') <> ISNULL(ocp.CodProdusFurnizor, '''')
204 THEN ISNULL(ocp.CodProdusFurnizor, '''')
205 ELSE ''''
206 END + ''</b>'',
207 ''<br><b>Cod: '' + p.Cod + ''</b>'', '''')
208 + ISNULL(''<br><b>Producator: '' + isnull(fab.DenumireFabricant,furnimpl.denumireunitate) + ''</b>'', '''') --+ ISNULL(''<br>'' + ocp.Explicatii1, '''')
209 + CASE WHEN ISNULL(oc.Echipamente, 0) = 1
210 THEN ISNULL(''<br><b>'' + A.Asset + ''</b>'', '''')
211 + ISNULL('' <b>'' + A.SerieIntrare + ''</b>'','''')
212 ELSE '''' END
213 +ISNULL(''<br>''+ocp.Descriere,'''')
214 --+ COALESCE(''<br>'' + p.Descriere, ''<br>'' + ocp.Descriere, '''')
215 + ISNULL(''<br>'' + ocp.Explicatii2, '''')
216 + ISNULL(''<br><b>Garantie: </b>'' + CAST(ISNULL(ocp.LuniGarantie, p.luniGarantie) AS NVARCHAR(20)) + '' Luni'',
217 '''') + ISNULL(''<br><b>Termen livrare: </b>'' + ocp.TermenLivrare, ''''),CHAR(10),''<br>'') AS ProductFaraExplicatii1
218
219 , isnull(p.cod,'' '') + '' '' +p.DenumireProdus as ProductCuCod
220 , um.Um AS unit
221 , isnull(ocp.Um,um.UM) AS UM
222 , ISNULL(trsum.Denumire,isnull(ocp.Um,um.UM)) UMTransl
223
224 , ocp.Cant AS OfferQuantity
225 , CASE WHEN ISNULL(oc.PretTva,0)=0
226 THEN ISNULL(ocp.PretValutaRedus, ocp.PretValuta)
227 ELSE ISNULL(ocp.PretValutaRedus, ocp.PretValuta)*(1+COALESCE(ocp.Tva,t.Tva,0)/100)
228 END
229 AS OfferPriceCurrency
230 , CASE WHEN ISNULL(oc.PretTva,0)=0
231 THEN ISNULL(ocp.PretValuta,0)
232 ELSE ISNULL(ocp.PretValuta,0)*(1+COALESCE(ocp.Tva,t.Tva,0)/100)
233 END AS OfferPriceCurrencyStandard
234 , inv.NumarZecimale(@setare247 , CASE WHEN ISNULL(oc.PretTva,0)=0 then ocp.PretValutaRedus
235 ELSE ocp.PretValutaRedus*(1+COALESCE(ocp.Tva,t.Tva,0)/100)
236 END) AS OfferPriceCurrencyRedus
237 , ocp.Cant * CASE WHEN ISNULL(oc.PretTva,0)=0
238 THEN ISNULL(ocp.PretValutaRedus, ocp.PretValuta)
239 ELSE ISNULL(ocp.PretValutaRedus, ocp.PretValuta)*(1+COALESCE(ocp.Tva,t.Tva,0)/100)
240 END AS ValoareOferta
241 , CASE WHEN ocp.ValutaId = ''ROL''
242 THEN ''RON''
243 ELSE ocp.ValutaId END AS OfferCurrency
244 , ocp.Discount AS OfferDiscount
245 , ocp.Adaos AS OfferFee
246 , tl.TipLivrare AS TipLivrare
247 , ocp.Explicatii1 AS OfferPositionsExplanation1
248 , ocp.Explicatii2 AS OfferPositionsExplanation2
249 , ocp.Descriere AS OfferPositionsDescription
250 , ISNULL(trsp.Denumire,ISNULL(OCP.DenumireProdusAfisare,P.DenumireProdus)) AS OfferPositionsProductTransl
251 , cfp.Cantitate AS ORDERQuantity
252 , inv.numarzecimale(@setare247, cfp.Pret) AS OrderPrice
253 , CASE WHEN cfp.ValutaId = ''ROL''
254 THEN ''RON''
255 ELSE cfp.ValutaId END AS OrderCurrency
256 , cfp.Explicatii1 AS OrderPositionsExplanation1
257 , cfp.Explicatii2 AS OrderPositionsExplanation2
258 , cf.NumarContract AS ContractNumber
259 , cf.DataContract AS DateOfContract
260 , cf.Observatii AS CONTRACTRemarks
261 , CASE WHEN p.Poza IS NULL THEN ''''
262 ELSE ''<img src="'' + @SYS_ROOTURL + ''/GlobalFileDownload.ashx?Type=4&id='' + CAST(p.ProdusId AS VARCHAR)
263 + ''&ImplementationName='' + @SYS_INSTANCE + ''" />''
264 END AS Prodimage
265 , COALESCE(aa.Valoaretva,oc.Valoare, 0) AS Total
266 , COALESCE(oc.Valoare,aa.Valoaretva, 0) AS TotalCap
267 , firm.DenumireUnitate AS Supplier
268 , ISNULL(cont.PrenumeContact, '''') + ISNULL('' '' + cont.NumeContact, '''') AS InAtentia
269 , cont.Titlu
270 , cont.NumeContact
271 , cont.Fax
272 , cont.Departament AS DepartamentContact
273 , ISNULL(cont.Email1, cont.Email2) as EmailClient
274 , Coalesce(cont.Telefon1, cont.Telefon2, cont.Mobil) AS Telefon
275
276 , isnull(ocp.CodProdusAfisare,p.Cod) AS ProductCode
277 , p.Descriere AS ProductDescription
278 , ocp.TermenLivrare AS ProductDelivery
279 -- , oc.TermenPlata as ModPlata
280 , tipdoc.Observatii AS ObservatiiTipDoc
281 , ISNULL(contactAgent.Titlu + '' '', '''') + ISNULL(contactAgent.PrenumeContact, '''') + ISNULL('' ''
282 + contactagent.Numecontact,
283 '''') AS ContactSupplier
284 , contactAgent.Fax AS FaxSupplier
285 , ISNULL(contactAgent.Telefon1, contactagent.Telefon2) AS TelefonSupplier
286 , ISNULL(contactAgent.Mobil, '''') AS MobilSupplier
287 , ISNULL(contactAgent.Email1, contactAgent.Email2) AS EmailSupplier
288 , isnull(contactagent.Functia, '''') as FunctieSupplier
289 , contactAgent.Departament AS DepartamentSupplier
290 , isnull(tipdoc.antet, f.Antet) as antet
291 , isnull(tipdoc.antet1, f.Antet1) as antet1
292 , isnull ( tipdoc.footer, f.Footer) as footer
293 , CASE WHEN p.Poza IS NOT NULL THEN 1
294 ELSE NULL
295 END AS PozaBool
296 , ocp.Cant AS CantitateComanda
297 , CASE WHEN std.StareId IS NOT NULL AND ISNULL(std.Respins,0) = 1 THEN @Refuzat
298 WHEN oc.DataValidare is null then ''<font color="red">'' + @Nevalidat + ''</font>''
299 WHEN oc.DataValidare Is not null THEN CASE WHEN ISNULL(oc.NecesitaAprobare,0)=1 and oc.DataAprobare IS null THEN @NeAprobat
300 WHEN ISNULL(oc.StareFinalizata,0) = 0 THEN @Validat + ISNULL('':'' + std.Stare,'''')
301 ELSE ''''
302 END
303 ELSE ''''
304 END as Stare
305 , coalesce(''Email: '' + cont.email1 + ''<br>'', ''Email: '' + cont.email2 + ''<br>'', '''')
306 + isnull(''Referitor la: ''+cofert.NumarCerere+''<br>''+''Data:''
307 + CONVERT(VARCHAR(10), cofert.DataCerere, 103),'''') as NumarCerere
308 , cofert.NumarCerere+''-''+''Data:''+ CONVERT(VARCHAR(10), cofert.DataCerere,103) as NrCerereOferta
309
310 , CONVERT(VARCHAR(10), cofert.DataCerere, 103) AS DataCerere
311 , coalesce(pp.PozaThumb2, pp.PozaThumb1, pp.Poza, p.Poza) as Poza
312 , CASE WHEN coalesce(pp.PozaThumb2, pp.PozaThumb1, pp.Poza, p.Poza) IS NULL THEN 0 ELSE 1 END as ArePoza
313 , CASE WHEN oc.ValutaId = ''ROL''
314 THEN ''RON''
315 ELSE oc.ValutaId END as ValutaId
316 , tip.TipDoc
317 , ISNULL(octp.Tip, '''') AS OfertaClientTipPozitie
318 , ISNULL(ocp.Grup, '''') AS Grup
319 , part.Adresa as AdresaSupplier
320 , part.Adresa as AdresaFurnizor
321 , pfirm.SiteWeb as WebSupplier
322 , mp.DenumireModPlata as Moddeplata
323 , ISNULL(oc.AdresaLivrare,'''') as AdresaLivrare
324 , ISNULL(@Contacte, ISNULL(cont.Titlu + '' '','''') + ISNULL(cont.PrenumeContact, '''') + ISNULL('' '' + cont.NumeContact, '''')) as InAtentiaContactelor
325 , tp.Denumire as TermenPlata
326 , coalesce(pro.DenumireProiect,uc.DenumireUnitate, l.Denumire) as DenumireProiect
327 , cofert.Tip as Tipcerereoferta
328 , p.ModAmbalare As ModAmbalare
329 , isnull(st.stoc,0) as Stoc
330 , ocp.Cant * CASE WHEN ISNULL(oc.PretTva,0)=0
331 THEN ocp.PretValuta
332 ELSE ocp.PretValuta*(1+COALESCE(ocp.Tva,t.Tva,0)/100)
333 END AS Valoarefarareducere
334 , CASE WHEN ocp.PretValutaRedus = 0
335 OR ocp.PretValutaRedus = ocp.PretValuta
336 OR ocp.PretValuta= 0
337 OR ocp.PretValutaRedus>ocp.PretValuta --rogeo
338 OR ISNULL(ocp.Discount,0) = 0
339 THEN 0
340 ELSE ROUND(ISNULL(ocp.Discount,(( ( ocp.PretValuta
341 - ocp.PretValutaRedus)
342 / ocp.PretValuta)*100)) , 2)
343 END AS Reducere
344 , CASE WHEN ocp.OfertaClientPoz2Id IS NULL
345 THEN (ocp.Cant * CASE WHEN ISNULL(oc.PretTva,0) = 0
346 THEN (ocp.PretValuta - ocp.PretValutaRedus)
347 ELSE (ISNULL(ocp.PretAmanunt,ocp.PretValuta * (1+tvaocp.Tva/100.00)) - ISNULL(ocp.PretAmanuntRedus,ocp.PretValutaRedus * (1+tvaocp.Tva/100.00)))
348 END )
349 ELSE 0 END as valoarediscount
350
351 , case when (ocp.Cant * ocp.PretValuta)<>0 then (ocp.Cant * (ocp.PretValuta-ocp.PretValutaRedus))/(ocp.Cant * ocp.PretValuta)*100
352 else (ocp.Cant * ocp.PretValuta) END AS ProcentDiscount
353
354 , ((ocp.Cant * ISNULL(ocp.PretValutaRedus,ocp.PretValuta)*(COALESCE(ocp.Tva,t.Tva,0)/100))) as ValoareTVA
355 , ISNULL(oc.PretTva,0) as Prettva
356 , dbo.ConturiPartener(firm.PartenerID, @SYS_LANGID, oc.TipDocId) AS Cont_Furnizor
357
358 , REPLACE(''<b>'' + coalesce(ocp.DenumireProdusAfisare,ocp.DenumireProdus, p.DenumireProdus) + ''</b>''
359 + ISNULL(''<br>''+ocp.Descriere,'''')
360 --+ COALESCE(''<br>'' + p.Descriere, ''<br>'' + ocp.Descriere, '''')
361 + ISNULL(''<br>'' + ocp.Explicatii2, '''')
362 ,CHAR(10),''<br>'') AS Productfaracod
363 ,pcat.DenumireCategorieProdus
364 ,psubcat.DenumireSubcategorieProdus
365 ,case when isnull(uc.Adresa,'''')='''' then isnull(pc.Adresa,l.adresa) else uc.Adresa end Adresa
366 ,tara.DenumireTara
367 ,coalesce(uc.CodFiscal, pc.CodFiscal,l.codfiscal,'''') as CodFiscal
368 ,ISNULL(pc.NrRegComertului,'''') NrRegComertului
369 ,oc.TermenLivrare as TermenLivrare
370 , oc.TermenPlata as TermenPlataOC
371 , cofert.tip as CerereOferta
372 , ISNULL(''<br><b>'' + A.Asset + ''</b>'', '''') + ISNULL('' <b>'' +''SN:''+ A.SerieIntrare + ''</b>'','''') as DenumireAsset
373 , REPLACE(oc.attr1,''<br />'',''<br>'') as attr1
374 , oc.attr2 as attr2
375 , oc.attr3 as attr3
376 , oc.attr4 as attr4
377 , oc.attr5 as attr5
378 , ISNULL(p.Greutate, 0) AS Greutate
379 , (ocp.Cant)*ISNULL(P.Greutate,0) AS GreutateBruta
380 , ISNULL(ocp.LuniGarantie, p.luniGarantie) AS LuniGarantie
381 , ocp.TipAsociere
382 , CASE WHEN ISNULL(oc.PretTva,0)=0
383 THEN ISNULL(ocp.PretValuta,ocp.PretValutaRedus)
384 ELSE ISNULL(ocp.PretValuta,ocp.PretValutaRedus) *(1+COALESCE(ocp.Tva,t.Tva,0)/100)
385 END
386 AS OfferPriceCurrencyNeredus
387 , cle.CodConditieLivrareExterna
388 , cle.DenumireConditieLivrareExterna
389 , CAST(ISNULL(p.Generic,0) AS INT) AS Generic
390 , cont.Functia as FunctieContact
391 , isnull(ocp2.DenumireProdusAfisare,procp2.DenumireProdus) as DenumireMeniu
392 , ocp2.Cant as CantitateMeniu
393 ,CASE WHEN ISNULL(oc.PretTva,0)=0 THEN ISNULL(ocp2.PretValutaRedus,ocp2.PretValuta)
394 ELSE coalesce(ocp2.pretamanuntredus,ocp2.pretamanunt,ISNULL(ocp2.PretValutaRedus,ocp2.PretValuta)*(1+tvaocp2.Tva/100.00)) END AS PretMeniu
395 --, coalesce(ocp2.pretamanuntredus,ocp2.pretamanunt,ocp2.PretValutaRedus*(1+tvaocp2.Tva/100.00)) as PretMeniu
396 ,CASE WHEN ISNULL(oc.PretTva, 0) = 0 THEN ISNULL(ocp.PretValutaRedus,ocp.PretValuta)
397 ELSE COALESCE(ocp.PretAmanuntRedus,ocp.PretAmanunt,ISNULL(ocp.PretValutaRedus,ocp.PretValuta)*(1+t.Tva/100.00)) END AS PretFaraMeniu
398 , gp.DenumireGrupaV as GrupaProdus
399 , CASE WHEN ocp2.ValutaId = ''ROL''
400 THEN ''RON''
401 ELSE ocp2.ValutaId END as ValutaMeniu
402 , ocp.Cantitate2
403 , Isnull(p.TipItem,procp2.TipItem) as Tipitem
404 , convert(varchar(10),ocp2.DataLivrare,103) as DataLivrareMeniu
405 , convert(varchar(10),ocp2.DataLivrare,108) as OraLivrareMeniu
406 , ocp.OfertaClientPoz2Id
407 ,coalesce( oc.valoarecuTVA,oc.valoare+oc.valoaretva) as ValoarecuTVA
408 ,isnull(isnull(ocp.PretAmanuntredus,ocp.pretamanunt),ISNULL(ocp.PretValutaRedus, ocp.PretValuta)*(1+COALESCE(ocp.Tva,t.Tva,0)/100)) as PretAmanunt
409 , ISNULL(ocp.PretValutaRedus, ocp.PretValuta) AS PretAmanunt2
410 , zisapt.ziua as ZiuaLivrare
411 , usun.DenumireUnitate as UserCreare
412 , ISNULL(usco.Telefon1, usco.Telefon2) as TelefonUserCreare
413 , ISNULL(usco.Email1, usco.Email2) as EmailUserCreare
414 , scont.Mobil as MobilCap
415 , ISNULL(scont.PrenumeContact, '''') + ISNULL('' '' + scont.NumeContact, '''') AS ContactCap
416 , ISNULL(ass.Asset,'''') + ISNULL('' '' + ass.SerieIntrare,'''') as Asset
417 , ISNULL(ass.SerieIntrare,'''') as SerieAsset
418 , ISNULL(A.Asset,'''') + ISNULL('' '' + A.SerieIntrare,'''') as AssetPoz
419 , REPLACE(''<b>'' + COALESCE(ocp.DenumireProdusAfisare, ocp.DenumireProdus, p.DenumireProdus, '''') + ''</b>''
420 + ISNULL(''<br><b>Brand: '' + brand.BrandName + ''</b>'', '''')
421 + COALESCE(''<br><b>Cod: '' + ocp.CodProdusFabricant
422 + CASE WHEN ISNULL(ocp.CodProdusFabricant, '''') <> ISNULL(ocp.CodProdusFurnizor, '''')
423 THEN ISNULL(ocp.CodProdusFurnizor, '''')
424 ELSE ''''
425 END + ''</b>'', ''<br><b>Cod: '' + p.Cod + ''</b>'', '''')
426 + ISNULL(''<br><b>Producator: '' + COALESCE(fab.DenumireFabricant,furnimpl.denumireunitate,vp.DenumireUnitate) + ''</b>'', '''') + ISNULL(''<br>'' + ocp.Explicatii1, '''')
427 + CASE WHEN ISNULL(oc.Echipamente, 0) = 1
428 THEN ISNULL(''<br><b>'' + A.Asset + ''</b>'', '''')
429 + ISNULL('' <b>'' + A.SerieIntrare + ''</b>'','''')
430 ELSE '''' END
431 + ISNULL(''<br>''+ocp.Descriere,'''')
432 --+ COALESCE(''<br>'' + p.Descriere, ''<br>'' + ocp.Descriere, '''')
433 + ISNULL(''<br>'' + ocp.Explicatii2, '''')
434 + ISNULL(''<br><b>Garantie: </b>'' + CAST(ISNULL(ocp.LuniGarantie, p.luniGarantie) AS NVARCHAR(20)) + '' Luni'',
435 '''') + ISNULL(''<br><b>Termen livrare: </b>'' + ocp.TermenLivrare, ''''),CHAR(10),''<br>'') AS ProdusFaraCod
436 , REPLACE(''<b>'' + COALESCE(ocp.DenumireProdusAfisare, ocp.DenumireProdus, p.DenumireProdus, '''') + ''</b>''
437 + ISNULL(''<br><b>Brand: '' + brand.BrandName + ''</b>'', '''')
438 /*+ COALESCE(''<br><b>Cod: '' + ocp.CodProdusFabricant
439 + CASE WHEN ISNULL(ocp.CodProdusFabricant, '''') <> ISNULL(ocp.CodProdusFurnizor, '''')
440 THEN ISNULL(ocp.CodProdusFurnizor, '''')
441 ELSE ''''
442 END + ''</b>'', ''<br><b>Cod: '' + p.Cod + ''</b>'', '''')*/
443 + ISNULL(''<br><b>Producator: '' + COALESCE(fab.DenumireFabricant,furnimpl.denumireunitate,vp.DenumireUnitate) + ''</b>'', '''') + ISNULL(''<br>'' + ocp.Explicatii1, '''')
444 + CASE WHEN ISNULL(oc.Echipamente, 0) = 1
445 THEN ISNULL(''<br><b>'' + A.Asset + ''</b>'', '''')
446 + ISNULL('' <b>'' + A.SerieIntrare + ''</b>'','''')
447 ELSE '''' END
448 --+ ISNULL(''<br>''+ocp.Descriere,'''')
449 --+ COALESCE(''<br>'' + p.Descriere, ''<br>'' + ocp.Descriere, '''')
450 + ISNULL(''<br>'' + ocp.Explicatii2, '''')
451 + ISNULL(''<br><b>Garantie: </b>'' + CAST(ISNULL(ocp.LuniGarantie, p.luniGarantie) AS NVARCHAR(20)) + '' Luni'',
452 '''') + ISNULL(''<br><b>Termen livrare: </b>'' + ocp.TermenLivrare, ''''),CHAR(10),''<br>'') AS ProdusFaraDescriere
453 , REPLACE(''<b>'' + COALESCE(ocp.DenumireProdusAfisare, ocp.DenumireProdus, p.DenumireProdus, '''') + ''</b>''
454 + ISNULL(''<br><b>Brand: '' + brand.BrandName + ''</b>'', '''')
455 /*+ COALESCE(''<br><b>Cod: '' + ocp.CodProdusFabricant
456 + CASE WHEN ISNULL(ocp.CodProdusFabricant, '''') <> ISNULL(ocp.CodProdusFurnizor, '''')
457 THEN ISNULL(ocp.CodProdusFurnizor, '''')
458 ELSE ''''
459 END + ''</b>'', ''<br><b>Cod: '' + p.Cod + ''</b>'', '''')*/
460 --+ ISNULL(''<br><b>Producator: '' + COALESCE(fab.DenumireFabricant,furnimpl.denumireunitate,vp.DenumireUnitate) + ''</b>'', '''') + ISNULL(''<br>'' + ocp.Explicatii1, '''')
461 + CASE WHEN ISNULL(oc.Echipamente, 0) = 1
462 THEN ISNULL(''<br><b>'' + A.Asset + ''</b>'', '''')
463 + ISNULL('' <b>'' + A.SerieIntrare + ''</b>'','''')
464 ELSE '''' END
465 --+ ISNULL(''<br>''+ocp.Descriere,'''')
466 --+ COALESCE(''<br>'' + p.Descriere, ''<br>'' + ocp.Descriere, '''')
467 + ISNULL(''<br>'' + ocp.Explicatii2, '''')
468 + ISNULL(''<br><b>Garantie: </b>'' + CAST(ISNULL(ocp.LuniGarantie, p.luniGarantie) AS NVARCHAR(20)) + '' Luni'',
469 '''') + ISNULL(''<br><b>Termen livrare: </b>'' + ocp.TermenLivrare, ''''),CHAR(10),''<br>'') AS ProdusFaraProducator
470 , usco.Fax AS FaxUserCreare
471 , usco.Mobil AS MobilUserCreare
472 , usco.Functia AS FunctieUserCreare
473 , ISNULL(@ValoareDiscountMeniu,0) AS ValoareDiscountMeniu
474 , vwUnitati_Furnizor.CodFiscal AS CodFiscal_Furnizor
475 , vwUnitati_Furnizor.NrRegComert AS NrRegCom_Furnizor
476 , oc.ObservatiiInterne AS ObservatiiInterneCap
477 ,isnull(p.cod,'' '') + '' '' +COALESCE(ocp.DenumireProdusAfisare, ocp.DenumireProdus, p.DenumireProdus, '''') + ISNULL(''<br><b>Luni Garantie: </b>'' + CAST(ISNULL(CAST(ocp.LuniGarantie AS VARCHAR(100)), p.CodProdusFabricant) AS NVARCHAR(20)),
478 '''') + ISNULL(''<br><b>Termen livrare: </b>'' + ocp.TermenLivrare, '''') as ProductCuCGT
479 , isnull(cast (oc.DataValidare as nvarchar(10)),''-'') as DataValidare
480 , ocp.Ordine
481 --, aa.PretLista AS PretLista
482 --, aa.valutalistaid as valutalistaid
483 ,ocp.valutalistaid as valutalistaid
484 ,inv.NumarZecimale(@setare247 , ocp.PretLista * ISNULL(NULLIF(pum.Factor,0),1)) AS PretLista
485 , CASE WHEN @Setare370 = ''0''
486 THEN CASE WHEN ISNULL(@Setare425,0) = 1
487 THEN CASE WHEN adr_furnizor_unitate.AdresaPartenerPrefixata = adr_furnizor_unitate.AdresaUnitatePrefixata
488 THEN ''''
489 ELSE adr_furnizor_unitate.AdresaUnitatePrefixata
490 END
491 ELSE CASE WHEN adr_furnizor_unitate.AdresaPartenerPrefixata = adr_furnizor_unitate.AdresaUnitatePrefixata
492 THEN ''''
493 ELSE adr_furnizor_unitate.AdresaUnitatePrefixata
494 END
495 END
496 ELSE @Setare370
497 END AS Furnizor_AdresaPunctLucru
498 ,p.BucatiBAX
499 ,client.SuplimentareCursValutar
500 , ''<b>''+isnull(p.cod,'' '') + '' '' +p.DenumireProdus + ''</b>'' + CASE WHEN ocp.Explicatii1 is null then '''' ELSE ''<br>'' + ocp.Explicatii1 END
501 + CASE WHEN ocp.Explicatii2 is null then '''' ELSE ''<br>'' + ocp.Explicatii2 END
502 + CASE WHEN p.Descriere IS NULL THEN '''' ELSE ''<br>'' + p.Descriere END
503 + CASE WHEN p.ModAmbalare IS NULL THEN '''' ELSE ''<br>'' + p.ModAmbalare END
504 AS ProdusCuExplicatiiSiDescriere
505 INTO #Temp
506
507 FROM dbo.tblOferteClienti oc (nolock)
508 left join dbo.tblproiecte pro (nolock) on pro.proiectid = oc.ProiectId
509 INNER JOIN dbo.tblOferteClientiPoz ocp (nolock)
510 ON ocp.OfertaClientId = oc.OfertaClientId
511 LEFT JOIN (
512 SELECT SUM(ROUND(ocpp.Cant * CASE WHEN ISNULL(ccc.PretTva,0)=0
513 THEN ISNULL(ocpp.PretValutaRedus, ocpp.PretValuta)
514 ELSE ISNULL(ocpp.PretValutaRedus, ocpp.PretValuta)*(1+COALESCE(ocpp.Tva,ppt.Tva,0)/100)
515 END, @Setare377)) as ValoareTva
516 ,ocpp.OfertaClientId
517 --,max(ocpp.valutalistaid) as valutalistaid
518 --,max(inv.NumarZecimale(@setare247 , ocpp.PretLista * ISNULL(NULLIF(um.Factor,0),1))) AS PretLista
519 FROM dbo.tblOferteClientiPoz ocpp (nolock)
520 inner join dbo.tbloferteclienti ccc (nolock) on ccc.ofertaclientid=ocpp.ofertaclientid
521 left join dbo.tblProduse pp (nolock) on pp.ProdusId=ocpp.ProdusId
522 left join dbo.tblProduseUm um (nolock) on pp.ProdusId = um.ProdusId and ocpp.umid=um.umid
523 left join dbo.tblTVA ppt (nolock) on ppt.TvaId=pp.TvaId
524 WHERE ocpp.OfertaClientId=@OfertaClientID
525 AND ocpp.OferteClientiTipPozId is NULL
526 AND ocpp.ParentOfertaClientPozId IS NULL
527 GROUP by ocpp.OfertaclientId) aa on aa.OfertaClientId=oc.OfertaClientId
528
529 LEFT JOIN dbo.tblProduse p (nolock)
530 ON p.ProdusId = ocp.ProdusId
531 left join dbo.tblProduseUm pum (nolock)
532 on p.ProdusId = pum.ProdusId
533 and ocp.umid=pum.umid
534
535 left join dbo.tblprodusedivizii pd (nolock)
536 on pd.produsid = p.produsid and pd.divizieid = @sys_divid
537 left join dbo.tblcategorieprodus pcat (nolock)
538 on pd.categorieprodusid = pcat.categorieprodusid and pcat.divizieid = @sys_divid
539 left join dbo.tblsubcategorieprodus psubcat (nolock)
540 on pd.subcategorieprodusid = psubcat.subcategorieprodusid and psubcat.DivizieId = @sys_divid
541
542 LEFT JOIN dbo.tblBrand brand (nolock)
543 on brand.BrandId=p.BrandId
544 LEFT JOIN tblProdusePoze pp (nolock)
545 ON p.ProdusId = pp.ProdusId and pp.Principala =1
546 LEFT JOIN dbo.tblTva t (nolock) on t.TvaId=p.TvaId
547 LEFT JOIN dbo.vwUnitati uc (nolock)
548 ON uc.UnitateId = oc.ClientId
549 LEFT JOIN dbo.vwPartener pc (nolock)
550 ON uc.PartenerID = pc.PartenerID
551
552 LEFT JOIN dbo.tblLead l (nolock)
553 ON l.LeadId = oc.LeadID
554 LEFT JOIN dbo.tblTipLivrare tl (nolock)
555 ON oc.TipLivrareId = tl.TipLivrareId
556 LEFT JOIN dbo.ContracteFurnizoriPoz cfp (nolock)
557 ON ocp.ContractFurnizorPozId = cfp.ContractFurnizorPozId
558 LEFT JOIN dbo.ContracteFurnizori cf (nolock)
559 ON cf.ContractFurnizorId = cfp.ContractFurnizorId
560 LEFT JOIN dbo.tblFirme f (nolock)
561 ON oc.FirmaId = f.FirmaId
562 LEFT JOIN dbo.vwUnitati firm (nolock)
563 ON firm.UnitateId = f.FirmaId
564 LEFT JOIN dbo.vwUnitatiAdrese adr_firm (NOLOCK)
565 ON adr_firm.UnitateId = firm.UnitateId
566 LEFT JOIN dbo.vwPartener part (nolock)
567 ON part.PartenerId=firm.PartenerId
568 LEFT JOIN dbo.tblUM um (nolock)
569 ON isnull(p.UmId, ocp.umid) = um.UMId
570 LEFT JOIN dbo.tblTermenePlata tp (nolock)
571 ON tp.TermenPlataId = oc.TermenPlataID
572 LEFT JOIN dbo.tblContact cont (nolock)
573 ON cont.ContactID = COALESCE(oc.ContactId,(select top 1 val from [dbo].[SplitString](oc.ContactIds,'','')),uc.ContactDefaultId)
574 LEFT JOIN inv.TipDoc tipdoc (nolock)
575 ON oc.TipDocId = tipdoc.TipDocId
576 LEFT JOIN dbo.vwUnitati agent (nolock)
577 ON agent.UnitateId = oc.AgentId
578 LEFT JOIN dbo.tblContact contactAgent (nolock)
579 ON contactAgent.ContactID = agent.ContactDefaultId
580 LEFT JOIN dbo.tblFabricanti fab (nolock)
581 ON fab.FabricantId = p.FabricantId
582 LEFT JOIN dbo.tblCerereOferta cofert (nolock)
583 ON cofert.CerereOfertaId = oc.CerereOfertaId
584 LEFT JOIN inv.TipDoc tip (nolock)
585 ON tip.TipDocId = oc.TipDocId
586 LEFT JOIN dbo.tblOferteClientiTipPoz octp (nolock)
587 ON ocp.OferteClientiTipPozId = octp.OferteClientiTipPozId
588 LEFT JOIN dbo.tblAssets A (nolock)
589 ON ocp.AssetId = A.AssetId
590 Left Join dbo.vwPartener pfirm (nolock)
591 ON pfirm.PartenerID=firm.PartenerId
592 LEFT JOIN dbo.tblModPlata mp (nolock)
593 ON mp.ModPlataId=oc.ModPlataId
594 LEFT JOIN dbo.vwUnitati furnimpl (nolock)
595 ON furnimpl.UnitateId=ocp.FurnizorId
596 LEFT JOIN #Stoc st
597 on st.produsid=p.produsid
598 LEFT JOIN dbo.tblStariDoc std (nolock)
599 on std.StareId = oc.StareId
600 LEFT JOIN dbo.tblDataTranslations trsp (nolock)
601 ON trsp.LanguageId = @LanguageId AND trsp.Tip = 7 AND trsp.Id =OCP.ProdusId
602 LEFT JOIN dbo.tblDataTranslations trsum (nolock)
603 ON trsum.LanguageId = @LanguageId AND trsum.Tip = 6 AND trsum.Id = p.UmId
604 LEFT JOIN dbo.tblTara tara (nolock)
605 ON tara.TaraID=isnull(uc.TaraID,l.TaraID)
606 LEFT JOIN dbo.tblConditiiLivrareExterna cle (nolock)
607 ON oc.ConditieLivrareExternaId = cle.ConditieLivrareExternaId
608 LEFT JOIN dbo.tblOferteClientiPoz2 ocp2 (nolock)
609 ON ocp2.OfertaClientPoz2Id=ocp.OfertaClientPoz2Id
610 LEFT JOIN dbo.tblproduse procp2 (nolock)
611 ON procp2.ProdusId=ocp2.ProdusId
612 LEFT JOIN dbo.tblGrupeVProdus gp (nolock)
613 on gp.GrupaVProdusId=pd.GrupaVProdusId
614 LEFT JOIN dbo.tbltva tvaocp (nolock) on tvaocp.tvaId=p.TvaId
615 LEFT JOIN dbo.tbltva tvaocp2 (nolock) on tvaocp2.tvaId=procp2.TvaId
616 LEFT JOIN syserp.ERP.weekdays(''ro'') zisapt on zisapt.ziuaid=datepart(weekday,ocp2.DataLivrare)
617 LEFT JOIN dbo.vwUnitati vp (nolock) on vp.UnitateID = p.ProducatorId
618 LEFT JOIN dbo.tblusers us (nolock)on oc.UserCreareId=us.UserId
619 LEFT JOIN dbo.vwunitati usun (nolock) on us.UnitateId=usun.UnitateID
620 LEFT JOIN dbo.tblContact usco (nolock) ON usco.ContactID=usun.ContactDefaultId
621 LEFT JOIN dbo.tblContact scont (nolock)ON scont.ContactID = oc.ContactId
622 LEFT JOIN tblAssets ass (NOLOCK)
623 ON oc.AssetId = ass.AssetId
624 LEFT OUTER JOIN dbo.vwUnitati vwUnitati_Furnizor (NOLOCK)
625 ON oc.FirmaId = vwUnitati_Furnizor.UnitateId
626 LEFT JOIN dbo.vwUnitatiAdrese adr_furnizor_unitate (NOLOCK)
627 ON adr_furnizor_unitate.UnitateId = vwUnitati_Furnizor.UnitateId
628 LEFT JOIN dbo.tblClienti client (nolock)
629 ON client.ClientId = oc.ClientId and client.DivizieId=oc.DivizieId and client.firmaid=oc.FirmaId
630
631
632 WHERE oc.OfertaClientId = @OfertaClientID
633 AND (ISNULL(oc.TiparireDetalii, 0) = 1 OR ocp.ParentOfertaClientPozId IS NULL)
634 ORDER BY ocp.Ordine
635 , ISNULL(ocp.DenumireProdus, p.DenumireProdus)
636
637 DECLARE @ValoareTotalaDiscount DECIMAL(18,5)
638 , @TotalGeneral DECIMAL(18,5)
639 , @TotalPretFaraMeniu DECIMAL(18,5)
640
641 SELECT @ValoareTotalaDiscount = SUM(valoarediscount)
642 FROM #Temp
643 WHERE OfertaClientPoz2Id IS NULL
644
645
646
647 SELECT @TotalGeneral = ISNULL(@TotalGeneral, 0) + SUM( ROUND(CantitateMeniu*PretMeniu, 2) )
648 -- , @TotalPretFaraMeniu = SUM(PretFaraMeniu)
649 FROM (SELECT distinct ofertaclientpoz2id,
650 PretMeniu
651 , CantitateMeniu
652
653 FROM #Temp
654 where OfertaClientPoz2Id is not null
655 ) a
656
657 SELECT @TotalPretFaraMeniu=SUM(round(offerquantity*pretfarameniu,2))
658
659 FROM (SELECT --DISTINCT OfertaClientPoz2Id
660 pretfarameniu
661 , offerquantity
662 FROM #Temp
663 where OfertaClientPoz2Id is null ) a
664
665 SELECT @TotalGeneral=ISNULL(@TotalGeneral,0)+isnull(@TotalPretFaraMeniu,0)
666
667 SELECT ISNULL(@TotalGeneral + ISNULL(@ValoareTotalaDiscount, 0) + ISNULL(@ValoareDiscountMeniu, 0), Total) AS TotalGeneral
668 , @TotalGeneral AS ValoareOfertaTotal
669 , @TotalPretFaraMeniu AS TotalFaraMeniu
670 , t.*
671 FROM #Temp t
672 ORDER BY t.Ordine,
673 t.Product
674
675
676 --DECLARE @GrandTotalOffer DECIMAL(18 , 5)
677
678 --SELECT @GrandTotalOffer = SUM(TotalOffer)
679 --FROM #OfertaClient
680
681
682 --SELECT *
683 -- , @GrandTotalOffer AS GrandTotalOffer
684 --FROM #OfertaClient
685 END
686
687
688drop table #Stoc
689drop table #temp
690;
691; '
692
693
694DECLARE @sql NVARCHAR(MAX);
695DECLARE @execute_sql NVARCHAR(MAX);
696DECLARE @database_name NVARCHAR(500);
697
698WHILE EXISTS (SELECT TOP 1 DBNAME FROM @tbl_databases)
699BEGIN
700 UPDATE @spcodestoexecute SET executed = 0
701
702 SELECT TOP 1
703 @database_name = DBNAME
704 FROM @tbl_databases
705 ORDER BY DBNAME
706
707 PRINT @database_name
708
709 WHILE EXISTS (SELECT TOP 1 keycol FROM @spcodestoexecute WHERE executed = 0 AND isnull(issys,0) = 0)
710 BEGIN
711 DECLARE @keycol int
712
713 SET @keycol = null
714 SET @sql = null
715
716 SELECT TOP 1
717 @keycol = keycol,
718 @sql = code
719 FROM @spcodestoexecute
720 WHERE executed = 0
721 AND isnull(issys,0) = 0
722 ORDER BY keycol
723 SET @execute_sql = 'EXEC ' + QUOTENAME(@database_name) + '.[dbo].[sp_executesql] @sql';
724
725 EXEC [dbo].[sp_executesql] @execute_sql, N'@sql NVARCHAR(MAX)', @sql;
726
727 UPDATE @spcodestoexecute
728 SET executed = 1
729 WHERE keycol = @keycol
730 END
731
732 DELETE FROM @tbl_databases
733 WHERE DBNAME = @database_name
734
735END
736
737IF EXISTS (SELECT TOP 1 keycol FROM @spcodestoexecute WHERE issys = 1)
738BEGIN
739 SET @database_name = 'SysErp'
740 PRINT @database_name
741
742 WHILE EXISTS (SELECT TOP 1 keycol FROM @spcodestoexecute WHERE executed = 0 AND issys = 1)
743 BEGIN
744
745 SET @keycol = null
746 SET @sql = null
747
748 SELECT TOP 1
749 @keycol = keycol,
750 @sql = code
751 FROM @spcodestoexecute
752 WHERE executed = 0
753 AND issys = 1
754 ORDER BY keycol
755
756 SET @execute_sql = 'EXEC ' + QUOTENAME(@database_name) + '.[dbo].[sp_executesql] @sql';
757
758 EXEC [dbo].[sp_executesql] @execute_sql, N'@sql NVARCHAR(MAX)', @sql;
759
760 UPDATE @spcodestoexecute
761 SET executed = 1
762 WHERE keycol = @keycol
763 END
764END