Consulta per veure un informe del contingut d’un albarà que te llista d’embalatge i lots
declare @DocEntry as int=405288; SELECT T3.DocNum ,T0.LineNum Linia ,CAST(T0.DocDate as DATE) Data ,T0.ItemCode ,T8.DistNumber U_Lote ,CAST(T8.ExpDate as DATE) Caducitat ,ABS(T7.Quantity) QuantitatRealLot ,T1.SalUnitMsr Unitats ,T1.U_INTRX_EDI_CUEXP UEmbalatge ,T0.WhsCode Magatzem ,T0.Quantity QuantitatReal ,T1.U_intrx_diasCadALE VidaUtil ,DATEADD(day,T1.U_intrx_diasCadALE,GETDATE()) DataLimit ,CASE WHEN CAST(DATEADD(day,T1.U_intrx_diasCadALE,GETDATE()) as DATE) > CAST(T8.ExpDate as DATE) THEN 'Caducat' ELSE 'Correcte' END EstatLot ,CASE WHEN T1.SalUnitMsr='Kg' THEN ROUND(ABS(T7.Quantity)/T1.U_INTRX_EDI_CUEXP,0)*T1.U_INTRX_EDI_CUEXP ELSE ABS(T7.Quantity) END Quantitat ,CASE WHEN T1.SalUnitMsr<>'Kg' THEN CASE WHEN T7.Quantity % T1.U_INTRX_EDI_CUEXP=0 THEN 'Correcte' ELSE 'Error' END ELSE 'Correcte' END EstatUEmbalatge FROM DLN1 T0 JOIN DLN8 T6 on T6.DocEntry=T0.DocEntry and T6.ItemCode=T0.ItemCode JOIN DLN7 T4 on T4.DocEntry=T0.DocEntry and T4.PackageNum=T6.PackageNum JOIN OITM T1 on T1.ItemCode=T0.ItemCode JOIN OITL T3 on T3.DocEntry=T0.DocEntry and T3.DocType=15 and T3.DocLine=T0.LineNum JOIN ITL1 T7 on T7.LogEntry=T3.LogEntry JOIN OBTN T8 on T8.SysNumber=T7.SysNumber and T8.ItemCode=T7.ItemCode JOIN OPKG T5 on T5.PkgType=T4.PackageTyp WHERE T0.DocEntry=@DocEntry ORDER BY T0.LineNum