SQLize | PHPize | SQLtest

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_WH_ALERTA_FIFO_MAIS_ANTIGA_EM_CENTRAL_ITENS] AS begin IF object_id('tempdb..#body') is not null Begin Drop Table #body End declare @recipients varchar(max); declare @subject varchar(100); declare @from_address varchar(100); declare @body varchar(max); DECLARE @year varchar(10) = (SELECT year(getdate())) --Carrega o Ano. create table #body(id varchar(200), ALMOX varchar(5), PART_NUMBER_SPMKT varchar(max), LOCAL_SPMKT varchar(10), CLIENTE varchar(10), QTDE_SPMKT varchar(max), DATA_FIFO_SPMKT varchar(max), ALMOX2 varchar(5) PART_NUMBER_CE varchar(max), LOCAL_CENTRAL varchar(10), QTDE_CENTRAL varchar(max), DATA_FIFO_CE varchar(max)) insert into #body SELECT NEWID() New_ID, * FROM OPENQUERY ([BAAN], 'SELECT DISTINCT FRONT.Warehouse AS ALMOX ,FRONT.PN AS PART_NUMBER_SPMKT ,FRONT.LOC AS LOCAL_SPMKT ,CASE WHEN SUBSTR(TRIM(FRONT.LOC), 1, 2) = ''EF'' THEN ''MOTOLORA'' WHEN SUBSTR(TRIM(FRONT.LOC), 1, 1) = ''D'' THEN ''DEVOLUCAO MOT'' WHEN SUBSTR(TRIM(FRONT.LOC), 1, 2) = ''AU'' THEN ''AUTOMOTIVA'' ELSE ''OTHERS'' END AS CLIENTE ,FRONT.QTY AS QTDE_SPMKT ,TO_CHAR(FRONT.DATA_FIFO, ''DD/MM/YYYY'')AS DATA_FIFO_SPMKT ,CENTRAL.Warehouse AS ALMOX2 ,CENTRAL.PN AS PART_NUMBER_CE ,CENTRAL.LOC AS LOCAL_CENTRAL ,CENTRAL.QTY AS QTDE_CENTRAL ,TO_CHAR(CENTRAL.DATA_FIFO, ''DD/MM/YYYY'') AS DATA_FIFO_CE FROM (SELECT DISTINCT TRIM(TDILC101.T$cwar) AS Warehouse ,TRIM(TDILC101.T$item) AS PN ,TRIM(TDILC101.T$loca) AS LOC ,TRIM(TDILC101.T$strs) AS QTY ,TRIM(TDILC101.T$clot) AS Lote ,TDILC101.T$date AS DATA_FIFO FROM BAAN.TTDILC001361 TDILC001 ,BAAN.TTDILC101361 TDILC101 WHERE TDILC001.T$loca = TDILC101.T$loca AND TDILC001.T$prio = ''600'' ORDER BY TDILC101.T$date DESC)FRONT FULL OUTER JOIN (SELECT DISTINCT TRIM(TDILC101.T$cwar) AS Warehouse ,TRIM(TDILC101.T$item) AS PN ,TRIM(TDILC101.T$loca) AS LOC ,TRIM(TDILC101.T$strs) AS QTY ,TDILC101.T$date AS DATA_FIFO FROM BAAN.TTDILC001361 TDILC001 ,BAAN.TTDILC101361 TDILC101 WHERE TDILC001.T$loca = TDILC101.T$loca AND TDILC001.T$prio = ''900'' AND TRIM(TDILC101.T$cwar) = ''910'' ORDER BY TDILC101.T$date DESC )CENTRAL ON FRONT.PN = CENTRAL.PN WHERE FRONT.DATA_FIFO > CENTRAL.DATA_FIFO AND FRONT.Lote IS NULL ORDER BY FRONT.LOC') DECLARE @count int select @count = count(*) from #body if (@count > 0 AND (SELECT DATEPART(weekday, GETDATE())) != '1' AND (SELECT CONVERT(VARCHAR(8),GETDATE(),108)) BETWEEN '00:00:01' AND '23:59:00') -- SE CONTER ITENS, NÃO FOR DOMINGO E FOR 06:15 begin declare @id varchar(200), ALMOX varchar(5), PART_NUMBER_SPMKT varchar(max), LOCAL_SPMKT varchar(10), CLIENTE varchar(10), QTDE_SPMKT varchar(max), DATA_FIFO_SPMKT varchar(max), ALMOX2 varchar(5) PART_NUMBER_CE varchar(max), LOCAL_CENTRAL varchar(10), QTDE_CENTRAL varchar(max), DATA_FIFO_CE varchar(max); declare @html varchar(max) set @html =''+ N'<div align="center">'+ N'<table class="MsoNormalTable" border="0" cellspacing="10" cellpadding="0" width="801" style="width:600.5pt;mso-cellspacing:7.5pt;background:black;mso-yfti-tbllook:'+ N' 1184;mso-padding-alt:7.5pt 7.5pt 7.5pt 7.5pt">'+ N' <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;mso-yfti-lastrow:yes">'+ N' <td width="150" style="width:112.5pt;padding:7.5pt 7.5pt 7.5pt 7.5pt">'+ N' <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:7.5pt;font-family:&quot;Helvetica&quot;,&quot;sans-serif&quot;;mso-fareast-font-family:'+ N' &quot;Times New Roman&quot;;color:black"><img id="_x0000_i1025" src="http://support.flex.com/cdnr/54/acton/attachment/39314/f-e636025b-9246-4c7c-9442-510ca479e953/1/-/-/-/-/logo_40x91.png" border="0"></span></p>'+ N' </td>'+ N' <td width="360" style="width:3.75in;padding:7.5pt 7.5pt 7.5pt 7.5pt">'+ N' <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:'+ N' auto;text-align:right"><span style="font-family:&quot;Arial&quot;,&quot;sans-serif&quot;;'+ N' mso-fareast-font-family:&quot;Times New Roman&quot;;color:white">Alerta: | FIFO DE PLACAS MAIS ANTIGO EM CENTRAL</span></p>'+ N' </td>'+ N' </tr>'+ N'</tbody></table>'+ N'</div>'+ N'<div align="center">'+ N'<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="600" style="width:6.25in;mso-cellspacing:0in;mso-yfti-tbllook:1184;mso-padding-alt:'+ N' 0in 0in 0in 0in">'+ N' <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes">'+ N' <td style="background:white;padding:15.0pt 0in 15.0pt 0in">'+ N'<style type="text/css">'+ N' table.steelBlueCols {'+ N' border: 0px solid #0101DF;'+ N' text-align: center;'+ N' border-collapse: collapse;'+ N' color: #000000;'+ N' width: 95%;'+ N' border: none;'+ N' margin:auto;'+ N' }'+ N' table.steelBlueCols th {border: 1px solid #A8A8A8;padding: 5px 10px;}'+ N' table.steelBlueCols tr {border: 1px solid #A8A8A8;padding: 5px 10px;}'+ N' table.steelBlueCols td {border: 1px solid #A8A8A8;padding: 5px 10px;}'+ N' h2 {text-align: center;}'+ N'</style> '+ N'<div class="container_Report">'+ N' <div class="row">'+ N' <p> </p>'+ N'<table class="steelBlueCols">' + N'<tr bgcolor="#01A9DB"><th>ALMOX</th>'+ N'<th>PART_NUMBER_SPMKT</th>'+ N'<th>CLIENTE</th>'+ N'<th>QTDE_SPMKT</th>'+ N'<th>DATA_FIFO_SPMKT</th>'+ N'<th>ALMOX2</th>'+ N'<th>PART_NUMBER_CE</th>'+ N'<th>LOCAL_CENTRALL</th>'+ N'<th>QTDE_CENTRAL</th>'+ N'<th>DATA_FIFO_CE</th>'+ WHILE @count > 0 BEGIN select top 1 @id = id , @ALMOX = ALMOX, @PART_NUMBER_SPMKT = PART_NUMBER_SPMKT, @LOCAL_SPMKT = LOCAL_SPMKT, @CLIENTE = CLIENTE, @QTDE_SPMKT = QTDE_SPMKT, @DATA_FIFO_SPMKT = DATA_FIFO_SPMKT, @ALMOX2 = ALMOX2, @PART_NUMBER_CE = PART_NUMBER_CE, @LOCAL_CENTRAL = LOCAL_CENTRAL, @QTDE_CENTRAL = QTDE_CENTRAL, @DATA_FIFO_CE = DATA_FIFO_CE from #body set @html =@html+ N'<tr><th>'+ @ALMOX +'</th>'+ N'<th>' +@PART_NUMBER_SPMKT +'</th>'+ N'<th>' +@LOCAL_SPMKT+'</th>'+ N'<th>' +@CLIENTE+'</th>'+ N'<th>' +@QTDE_SPMKT+'</th>'+ N'<th>' +@DATA_FIFO_SPMKT+'</th>'+ N'<th>' +@ALMOX2+'</th>'+ N'<th>' +@PART_NUMBER_CE+'</th>'+ N'<th>' +@LOCAL_CENTRAL+'</th>'+ N'<th>' +@QTDE_CENTRAL+'</th>'+ N'<th>' +@DATA_FIFO_CE+'</th>'; DELETE FROM #body WHERE @id = id SELECT @count = count(*) FROM #body END set @html = @html+ N'</table>'+ N' </td>'+ N' </tr>'+ N' <tr style="mso-yfti-irow:1;mso-yfti-lastrow:yes">'+ N' <td style="padding:0in 0in 0in 0in"></td>'+ N' </tr>'+ N' </tbody></table>'+ N' </div>'+ N' </td>'+ N' </tr>'+ N' <tr style="mso-yfti-irow:1;mso-yfti-lastrow:yes">'+ N' <td style="background:#E6E6E6;padding:15.0pt 0in 15.0pt 0in">'+ N' <div align="center">'+ N' <table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="500" style="width:375.0pt;mso-cellspacing:0in;mso-yfti-tbllook:1184;mso-padding-alt:'+ N' 0in 0in 0in 0in">'+ N' <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;mso-yfti-lastrow:yes">'+ N' <td style="padding:0in 0in 0in 0in">'+ N' <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:'+ N' auto"><span style="font-size:7.5pt;font-family:&quot;Arial&quot;,&quot;sans-serif&quot;;'+ N' color:gray">Copyright © 2021. Flex Ltd. All rights reserved. No part of'+ N' this document may be reproduced in any form without the prior written'+ N' permission of Flex. The information contained in this document is intended'+ N' for use and viewing by Flex employees only and may contain confidential,'+ N' trade secret, insider and/or legally privileged information belonging to'+ N' Flex or its clients. Unauthorized disclosure of this content in any form is'+ N' strictly prohibited and may result in disciplinary and/or legal action.</span></p>'+ N' </td>'+ N' </tr>'+ N' </tbody></table>'+ N' </div>'+ N' </td>'+ N' </tr>'+ N'</tbody></table>'+ N'</div>'; select @html --recipients select @recipients = email from ( SELECT distinct COALESCE( (SELECT CAST(email AS VARCHAR(200)) + ';' AS [text()] FROM WH_PROCESSO_SPMKT_FE_Email AS O FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), '') AS email FROM WH_PROCESSO_SPMKT_FE_Email AS C) as t --body EXEC [dbo].[sp_send_mail] @_from_address = 'Alerta: | FIFO MAIS ANTIGA EM CENTRAL <fifo.wh-reply@flex.com>', @_recipients = @recipients, --@_recipients = N'andre.capeleto@flex.com;sidnei.silva@flex.com;lincoln.delfino@flex.com', @_subject = N'FIFO DE PLACAS MAIS ANTIGA EM CENTRAL MOVER PARA O MERCADO', @_body= @html end end --EXEC [sp_WH_ALERTA_FIFO_MAIS_ANTIGA_EM_CENTRAL_ITENS] --http://governance.jag.flextronics.com/PLN_Report/Generic/Read?query_name=WH_CAD_SELF_LIFE_Email GO