SQLize Online / PHPize Online  /  SQLtest Online

--||||| DIAGNOSTICA GENERALE |||||-- DECLARE @SDDOCO FLOAT,@WODOCO FLOAT,@LOTN VARCHAR(8),@UKID VARCHAR(8),@R0DOCO FLOAT,@LOTS VARCHAR(1),@LOT3 VARCHAR(30),@LITM VARCHAR(25), @MCU VARCHAR(12),@SDDCTO CHAR(2),@LOTPROD VARCHAR(30),@SHPN FLOAT,@LOT1 VARCHAR(30),@BULK FLOAT,@BULKMCU VARCHAR(12),@LOT2 VARCHAR(30), @TODAY INT,@RLOT VARCHAR(30),@PITM VARCHAR(25),@CITM VARCHAR(25),@VITM VARCHAR(25),@CLIMCU VARCHAR(12),@ITM FLOAT,@WAVR01 VARCHAR(13), @LOTTO AS NCHAR(30),@WOUKID AS FLOAT,@SLDOCO AS FLOAT,@PALMARI AS FLOAT,@BR FLOAT,@BULKITM NVARCHAR(9),@ILDOC FLOAT,@MATERIALI FLOAT, @ORD FLOAT,@TPORD CHAR(2),@BRSRST CHAR(2),@UK FLOAT,@SP FLOAT,@WOSRST CHAR(2),@UKLTTR CHAR(3),@UKNXTR CHAR(3),@SPLTTR CHAR(3), @SPNXTR CHAR(3),@LOTNLITM NVARCHAR(30),@PREL CHAR(1),@SPED FLOAT,@SPEDST VARCHAR(2),@SPC FLOAT -- FLAG 0-1 SET @PALMARI=1 SET @BULK=0 SET @BULKMCU='' SET @BULKITM='' SET @MATERIALI=0 SET @ILDOC=0 -- DIAGNOSTICA BARCODE SET @LOTN='c0310543 ' -- DIAGNOSTICA ODV SET @SDDCTO='' SET @SDDOCO=0 -- DIAGNOSTICA ARTICOLO SET @LITM='' SET @MCU=' 1010001MG1' -- RIEMPIMENTO VARIABILI IF @SDDOCO=0 BEGIN SET @LOTTO=(SELECT TOP 1 WHY59WCLOTN FROM PRODDTA.FY59WC03 A WITH(NOLOCK) JOIN PRODDTA.FY59WC00 WITH(NOLOCK) ON A.WBUKID=WHUKID WHERE WBUPMJ>119000 AND WBLOTN IN (@LOTN) ORDER BY A.WBUKID DESC) SET @MCU=(SELECT TOP 1 LIMCU FROM PRODDTA.F41021 WITH(NOLOCK) WHERE (LIPQOH>0 OR LIQTTR>0) AND LILOTN=@LOTN) --SET @LITM=(SELECT TOP 1 IOLITM FROM PRODDTA.F41021 WITH(NOLOCK) JOIN PRODDTA.F4108 WITH(NOLOCK) ON IOITM=LIITM AND IOLOTN=LILOTN AND IOMCU=LIMCU WHERE LILOTN=@LOTN AND LIMCU=@MCU) SET @WODOCO=(SELECT TOP 1 wbdoco FROM PRODDTA.FY59WC03 A WITH(NOLOCK) JOIN PRODDTA.FY59WC00 WITH(NOLOCK) ON A.WBUKID=WHUKID WHERE WBUPMJ>119000 AND WBLOTN IN (@LOTN)) SET @RLOT=(SELECT TOP 1 IORLOT FROM PRODDTA.F4108 WITH(NOLOCK) WHERE IOLOTN=@LOTN) SET @TODAY=JDE_PRODUCTION.DBO.DATE_TO_JULIAN(GETDATE()) SET @BR=(SELECT TOP 1 WBUKID FROM PRODDTA.FY59WC03 A WITH(NOLOCK) JOIN PRODDTA.FY59WC00 WITH(NOLOCK) ON A.WBUKID=WHUKID WHERE WBLOTN=@LOTN) SET @BRSRST=(SELECT TOP 1 WHSRST FROM PRODDTA.FY59WC03 A WITH(NOLOCK) JOIN PRODDTA.FY59WC00 WITH(NOLOCK) ON A.WBUKID=WHUKID WHERE WBUKID=@BR) SET @SHPN=(SELECT TOP 1 SDSHPN FROM PRODDTA.F4211 WITH(NOLOCK) ) SET @UK=(SELECT TOP 1 SDDOCO FROM PRODDTA.F4211 WITH(NOLOCK) WHERE SDKCOO='00010' AND SDDCTO='UK' AND SDLOTN=@LOTN and sdnxtr<999 ORDER BY SDDOCO DESC) SET @SP=(SELECT TOP 1 SDDOCO FROM PRODDTA.F4211 WITH(NOLOCK) WHERE SDKCOO='00010' AND SDDCTO='SP' AND SDLOTN=@LOTN ORDER BY SDDOCO DESC) SET @WOSRST=(SELECT TOP 1 WASRST FROM PRODDTA.F4801 WITH(NOLOCK) WHERE WADOCO=@WODOCO) SET @UKLTTR=(SELECT TOP 1 SDLTTR FROM PRODDTA.F4211 WITH(NOLOCK) WHERE SDKCOO='00010' AND SDDCTO='UK' AND SDDOCO=@UK) SET @UKNXTR=(SELECT TOP 1 SDNXTR FROM PRODDTA.F4211 WITH(NOLOCK) WHERE SDKCOO='00010' AND SDDCTO='UK' AND SDDOCO=@UK) SET @SPLTTR=(SELECT TOP 1 SDLTTR FROM PRODDTA.F4211 WITH(NOLOCK) WHERE SDKCOO='00010' AND SDDCTO='SP' AND SDDOCO=@SP) SET @SPNXTR=(SELECT TOP 1 SDNXTR FROM PRODDTA.F4211 WITH(NOLOCK) WHERE SDKCOO='00010' AND SDDCTO='SP' AND SDDOCO=@SP) SET @LOTNLITM=(SELECT TOP 1 WCLITM FROM PRODDTA.FY59R411 WITH(NOLOCK) WHERE WCLOTN=@LOTN AND WCPQOH>0) SET @PREL=(SELECT TOP 1 WSY59PREL FROM PRODDTA.FY59X012 WITH(NOLOCK) WHERE WSLOTN=@LOTN) SET @SPED=(SELECT TOP 1 WSSHPN FROM PRODDTA.FY59X012 WITH(NOLOCK) WHERE WSLOTN=@LOTN) SET @SPEDST=(SELECT TOP 1 SYSSTS FROM PRODDTA.FY59G58Y WITH(NOLOCK) WHERE SYSHPN=@SPED) SET @SPC=(SELECT TOP 1 R0DOCO FROM PRODDTA.FY5941R0 WITH(NOLOCK) WHERE R0LOTN=@LOTN AND R0DOCO<>0 ORDER BY R0UKID DESC) END -- MOSTRA VARIABILI CALCOLATE SELECT @LOTN'BARCODE',@LOTNLITM'PRODOTTO',@WODOCO'WO',@WOSRST'ST',@BR'BR',@BRSRST'ST',@LOTTO'LOTTO',@MCU'GIACENZA', ISNULL(@UK,'')'UK',ISNULL(@UKLTTR,'')'LTTR',ISNULL(@UKNXTR,'')'NXTR',ISNULL(@SP,'')'SP',ISNULL(@SPLTTR,'')'LTTR',ISNULL(@SPNXTR,'')'NXTR', @PREL'PREL',@SPED'SHPN',@SPEDST'SSTS',@SPC'SP-C' IF @LOTN<>'' -- GIACENZA ENTRA PER LOTN (BOMBOLE E BULK) BEGIN SELECT ARLOT1,ARLOT2,CONCAT(LTRIM(RTRIM(ARY59RGAS)),LTRIM(RTRIM(ARSEG3)),LTRIM(RTRIM(ARSEG4)),LTRIM(RTRIM(ARSEG5)),LTRIM(RTRIM(ARSEG6)))'ARSEGs + GAS', ARLRP1'GER',IIF(DBO.JULIAN_TO_DATE(ARMMEJ)<=GETDATE(),CONCAT('SCADUTO IL',DBO.JULIAN_TO_DATE(ARMMEJ)),CONCAT(DBO.JULIAN_TO_DATE(ARMMEJ),''))'SCAD COLL', IIF(DBO.JULIAN_TO_DATE(IOMMEJ)<=GETDATE(),CONCAT('SCADUTO IL ',DBO.JULIAN_TO_DATE(IOMMEJ)),CONCAT(DBO.JULIAN_TO_DATE(MAX(IOMMEJ)),''))'SCAD PROD', CONCAT(IOLOTS,' ',LILOTS)'LOTS',LIMCU,LIPQOH'QOH',LIHCOM'HCOM',LIQOWO'QOWO',LIQTTR'QTTR', IBSRP0'SRP0',IBSRCE'SRCE',IBPRP4'PRP4',IBSTKT'STKT',LIUSER,ABALPH FROM PRODDTA.F41021 WITH(NOLOCK) LEFT JOIN PRODDTA.F4108 WITH(NOLOCK) ON IOITM=LIITM AND IOLOTN=LILOTN AND IOMCU=LIMCU LEFT JOIN PRODDTA.FY59XAAB WITH(NOLOCK) ON IOLOT1=ARLOT1 AND IOLOT2=ARLOT2 LEFT JOIN PRODDTA.F4102 WITH(NOLOCK) ON LIMCU=IBMCU AND LIITM=IBITM LEFT JOIN PRODDTA.F4101 WITH(NOLOCK) ON LIITM=IMITM LEFT JOIN PRODDTA.F0092 WITH (NOLOCK) ON ULUSER=LIUSER LEFT JOIN PRODDTA.F0101 WITH (NOLOCK) ON ABAN8=ULAN8 LEFT JOIN PRODDTA.FY59R411 WITH (NOLOCK) ON WCLOTN=LILOTN AND WCMCU=LIMCU AND WCITM=LIITM WHERE (LIPQOH>0 OR LIQTTR>0) AND LILOTN=@LOTN GROUP BY ARLOT1,ARLOT2,ARY59RGAS,ARSEG3,ARSEG4,ARSEG5,ARSEG6,IOLITM,ARLRP1,IBSRP0,IBSRCE,LIUSER,ARMMEJ,IOMMEJ,IOLOTS,LILOTS,IBPRP4,IBSTKT,ABALPH,LIMCU,LIPQOH,LIHCOM,LIQOWO,LIQTTR,WCLITM END IF @LOTN<>'' -- STORICO MOVIMENTI ENTRA PER LOTN (BOMBOLE) BEGIN SELECT TOP 45 ILLOTN,ILLITM,ILMCU,ILLOCN,ILFRTO'F/T', ILTRQT,CONCAT(ILDCT,' - ',ILTREX)'TIPO MOVIMENTO',ILDOCO,ILDCTO'DCTO',DBO.JULIAN_TO_DATE(ILTRDJ)'DATA',ILTDAY'ORA',ILPID'APP', ILLOTS'LOTS',CONCAT(ILUSER,ABALPH)'UTONTO',ILDOC,* FROM PRODDTA.F4111 WITH (NOLOCK) LEFT JOIN PRODDTA.F0092 WITH (NOLOCK) ON ULUSER=ILUSER LEFT JOIN PRODDTA.F0101 WITH (NOLOCK) ON ABAN8=ULAN8 WHERE ILDCT<>'IB' AND ILDCT<>'IZ' AND ILLOTN=@LOTN ORDER BY ILTRDJ DESC, ILUKID DESC SELECT 'IMPEGNO WO' 'IMPEGNO WO?', ISLOTN AS BARCODE, ISDOCO AS NR_ORDINE,ISDCT AS TIPO_ORDINE, WAMCU AS FILIALE_PROD, WASRST AS STATO, WAANPA AS CLIENTE,SUBSTRING(WAVR01,1,8) AS ORDINE_SP,SUBSTRING(WAVR01,13,5) AS RIGA, WALITM AS CODICE_PRODOTTO FROM PRODDTA.F3105 WITH (NOLOCK) JOIN PRODDTA.F4801 WITH (NOLOCK) ON ISDOCO=WADOCO AND WADCTO=ISDCT WHERE ISLOTN =@LOTN AND WASRST<90 SELECT 'IMPEGNO U/T' 'IMPEGNO TRASF?',SDDOCO,CONCAT(SDDCTO,' ',DBO.GETUDC(SDDCTO,'00','DT',1))'DCTO',SDLTTR,CONCAT(SDNXTR,' ',DBO.GETUDC(SDNXTR,'40','AT',1))'NXTR',SDRCTO'RCTO',SDRORN,SDLOTN,SDTORG,SDMCU,SDMOT,SDSHAN,ABALPH,SDVR02 FROM PRODDTA.F4211 JOIN PRODDTA.F0006 WITH (NOLOCK) ON SDMCU=MCMCU JOIN PRODDTA.F0101 WITH (NOLOCK) ON ABAN8=SDSHAN WHERE SDLOTN IN (@LOTN) AND SDSO02 = ' ' AND SDLTTR <> '980' END IF @SP<>0 AND @SP IS NOT NULL BEGIN SELECT SDDOCO,SDDCTO'DCTO',SDLTTR'LTTR',SDNXTR'NXTR',SDLNID,SDMCU,SDDELN,SDSHPN,SDURAT,SDRORN,SDLITM,SDLOCN'LOCN',SDAN8,SDLOB,IBSRP0'SRP0',IBSRP4'PRP4',IBSTKT'STKT',SDRCTO,SDTORG,SDUSER,SDPID,SDJOBN, DBO.JULIAN_TO_DATE(SDUPMJ)'SDUPMJ',SDTDAY,HOHCOD,* FROM PRODDTA.F4211 WITH (NOLOCK) LEFT JOIN PRODDTA.F4209 WITH(NOLOCK) ON SDDOCO=HODOCO AND SDDCTO=HODCTO AND SDKCOO=HOKCOO LEFT JOIN PRODDTA.F4102 WITH (NOLOCK) ON IBITM=SDITM AND IBMCU=SDMCU WHERE SDKCOO='00010' AND SDDCTO='SP' AND SDDOCO=@SP SELECT SLDOCO,SLDCTO,SLLTTR,SLNXTR,SLUSER,SLLITM FROM PRODDTA.F42199 WITH (NOLOCK) WHERE SLKCOO='00010' AND SLDCTO='SP' AND SLDOCO=@SP END IF @UK<>0 AND @UK IS NOT NULL BEGIN SELECT SDDOCO,SDDCTO'DCTO',SDLTTR'LTTR',SDNXTR'NXTR',SDLNID,SDMCU,SDDELN,SDSHPN,SDURAT,SDRORN,SDLITM,SDLOCN'LOCN',SDAN8,SDLOB,IBSRP0'SRP0',IBSRP4'PRP4',IBSTKT'STKT',SDRCTO,SDTORG,SDUSER,SDPID,SDJOBN, DBO.JULIAN_TO_DATE(SDUPMJ)'SDUPMJ',SDTDAY,* FROM PRODDTA.F4211 WITH (NOLOCK) LEFT JOIN PRODDTA.F4102 WITH (NOLOCK) ON IBITM=SDITM AND IBMCU=SDMCU WHERE SDKCOO='00010' AND SDDCTO='UK' AND SDDOCO=@UK SELECT SLDOCO,SLDCTO,SLLTTR,SLNXTR,SLUSER,SLLITM FROM PRODDTA.F42199 WITH (NOLOCK) WHERE SLKCOO='00010' AND SLDCTO='UK' AND SLDOCO=@UK END IF @LOTN LIKE 'C%' --> DIAGNOSTICA BOMBOLE CLIENTE BEGIN SELECT SEY59C01,R0UKID,R0LOTN,MAX(R0AN8)'R0AN8',R0MCU,R0DOCO,R0DCTO,R0LNID,R0MMCU,R0SRST,R0KITL,R0URRF,ODV.SDDOCO,ODV.SDDCTO,MAX(ODV.SDSHAN)'ODV.SDSHAN',ODV.SDLITM,ODV.SDLNID,ODV.SDLTTR,ODV.SDNXTR,ODV.SDMCU,ODV.SDLNTY,WE.WADOCO,WE.WADCTO,WE.WASRST, WE.WAMMCU,WO.WADOCO,WO.WADCTO,WO.WASRST,WO.WAMMCU,LIMCU,LILOCN,LIPQOH/1000 'Q.TA',IOLITM, WHY59WCLOTN, WHUKID, UW.SDDOCO,UW.SDDCTO,UW.SDLITM,UW.SDLNID,UW.SDMCU,UW.SDSHAN,UW.SDLTTR,UW.SDNXTR FROM JDE_PRODUCTION.PRODDTA.FY5941R0 WITH(NOLOCK) JOIN JDE_PRODUCTION.PRODDTA.F4211 ODV WITH(NOLOCK) ON R0DOCO=ODV.SDDOCO AND R0DCTO=ODV.SDDCTO AND R0LNID=ODV.SDLNID AND R0KCOO=ODV.SDKCOO AND ODV.SDNXTR<999 JOIN JDE_PRODUCTION.PRODDTA.FY594211 WITH(NOLOCK) ON R0DOCO=SEDOCO AND R0DCTO=SEDCTO AND R0LNID=SELNID AND R0KCOO=SEKCOO LEFT JOIN JDE_PRODUCTION.PRODDTA.F4801 WE WITH(NOLOCK) ON WE.WAVR01=CONCAT(CAST(R0DOCO AS INT),'-',RTRIM(R0DCTO),'-',RTRIM(R0LNID)) AND WE.WADCTO='WE' --AND WE.WASRST<91 LEFT JOIN JDE_PRODUCTION.PRODDTA.F4801 WO WITH(NOLOCK) ON WO.WAVR01=CONCAT(CAST(R0DOCO AS INT),'-',RTRIM(R0DCTO),'-',RTRIM(R0LNID)) AND WO.WADCTO='WO' JOIN JDE_PRODUCTION.PRODDTA.F4108 WITH(NOLOCK) ON IOLOT1=R0LOT1 AND IOLOT2=R0LOT2 AND (IOLITM=R0LITM OR IOLITM=CONCAT('P',SUBSTRING(R0LITM,2,15))) LEFT JOIN JDE_PRODUCTION.PRODDTA.F41021 WITH(NOLOCK) ON R0LOTN=LILOTN AND IOITM=LIITM AND LIPQOH=1000 LEFT JOIN JDE_PRODUCTION.PRODDTA.FY59WC01 WITH(NOLOCK) ON WHDOC=WE.WADOCO AND WHDCT=WE.WADCTO LEFT JOIN JDE_PRODUCTION.PRODDTA.F4211 UW WITH(NOLOCK) ON UW.SDVR02=CONCAT(CAST(R0DOCO AS INT),'-',RTRIM(R0DCTO),'-',RTRIM(R0LNID)) AND UW.SDLTTR<620 WHERE R0LOTN IN (@LOTN) GROUP BY SEY59C01,R0UKID,R0LOTN,R0MCU,R0DOCO,R0DCTO,R0LNID,R0MMCU,R0SRST,R0KITL,R0URRF,ODV.SDDOCO,ODV.SDDCTO,ODV.SDLITM,ODV.SDLNID,ODV.SDLTTR,ODV.SDNXTR,ODV.SDMCU,ODV.SDLNTY,WE.WADOCO,WE.WADCTO,WE.WASRST,WE.WAMMCU,WO.WADOCO,WO.WADCTO,WO.WASRST,WO.WAMMCU,LIMCU,LILOCN,LIPQOH,IOLITM, WHY59WCLOTN, WHUKID,UW.SDDOCO,UW.SDDCTO,UW.SDLITM,UW.SDLNID,UW.SDMCU,UW.SDSHAN,UW.SDLTTR,UW.SDNXTR ORDER BY R0LOTN DESC SELECT CONCAT(R0SRST,' ',DBO.GETUDC(R0SRST,'00','SS',1))'STATO',* FROM PRODDTA.FY5941R0 WHERE R0LOTN=@LOTN ORDER BY R0UKID DESC SELECT * FROM PRODDTA.F41021 WITH (NOLOCK) WHERE LILOTN=@LOTN AND LIPQOH=1000 END IF @BULK=1 -- STORICO MOVIMENTI ENTRA PER LOTN+MCU+LITM (BULK) BEGIN SELECT TOP 10 ILLOTN,ILLITM,ILMCU,ILLOCN,ILFRTO'F/T',ILTRQT'+/-', CONCAT(ILDCT,' - ',DBO.GETUDC(ILDCT,'00','DT',1))'TIPO MOVIMENTO',ILPID,ILDOC,ILLOTS'LOTS',ILDOCO, ILDCTO'DCTO',DBO.JULIAN_TO_DATE(ILTRDJ)'DATA',ILTDAY'ORA',ILUSER FROM PRODDTA.F4111 WITH (NOLOCK) WHERE ILDCT<>'IB' AND ILDCT<>'IZ' AND ILITM=@ITM ORDER BY ILTRDJ DESC, ILUKID DESC END IF @MATERIALI = 1 -- STORICO MOVIMENTI ENTRA PER LOTN+MCU (MATERIALI) BEGIN SELECT TOP 10 ILLOTN,ILLITM,ILMCU,ILLOCN,ILFRTO'F/T',ILTRQT'+/-', CONCAT(ILDCT,' - ',DBO.GETUDC(ILDCT,'00','DT',1))'TIPO MOVIMENTO',ILPID,ILDOC,ILLOTS'LOTS',ILDOCO, ILDCTO'DCTO',DBO.JULIAN_TO_DATE(ILTRDJ)'DATA',ILTDAY'ORA',ILUSER FROM PRODDTA.F4111 WITH (NOLOCK) WHERE ILDCT<>'IB' AND ILDCT<>'IZ' AND ILITM=@ITM ORDER BY ILTRDJ DESC, ILUKID DESC END IF @ILDOC<>0 -- STORICO MOVIMENTI ENTRA PER ILDOC (BOMBOLE) BEGIN SELECT TOP 10 ILLOTN,ILLITM,ILMCU,ILLOCN,ILFRTO'F/T', ILTRQT,CONCAT(ILDCT,' - ',ILTREX)'TIPO MOV.',ILDOC,ILLOTS'LOTS',ILDOCO, ILDCTO'DCTO',DBO.JULIAN_TO_DATE(ILTRDJ)'DATA',ILTDAY'ORA',ILPID,ILUSER FROM PRODDTA.F4111 WITH (NOLOCK) WHERE ILDCT<>'IB' AND ILDCT<>'IZ' AND ILLOTN=@ILDOC ORDER BY ILTRDJ DESC, ILUKID DESC END IF @BULK=1 -- GIACENZA ENTRA PER ILLOTN (BOMBOLE E BULK) BEGIN SELECT LILOTN,ARLOT1,ARLOT2,CONCAT(LTRIM(RTRIM(ARY59RGAS)),LTRIM(RTRIM(ARSEG3)),LTRIM(RTRIM(ARSEG4)),LTRIM(RTRIM(ARSEG5)),LTRIM(RTRIM(ARSEG6)))'SEGS + GAS',IOLITM, ARLRP1'GER',IIF(DBO.JULIAN_TO_DATE(ARMMEJ)<=GETDATE(),CONCAT('SCADUTO IL ',DBO.JULIAN_TO_DATE(ARMMEJ)),CONCAT(DBO.JULIAN_TO_DATE(ARMMEJ),''))'SCAD COLL', IIF(DBO.JULIAN_TO_DATE(IOMMEJ)<=GETDATE(),CONCAT('SCADUTO IL ',DBO.JULIAN_TO_DATE(IOMMEJ)),CONCAT(DBO.JULIAN_TO_DATE(MAX(IOMMEJ)),''))'SCAD PROD', CONCAT(IOLOTS,' ',LILOTS)'LOTS',IBPRP4'PRP4',IBSTKT'STKT',LIMCU,LIPQOH'QOH',LIHCOM'HCOM',LIQOWO'QOWO',LIQTTR'QTTR' FROM PRODDTA.F41021 WITH (NOLOCK) LEFT JOIN PRODDTA.F4108 WITH(NOLOCK) ON IOITM=LIITM AND IOLOTN=LILOTN AND IOMCU=LIMCU LEFT JOIN PRODDTA.FY59XAAB WITH(NOLOCK) ON IOLOT1=ARLOT1 AND IOLOT2=ARLOT2 LEFT JOIN PRODDTA.F4102 WITH(NOLOCK) ON LIMCU=IBMCU AND LIITM=IBITM LEFT JOIN PRODDTA.F4101 WITH(NOLOCK) ON LIITM=IMITM WHERE (LIPQOH>0 OR LIQTTR>0) AND LILOTN = @LOTN GROUP BY LILOTN,ARLOT1,ARLOT2,ARY59RGAS,ARSEG3,ARSEG4,ARSEG5,ARSEG6,IOLITM,ARLRP1,ARMMEJ,IOMMEJ,IOLOTS,LILOTS,IBPRP4,IBSTKT,LIMCU,LIPQOH,LIHCOM,LIQOWO,LIQTTR END IF @LITM<>'' BEGIN SELECT IBLITM,IBSTKT,IBPRP4,IBSRP0,IBSRCE, * FROM PRODDTA.F4102 WITH (NOLOCK) WHERE IBLITM=@LITM AND IBMCU=@MCU END IF @ITM<>0 --> DIAGNOSTICA ABILITAZIONE ARTICOLO SU MCU, STKT E FAMIGLIA PIANIFICAZIONE BEGIN SELECT TOP 1 'DATI ARTICOLO'AS ' ',LILOTN, IBLITM'ART',IBITM'ART BRV',CONCAT(IMDSC1,' ',IMDSC2)'DESCRIZIONE',CONCAT(IBPRP4,' ',DBO.GETUDC(IBPRP4,'41','P4',1))'FAMIGLIA PIANIF', LIPBIN,IBSTKT,IMSTKT,IBMCU,CONCAT(IBSRCE,' ',DBO.GETUDC(IBSRCE,'H41','SR',1))'SRCE - GESTIONE LOTTO/BARCODE' FROM PRODDTA.F4102 WITH(NOLOCK) JOIN PRODDTA.F41021 WITH(NOLOCK) ON IBMCU=LIMCU AND IBITM=LIITM --AND LIPQOH<>0 LEFT JOIN PRODDTA.F4101 WITH(NOLOCK) ON IMITM=IBITM WHERE LIITM=@ITM AND LIPQOH>0 SELECT LILOTN,IOLITM,ARLOTN,ARLOT1,ARLOT2,CONCAT(LILOTS,' ',DBO.GETUDC(LILOTS,'41','L',1))'LILOTS',CONCAT(IOLOTS,' ',DBO.GETUDC(IOLOTS,'41','L',1))'IOLOTS',LIPQOH,LIQOWO,LIHCOM,LIQTTR,LIMCU,IORLOT, IIF(DBO.JULIAN_TO_DATE(ARMMEJ)<=GETDATE(),CONCAT('** SCADUTO IL ',DBO.JULIAN_TO_DATE(ARMMEJ),' **'),CONCAT(DBO.JULIAN_TO_DATE(ARMMEJ),''))'SCAD COLLAUDO',IIF(DBO.JULIAN_TO_DATE(IOMMEJ)<=GETDATE(),CONCAT('** SCADUTO IL ',DBO.JULIAN_TO_DATE(IOMMEJ),'***'),CONCAT(DBO.JULIAN_TO_DATE(IOMMEJ),''))'SCAD CONTENUTO', CASE WHEN ARCRUD<>0 THEN DBO.JULIAN_TO_DATE(ARCRUD) ELSE NULL END 'GG COSTR VALV',IIF(ARCRUD<>0,CONVERT(DATE,CONCAT(DATEPART(YYYY,DBO.JULIAN_TO_DATE(ARCRUD))+ISNULL(TVY59NTDS,0),'-',DATEPART(MM,DBO.JULIAN_TO_DATE(ARCRUD)),'-',DATEPART(DD,DBO.JULIAN_TO_DATE(ARCRUD)))),NULL)'SCAD VALVOLA',ISNULL(TVY59NTDS,0) 'DEPER VALVOLA', RTRIM(ARDSC2)'TIPO VALVOLA',DBO.JULIAN_TO_DATE(WHSTRT)'DATA PROD',DBO.JULIAN_TO_DATE(WHURDT)'SCAD PROD',IBSLD'DEPER PROD',DBO.JULIAN_TO_DATE(ARSTRX)'COSTR.CONTEN.' FROM PRODDTA.F41021 WITH(NOLOCK) JOIN PRODDTA.F4108 WITH(NOLOCK) ON IOITM=LIITM AND IOMCU=LIMCU AND IOLOTN=LILOTN LEFT JOIN PRODDTA.FY59XAAB WITH(NOLOCK) ON IOLOT1=ARLOT1 AND IOLOT2=ARLOT2 LEFT JOIN PRODDTA.F4101 WITH(NOLOCK) ON IMITM=LIITM LEFT JOIN PRODDTA.FY59XAAI WITH(NOLOCK) ON RTRIM(ARDSC2)=TVUKID LEFT JOIN PRODDTA.FY59WC00 WITH(NOLOCK) ON IORLOT=WHY59WCLOTN AND IORLOT<>' ' AND IMTMPL='CODPKGB' LEFT JOIN PRODDTA.F4102 WITH(NOLOCK) ON IBITM=LIITM AND IBMCU=WHMCU AND IBLITM IS NOT NULL WHERE LIITM=@ITM AND LIPQOH>0 ORDER BY LIPQOH DESC END IF @SPED<>0 -- X011 X012 BEGIN SELECT 'X011'AS' ', * FROM PRODDTA.FY59X011 WITH(NOLOCK) WHERE WFSHPN=@SPED SELECT 'X012'AS' ', * FROM PRODDTA.FY59X012 WITH(NOLOCK) WHERE WSSHPN=@SPED ORDER BY WSDOCO DESC END IF @LOTTO<>'' --> DIAGNOSTICA PRODUZIONE A LOTTI BEGIN SET @WOUKID=(SELECT TOP 1 WHUKID FROM PRODDTA.FY59WC00 WITH (NOLOCK) WHERE WHY59WCLOTN=@LOTTO ORDER BY WHUKID DESC) SELECT 'WC00'AS' ',CONCAT(WHSRST,' ',DBO.GETUDC(WHSRST,'00','SS',1))'WHSRST',* FROM PRODDTA.FY59WC00 WHERE WHY59WCLOTN=@LOTTO SELECT 'WCJT'AS' ',* FROM PRODDTA.FY59WCJT WITH (NOLOCK) WHERE JTLOTN=@LOTTO SELECT 'WC03 JOIN WC01'AS' ',A.WBUKID,CONCAT(WHSRST,' ',DBO.GETUDC(WHSRST,'00','SS',1))'WHSRST',DBO.JULIAN_TO_DATE(A.WBURDT)'DATA VERS',WHY59WCLOTN,* FROM PRODDTA.FY59WC03 A WITH (NOLOCK) JOIN PRODDTA.FY59WC00 WITH (NOLOCK) ON A.WBUKID=WHUKID WHERE WHY59WCLOTN=@LOTTO AND WBURDT>119000 ORDER BY A.WBUKID DESC SELECT '3711'AS' ',* FROM PRODDTA.F3711 WITH (NOLOCK) WHERE TRQMRF3=@LOTTO SELECT 'G26B'AS' ',BOUKID,BOY59G26GR,BODL01,BOPSEQ,BOY59G26FA,BODL02,BODSPSEQ'BODSPSEQ.',CONCAT(BOSRST,' ',DBO.GETUDC(BOSRST,'00','SS',1))'BOSRST',BOLOTS,* FROM PRODDTA.FY59G26B WITH (NOLOCK) WHERE BOUKID=@WOUKID ORDER BY BODSPSEQ DESC END IF @BULK=1 --BULK BEGIN SELECT T.WHMCU AS 'MCU', MCDL01 AS 'DESC MCU', T.WHY59WCLOTN AS 'LOTTO', SUBSTRING(T1.WHLITM,2,4) AS 'SPECIFICA GAS', IIF(SUBSTRING(T1.WHLITM,10,1)='B','BOMBOLE', IIF(SUBSTRING(T1.WHLITM,10,1)='P','PACCHI','CRIO')) AS 'TIPO IMBALLO', JTLOCN AS 'UBICAZIONE TANK', JTLOTN AS 'LOTTO MATERIA PRIMA', JTITM AS 'PBITM', JTLITM AS 'ARTICOLO MATERIA PRIMA', WMUORG/1000 AS 'Q.TÀ RICHIESTA', WMTRQT/1000 AS 'Q.TÀ SCARICATA', WMSOCN/1000 AS 'Q.TÀ RESIDUA', WMUM AS 'UNITÀ DI MISURA', JDE_PRODUCTION.DBO.JULIAN_TO_DATE(T.WHSTRT) AS 'DATA INIZIO PRODUZIONE', T1.WHDOCO AS 'ORDINE PRODUZIONE', T1.WHDCTO AS 'TIPO ORDINE' FROM JDE_PRODUCTION.PRODDTA.FY59WC00 T WITH(NOLOCK) JOIN JDE_PRODUCTION.PRODDTA.F0006 WITH(NOLOCK) ON T.WHMCU=MCMCU JOIN JDE_PRODUCTION.PRODDTA.FY59WC01 T1 WITH(NOLOCK) ON T.WHUKID=T1.WHUKID JOIN JDE_PRODUCTION.PRODDTA.FY59WCJT WITH(NOLOCK) ON T.WHUKID=JTUKID JOIN JDE_PRODUCTION.PRODDTA.F3111 WITH(NOLOCK) ON T1.WHDOCO=WMDOCO AND T1.WHDCTO=WMDCTO AND JTLOTN=WMLOTN AND JTLOCN=WMLOCN AND JTITM=WMCPIT --JOIN JDE_PRODUCTION.PRODDTA.F4111 WITH(NOLOCK) ON JTLOTN=ILLOTN AND T.WHMCU=ILMCU AND JTITM=ILITM AND ILDCT='IM' AND T1.WHDOCO=ILDOCO AND T1.WHDCTO=ILDCTO WHERE T.WHMCU=@BULKMCU AND T.WHSTRT BETWEEN 117091 AND 117120 AND SUBSTRING(T1.WHLITM,10,1) IN ('O','F') SELECT IXKITL 'ARTICOLO PIENO',IXLITM 'ARTICOLO COMPONENTE',IXQNTY/1000 'QUANTITÀ', IXUM 'UNITÀ DI MISURA', IXCPNB/100 'NR RIGA COMPONENTE', IIF(IXSBNT=0,'Y','N') 'COMPONENTE PRINCIPALE', IIF(IXSBNT>0,'Y','N') 'COMPONENTE ALTERNATIVO', UMCONV/10000000 'LITRAGGIO',UMRUM 'UNITÀ DI MISURA' FROM JDE_PRODUCTION.PRODDTA.F3002 WITH(NOLOCK) JOIN JDE_PRODUCTION.PRODDTA.F41002 WITH(NOLOCK) ON IXKIT=UMITM AND UMRUM='M3' WHERE IXMMCU=@BULKMCU ORDER BY IXKIT,IXCPNB,IXSBNT END IF @LOTN<>'' --> ESTRAI CESTELLO DI QUEL BARCODE CON TUTTE LE BBLE ASSOCIATE BEGIN SELECT 'CESTELLO'AS' ',IOLOT3,IOLOTN, LIMCU, LILOCN, IMLITM FROM PRODDTA.F4108 WITH(NOLOCK) JOIN PRODDTA.F41021 WITH(NOLOCK) ON LILOTN=IOLOTN AND IOMCU=LIMCU AND IOITM=LIITM JOIN PRODDTA.F4101 WITH(NOLOCK) ON IMITM=LIITM WHERE LIPQOH>1 AND LILOCN <>'GGER1' AND LILOTN<>'' AND IOLOT3<>'' AND IOLOT3 IN (SELECT TOP 1 IOLOT3 FROM PRODDTA.F4108 WITH(NOLOCK) WHERE IOLOTN=@LOTN AND IOLOT3<>'') END -- SE ULTIMO MOVIMENTO 4111 E' UK O UW GUARDAMI LA 4211 E 4311 DI QUEI TRASFERIMENTI, E LO STORICO PALMARI --IF (SELECT TOP 1 ILDCTO FROM PRODDTA.F4111 WHERE ILLOTN=@LOTN ORDER BY ILTRDJ DESC, ILUKID DESC) IN ('UW','UK') -- BEGIN -- SELECT SDDELN,SDURAT,SDLOTN,SDLITM,SDLTTR'LTTR',SDNXTR'NXTR',SDLNID/1000'RIGA',SDDOCO,A.ABALPH'DA',SDDCT,MCDL01'A',HOHCOD,SDLOB,SDMCU,SDRORN,SDRCTO,SDLITM,SDLOCN,SDAN8,SDUSER,B.ABALPH,* -- FROM PRODDTA.F4211 -- JOIN PRODDTA.F0101 A WITH(NOLOCK) ON A.ABAN8=SDSHAN -- JOIN PRODDTA.F0006 WITH(NOLOCK) ON MCMCU=SDMCU -- LEFT JOIN PRODDTA.F4209 WITH(NOLOCK) ON SDDOCO=HODOCO AND SDDCTO=HODCTO AND SDKCOO=HOKCOO -- LEFT JOIN PRODDTA.F0092 WITH (NOLOCK) ON ULUSER=SDUSER -- LEFT JOIN PRODDTA.F0101 B WITH (NOLOCK) ON B.ABAN8=ULAN8 -- WHERE SDDCTO IN ('UK','UW') AND SDDOCO=(SELECT TOP 1 ILDOCO FROM PRODDTA.F4111 WHERE ILLOTN=@LOTN AND ILDCTO IN ('UK','UW') ORDER BY ILTRDJ DESC, ILUKID DESC) -- DECLARE @UK FLOAT,@TK FLOAT,@VIAGGIO FLOAT,@SPEDIZIONE FLOAT,@DCTO NCHAR(2) -- SET @VIAGGIO=(SELECT TOP 1 SDURAT FROM PRODDTA.F4211 WHERE SDLOTN=@LOTN) -- SET @UK=(SELECT TOP 1 SDDOCO FROM PRODDTA.F4211 WITH(NOLOCK) WHERE SDKCOO='00010' AND SDDCTO IN ('UW','UK') AND SDSHPN=@VIAGGIO) -- SET @TK=(SELECT TOP 1 SDRORN FROM PRODDTA.F4211 WITH(NOLOCK) WHERE SDKCOO='00010'AND SDDCTO IN ('UW','UK') AND SDDOCO=@UK) -- SET @DCTO=(SELECT TOP 1 ILDCTO FROM PRODDTA.F4111 WHERE ILLOTN=@LOTN ORDER BY ILTRDJ DESC, ILUKID DESC) -- SELECT PDDCTO,PDDOCO,PDLTTR,PDNXTR,PDDSC1,PDDSC2,PDLNID,PDLNTY,DBO.JULIAN_TO_DATE(PDUPMJ)'UPMJ',PDTDAY,* -- FROM PRODDTA.F4311 WITH(NOLOCK) WHERE PDKCOO='00010'AND PDDCTO='TK' AND PDDOCO=@TK -- SELECT PRDCTO,PRDOCO,PRLTTR,PRNXTR,DBO.JULIAN_TO_DATE(PRUPMJ)'UPMJ',PRTDAY,* -- FROM PRODDTA.F43121 WITH(NOLOCK) WHERE PRKCOO='00010'AND PRDCTO='TK' AND PRDOCO=@TK -- END --IF (@SP IS NOT NULL AND @SP <>0 AND @SP<>'') OR (@SPC IS NOT NULL AND @SPC <>0 AND @SPC<>'') --> DIAGNOSTICA ORDINI -- BEGIN -- SELECT '4211'AS' ',SDLNTY,SDDOCO,SDDCTO,SDLTTR,SDNXTR,SDLNID,SDMCU,SDRORN,SDRCTO,SDLITM,SDLOCN,SDAN8,CONCAT(HOHCOD,' ',DBO.GETUDC(HOHCOD,'42','HC',1))'COD BLOCCO',SDUSER FROM PRODDTA.F4211 WITH(NOLOCK) -- LEFT JOIN PRODDTA.F4209 WITH(NOLOCK) ON SDDOCO=HODOCO AND SDDCTO=HODCTO AND SDKCOO=HOKCOO WHERE SDKCOO='00010' AND SDDCTO='SP' AND SDDOCO=@SP -- SELECT SDMCU 'MAGAZZINO',SDDOCO 'ORDINE',SDDCTO 'TIPO ORDINE',SDDELN'BOLLA',SDLNID 'N° RIGA',SDLITM 'ARTICOLO ORDINE',SECPIL 'ARTICOLO RICHIESTO',SDLTTR 'STATO PRECEDENTE',SDNXTR 'STATO SUCCESSIVO',SDUORG/1000 'RICHIESTA ODV',SDAN8'CLIENTE', -- ISNULL((SELECT SUM(LIPQOH) -- FROM PRODDTA.F41021 WITH(NOLOCK) WHERE LIITM=SECPIT AND LIMCU=SDMCU AND LIPQOH<>0 GROUP BY LIMCU, LIITM)/1000,0) 'GIACENZA',CONCAT(HOHCOD,' ',DBO.GETUDC(HOHCOD,'42','HC',1))'COD BLOCCO', -- (ISNULL((SELECT SUM(LIPQOH) FROM PRODDTA.F41021 WITH(NOLOCK) -- JOIN PRODDTA.F4108 ON IOITM=LIITM AND IOMCU=LIMCU AND IOLOTN=LILOTN WHERE LIITM=SECPIT AND LIMCU=SDMCU AND LIPQOH<>0 AND LILOTS='' AND IOMMEJ>119331 GROUP BY LIMCU, LIITM),0) -- - ISNULL((SELECT SUM(SDUORG) FROM PRODDTA.F4211 C WITH(NOLOCK) -- JOIN PRODDTA.FY594211 D ON SEKCOO=SDKCOO AND SEDOCO=SDDOCO AND SEDCTO=SDDCTO AND SELNID=SDLNID WHERE B.SECPIT=D.SECPIT AND A.SDMCU=C.SDMCU AND C.SDNXTR BETWEEN '540' AND '555'),0))/1000 'DISPONIBILE (INDICATIVO)' , -- ISNULL((SELECT SUM(SDUORG) FROM PRODDTA.F4211 C WITH(NOLOCK) -- JOIN PRODDTA.FY594211 D ON SEKCOO=SDKCOO AND SEDOCO=SDDOCO AND SEDCTO=SDDCTO AND SELNID=SDLNID WHERE B.SECPIT=D.SECPIT AND A.SDMCU=C.SDMCU AND C.SDNXTR BETWEEN '540' AND '555')/1000,0) 'IMPEGNI DA ODV' FROM PRODDTA.F4211 A WITH(NOLOCK) -- JOIN PRODDTA.FY594211 B WITH(NOLOCK) ON SEKCOO=SDKCOO AND SEDOCO=SDDOCO AND SEDCTO=SDDCTO AND SELNID=SDLNID AND SDLNTY<>'N' -- LEFT JOIN PRODDTA.F4209 WITH(NOLOCK)ON SDDOCO=HODOCO AND SDDCTO=HODCTO AND SDKCOO=HOKCOO -- WHERE SDDOCO=@SP AND SDDCTO='SP' -- END --IF (@SPC IS NOT NULL AND @SPC <>0 AND @SPC<>'') --> DIAGNOSTICA ORDINI -- BEGIN -- SELECT '4211'AS' ',SDLNTY,SDDOCO,SDDCTO,SDLTTR,SDNXTR,SDLNID,SDMCU,SDRORN,SDRCTO,SDLITM,SDLOCN,SDAN8,CONCAT(HOHCOD,' ',DBO.GETUDC(HOHCOD,'42','HC',1))'COD BLOCCO',SDUSER FROM PRODDTA.F4211 WITH(NOLOCK) -- LEFT JOIN PRODDTA.F4209 WITH(NOLOCK) ON SDDOCO=HODOCO AND SDDCTO=HODCTO AND SDKCOO=HOKCOO WHERE SDKCOO='00010' AND SDDCTO='SP' AND SDDOCO=@SPC -- SELECT SDMCU 'MAGAZZINO',SDDOCO 'ORDINE',SDDCTO 'TIPO ORDINE',SDDELN'BOLLA',SDLNID 'N° RIGA',SDLITM 'ARTICOLO ORDINE',SECPIL 'ARTICOLO RICHIESTO',SDLTTR 'STATO PRECEDENTE',SDNXTR 'STATO SUCCESSIVO',SDUORG/1000 'RICHIESTA ODV',SDAN8'CLIENTE', -- ISNULL((SELECT SUM(LIPQOH) -- FROM PRODDTA.F41021 WITH(NOLOCK) WHERE LIITM=SECPIT AND LIMCU=SDMCU AND LIPQOH<>0 GROUP BY LIMCU, LIITM)/1000,0) 'GIACENZA',CONCAT(HOHCOD,' ',DBO.GETUDC(HOHCOD,'42','HC',1))'COD BLOCCO', -- (ISNULL((SELECT SUM(LIPQOH) FROM PRODDTA.F41021 WITH(NOLOCK) -- JOIN PRODDTA.F4108 ON IOITM=LIITM AND IOMCU=LIMCU AND IOLOTN=LILOTN WHERE LIITM=SECPIT AND LIMCU=SDMCU AND LIPQOH<>0 AND LILOTS='' AND IOMMEJ>119331 GROUP BY LIMCU, LIITM),0) -- - ISNULL((SELECT SUM(SDUORG) FROM PRODDTA.F4211 C WITH(NOLOCK) -- JOIN PRODDTA.FY594211 D ON SEKCOO=SDKCOO AND SEDOCO=SDDOCO AND SEDCTO=SDDCTO AND SELNID=SDLNID WHERE B.SECPIT=D.SECPIT AND A.SDMCU=C.SDMCU AND C.SDNXTR BETWEEN '540' AND '555'),0))/1000 'DISPONIBILE (INDICATIVO)' , -- ISNULL((SELECT SUM(SDUORG) FROM PRODDTA.F4211 C WITH(NOLOCK) -- JOIN PRODDTA.FY594211 D ON SEKCOO=SDKCOO AND SEDOCO=SDDOCO AND SEDCTO=SDDCTO AND SELNID=SDLNID WHERE B.SECPIT=D.SECPIT AND A.SDMCU=C.SDMCU AND C.SDNXTR BETWEEN '540' AND '555')/1000,0) 'IMPEGNI DA ODV' FROM PRODDTA.F4211 A WITH(NOLOCK) -- JOIN PRODDTA.FY594211 B WITH(NOLOCK) ON SEKCOO=SDKCOO AND SEDOCO=SDDOCO AND SEDCTO=SDDCTO AND SELNID=SDLNID AND SDLNTY<>'N' -- LEFT JOIN PRODDTA.F4209 WITH(NOLOCK)ON SDDOCO=HODOCO AND SDDCTO=HODCTO AND SDKCOO=HOKCOO -- W