· 4 years ago · Apr 06, 2021, 12:54 PM
1
2create procedure up_os_pend
3 @dt_ini datetime,
4 @dt_fin datetime,
5 @tipo_os char(1), -- L - veiculos Liberados, O - veiculos na Oficina, T - Todos
6 @situacao_os char(2), -- P - Pendentes emissao NF, E - Serviço Encerrado, A - Serviço em Andamento, T - Todos
7 @tp_agrupamento char(1) = 'N' -- N - normal (default), T - por Tipo de OS
8
9as
10
11/*
12 Traz todas as O.S. que estao pendentes e que foram abertas entre
13 @dt_ini e @dt_fin.
14*/
15
16SET NOCOUNT ON
17SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
18
19create index xk1tmp_tpos on #tmp_tpos (tpos_cd)
20create index xk1tmp_rec on #tmp_rec (fun_cd)
21
22declare @select varchar(5000)
23
24/* Coloquei uma cr¡tica para pegar o valor das pe‡as, caso a OS possua or‡amento,
25 pega da pr¢pria requisi‡ao de pe‡as, senao pega os valores atualizados das pe‡as */
26
27create table #temp1 (
28 os_nr char(7) not null,
29 tpos_cd char(2) null,
30 tpos_ds varchar(35) null,
31 os_dtaber datetime null,
32 os_hraber int null,
33 os_dtprom datetime null,
34 os_hrprom int null,
35 fun_rec char(3) null,
36 fun_nmguerra varchar(15) null,
37 cg_cd char(7) null,
38 cg_nm varchar(45) null,
39 cg_fone1 char(14) null,
40 cg_cgccpf char(14) null,
41 ve_nr char(7) null,
42 ve_placa char(8) null,
43 uf_placa char(2) null,
44 pad_cd char(3) null,
45 os_dtlib datetime null,
46 os_hrlib int null,
47 os_prisma char(4) null,
48 mod_ds char(35) null,
49 os_dtlavagem datetime null,
50 os_hrlavagem int null,
51 tes_dtocorr datetime null,
52 tes_hrocorr int null,
53 osm_dtfim datetime null,
54 osm_hrfim int null,
55 ossp_dtocor datetime null,
56 ossp_hrocor int null,
57 tot_servicos float not null,
58 tot_pecas float not null,
59 ve_chassi char(30) null,
60 ve_nfrota char(17) null,
61 ostp_nfpend char(1) null)
62
63select @select = 'insert into #temp1
64SELECT sof_os.os_nr,
65 sof_ostp.tpos_cd,
66 tab_tpos.tpos_ds,
67 sof_os.os_dtaber,
68 sof_os.os_hraber,
69 sof_os.os_dtprom,
70 sof_os.os_hrprom,
71 tab_fun.fun_cd,
72 tab_fun.fun_nmguerra,
73 ger_ve.cg_cd,
74 ger_cg.cg_nm,
75 ger_cg.cg_fone1,
76 ger_cg.cg_cgccpf,
77 sof_os.ve_nr,
78 ger_ve.ve_placa,
79 ger_ve.uf_placa,
80 tab_mod.pad_cd,
81 sof_os.os_dtlib,
82 sof_os.os_hrlib,
83 sof_os.os_prisma,
84 tab_mod.mod_ds,
85 sof_os.os_dtlavagem,
86 sof_os.os_hrlavagem,
87 tes_dtocorr = (SELECT max(sof_tes.tes_dtocorr)
88 FROM sof_tes (nolock)
89 WHERE sof_tes.os_nr = sof_os.os_nr and
90 sof_tes.tes_parecer = ''O'' and
91 sof_tes.tes_dtcanc is null),
92 tes_hrocorr = (SELECT max(sof_tes.tes_hrocorr)
93 FROM sof_tes (nolock)
94 WHERE sof_tes.os_nr = sof_os.os_nr and
95 sof_tes.tes_parecer = ''O'' and
96 sof_tes.tes_dtcanc is null),
97 osm_dtfim = case when not exists (select os_nr from sof_oss (nolock) where sof_oss.os_nr = sof_ostp.os_nr and sof_oss.tpos_cd = sof_ostp.tpos_cd and oss_situac = '' '') then
98 (SELECT max(sof_osm.osm_dtfim)
99 FROM sof_osm (nolock), sof_oss (nolock)
100 WHERE sof_osm.os_nr = sof_os.os_nr and
101 sof_osm.os_nr = sof_oss.os_nr and
102 sof_oss.os_nr = sof_os.os_nr and
103 sof_oss.oss_situac <> ''C'' and
104 sof_oss.tpos_cd = sof_ostp.tpos_cd)
105 else null end,
106 osm_hrfim = convert(float,0.00),
107 ossp_dtocor = (SELECT max(sof_ossp.ossp_dtocor)
108 FROM sof_ossp (nolock), sof_oss (nolock)
109 WHERE sof_ossp.os_nr = sof_os.os_nr and
110 sof_ossp.os_nr = sof_oss.os_nr and
111 sof_oss.os_nr = sof_os.os_nr and
112 sof_oss.oss_situac <> ''C'' and
113 sof_oss.tpos_cd = sof_ostp.tpos_cd),
114 ossp_hrocor = convert(float,0.00),
115 tot_servicos = convert(float,0.00),
116 tot_pecas = convert(float,0.00),
117 ve_chassi = ger_ve.ve_chassi,
118 ve_nfrota = ger_ve.ve_nfrota ,
119 sof_ostp.ostp_nfpend
120 FROM ger_ve (nolock) , sof_os (nolock) , sof_ostp (nolock) , tab_fun (nolock) , ger_cg (nolock), tab_mod (nolock) , #tmp_tpos (nolock), tab_tpos (nolock), #tmp_mod
121 WHERE ( ger_cg.cg_cd = ger_ve.cg_cd ) and
122 ( ger_ve.ve_nr = sof_os.ve_nr ) and
123 ( sof_os.os_nr = sof_ostp.os_nr ) and
124 ( sof_ostp.fun_rec = tab_fun.fun_cd ) and
125 ( ger_ve.mod_cd = tab_mod.mod_cd ) and
126 ( tab_mod.mod_cd = #tmp_mod.mod_cd ) and
127 ( sof_os.os_dtaber >= '''+convert(char(10),@dt_ini,101)+''') and
128 ( sof_os.os_dtaber <= '''+convert(char(10),@dt_fin,101)+''') and
129 ( sof_ostp.tpos_cd = #tmp_tpos.tpos_cd ) and
130 ( ((sof_ostp.fun_rec in (select fun_cd from #tmp_rec) and (select count(*) from #tmp_rec) > 0) or ((select count(*) from #tmp_rec) = 0)) ) and
131 (tab_tpos.tpos_cd = #tmp_tpos.tpos_cd)'
132
133-- Só saem as com situação em branco se não solicitar as pendentes de emissão de NF
134
135If @situacao_os = 'P'
136 select @select = @select + ' and sof_ostp.ostp_situac = ''P'' '
137else
138 select @select = @select + ' and (sof_ostp.ostp_situac = ''P'' or sof_ostp.ostp_situac = '' '' ) '
139
140-- Separa os veículos liberados dos não liberados
141If @tipo_os <> 'T' begin
142 If @tipo_os = 'O'
143 select @select = @select + ' and sof_os.os_dtlib is null '
144 else
145 select @select = @select + ' and sof_os.os_dtlib is not null '
146End
147
148-- Separa as OS's com serviço encerrado das Os's em andamento
149
150If @situacao_os <> 'T' and @situacao_os <> 'P' begin
151 If @situacao_os = 'E'
152 select @select = @select + ' and not exists (select os_nr from sof_oss (nolock) where os_nr = sof_ostp.os_nr and tpos_cd = sof_ostp.tpos_cd and oss_situac = '' '')'
153 else
154 select @select = @select + ' and exists (select os_nr from sof_oss (nolock) where os_nr = sof_ostp.os_nr and tpos_cd = sof_ostp.tpos_cd and oss_situac = '' '')'
155End
156
157exec (@select)
158
159/* Atualizando os valores para as OS's que possuem or‡amento */
160
161update #temp1
162set tot_servicos = ( select isnull( sum( round((sof_oss.oss_vlunit * sof_oss.oss_qt) -
163 sof_oss.oss_vldesc ,2)), 0.00 )
164 from sof_oss (nolock)
165 where sof_oss.os_nr = #temp1.os_nr and
166 sof_oss.tpos_cd = #temp1.tpos_cd and
167 sof_oss.oss_cobra = 'S' and
168 ( ostp_nfpend is null or ostp_nfpend = 'S' ) and
169 sof_oss.oss_situac <> 'C'),
170 tot_pecas = (select isnull(sum((isnull(orp.orp_vlpd, 0) * rm.rm_qt) - round((isnull(orp.orp_vldesc, 0) / (orp.orp_qt * rm.rm_qt)), 2)) , 0.00)
171 from (select os_nr = sof_rm.os_nr, tpos_cd = sof_rm.tpos_cd,
172 pd_cd = sof_rm.pd_cd, rm_qt = sum(sof_rm.rm_qt)
173 from sof_rm
174 where sof_rm.os_nr = sof_or.os_nr and
175 sof_rm.tpos_cd = sof_or.tpos_cd and
176 sof_rm.rm_dtcanc is null and
177 sof_rm.rm_qt > 0
178 group by sof_rm.os_nr, sof_rm.tpos_cd, sof_rm.pd_cd) rm
179 inner join ger_pd (nolock) on (ger_pd.pd_cd = rm.pd_cd)
180 inner join (select os_nr = sof_orp.os_nr, pd_cd = sof_orp.pd_cd,
181 orp_vlpd = sum(sof_orp.orp_vlpd) / count(*),
182 orp_vldesc = sum(sof_orp.orp_vldesc),
183 orp_qt = sum(sof_orp.orp_qt)
184 from sof_orp
185 where sof_orp.os_nr = sof_or.os_nr and
186 sof_orp.orp_dtcanc is null and
187 sof_orp.orp_qt > 0
188 group by sof_orp.os_nr, sof_orp.pd_cd) orp on (orp.pd_cd = rm.pd_cd and
189 orp.os_nr = rm.os_nr)
190 inner join ger_pde (nolock) on (ger_pde.pd_cd = rm.pd_cd and
191 ger_pde.est_cd = dbo.fn_get_estoque_sof(rm.tpos_cd))
192 where (ostp_nfpend is null or ostp_nfpend = 'P'))
193from sof_or (nolock)
194where sof_or.os_nr = #temp1.os_nr and
195 sof_or.tpos_cd = #temp1.tpos_cd
196
197Update #temp1
198set tot_servicos = ( select isnull( sum( round((sof_oss.oss_vlunit) -
199 sof_oss.oss_vldesc,2) ), 0.00 )
200 from sof_oss (nolock)
201 where sof_oss.os_nr = #temp1.os_nr and
202 sof_oss.tpos_cd = #temp1.tpos_cd and
203 sof_oss.oss_cobra = 'S' and
204 ( ostp_nfpend is null or ostp_nfpend = 'S' ) and
205 sof_oss.oss_situac <> 'C'),
206
207
208 tot_pecas = ( select isnull( sum( (round ( (case when tpos_fatura = 'RR' then sof_rm.rm_vl
209 when tpos_fatura = 'PS' then pd_vlpublic
210 when tpos_fatura = 'PP' then ger_pd.pd_vlven
211 when tpos_fatura = 'PG' then ger_pd.pd_vlgar
212 when tpos_fatura = 'PR' then ger_pd.pd_vlaquis
213 when tpos_fatura = 'PC' then round(ger_pde.pd_premed,2)
214 end)* tpos_prfat/100,2)* rm_qt) - sof_rm.rm_vldesc) , 0.00 )
215 from sof_rm (nolock),ger_pd (nolock),tab_tpos (nolock),ger_pde (nolock)
216 where sof_rm.os_nr = #temp1.os_nr and
217 sof_rm.tpos_cd = #temp1.tpos_cd and
218 sof_rm.rm_dtcanc is null and
219 sof_rm.rm_qt > 0 and
220 ( ostp_nfpend is null or ostp_nfpend = 'P' ) and
221 ger_pd.pd_cd = sof_rm.pd_cd and
222 ger_pde.pd_cd = sof_rm.pd_cd and
223 ger_pde.est_cd = dbo.fn_get_estoque_sof(sof_rm.tpos_cd) and
224 tab_tpos.tpos_cd = sof_rm.tpos_cd )
225where not exists (select or_nr from sof_or (nolock) where sof_or.os_nr = #temp1.os_nr and sof_or.tpos_cd = #temp1.tpos_cd)
226
227/* Pegando a hora da £ltima marca‡ao de tempo */
228
229
230Update #temp1
231 set osm_hrfim = isnull((SELECT max(sof_osm.osm_hrfim)
232 FROM sof_osm (nolock), sof_oss (nolock)
233 WHERE sof_osm.os_nr = #temp1.os_nr and
234 sof_osm.os_nr = sof_oss.os_nr and
235 sof_oss.os_nr = #temp1.os_nr and
236 sof_oss.oss_situac <> 'C' and
237 ( ostp_nfpend is null or ostp_nfpend = 'S' ) and
238 sof_oss.tpos_cd = #temp1.tpos_cd and
239 sof_osm.osm_dtfim = #temp1.osm_dtfim),0.00)
240
241
242/* Pegando a hora da £ltima parada de servi‡o */
243
244Update #temp1
245 set ossp_hrocor = isnull((SELECT max(sof_ossp.ossp_hrocor)
246 FROM sof_ossp (nolock), sof_oss (nolock)
247 WHERE sof_ossp.os_nr = #temp1.os_nr and
248 sof_ossp.os_nr = sof_oss.os_nr and
249 ( ostp_nfpend is null or ostp_nfpend = 'S' ) and
250 sof_oss.os_nr = #temp1.os_nr and
251 sof_oss.oss_situac <> 'C' and
252 sof_oss.tpos_cd = #temp1.tpos_cd and
253 sof_ossp.ossp_dtocor = #temp1.ossp_dtocor),0.00)
254
255If @tp_agrupamento = 'N'
256begin
257
258 SELECT distinct
259 os_nr,
260 tpos_cd,
261 tpos_ds,
262 os_dtaber,
263 os_hraber,
264 os_dtprom,
265 os_hrprom,
266 fun_rec,
267 fun_nmguerra,
268 cg_cd,
269 cg_nm,
270 cg_fone1,
271 cg_cgccpf,
272 ve_nr,
273 ve_placa,
274 uf_placa,
275 pad_cd,
276 os_dtlib,
277 os_hrlib,
278 os_prisma,
279 mod_ds,
280 os_dtlavagem,
281 os_hrlavagem,
282 tes_dtocorr,
283 tes_hrocorr,
284 osm_dtfim,
285 osm_hrfim,
286 ossp_dtocor,
287 ossp_hrocor,
288 tot_servicos,
289 tot_pecas,
290 ve_chassi,
291 ve_nfrota,
292 dias_aberto = datediff(dd,os_dtaber,getdate())
293 from #temp1 (nolock)
294 order by fun_nmguerra ASC , os_dtaber ASC, os_nr ASC
295end
296else
297begin
298
299
300 SELECT distinct
301 os_nr,
302 tpos_cd,
303 fun_rec,
304 tot_servicos=sum(tot_servicos),
305 tot_pecas=sum(tot_pecas)
306 from #temp1 (nolock)
307 group by
308 os_nr,
309 tpos_cd,
310 fun_rec
311
312end
313
314SET TRANSACTION ISOLATION LEVEL READ COMMITTED