Query para Articulos con Costo

[former_member528316]

Estimados,

Junto con Saludar, necesito me puedan guiar, necesito hacer una Query que contenga El codigo del Articulo, la Descripción, el costo, y todos los precios que tiene en las diferentes listas que existen es SAP (Existen 8 Tipos de listas de precios distintas).

Les Agradecería su ayuda.

Pablo Aguirre

[Former Member · Accepted solution]

Hola Pablo

Prueba con esta qry:

SELECT

T0.ItemCode

,T0.ItemName

,T0.AvgPrice

,T2.ListName

,T1.Price

FROM OITM T0

INNER JOIN ITM1 T1 on T1.ItemCode = T0.ItemCode

INNER JOIN OPLN T2 ON T2.ListNum = T1.PriceList

WHERE T1.Price > 0

Slds y espero te sea de utilidad.

--

Ana María Mejía

[former_member228470]

Hola Pablo, yo hice este para la empresa donde laboro.  Tiene filtros por articulos, grupo de articulos y proveedor.   En caso de que quieras que los traiga todos los dejas en blanco.

/* SELECT FROM [dbo].[OITM] T0 */      

DECLARE @productodesde AS NVARCHAR(30)     

/* WHERE */   SET @productodesde = /* T0.ItemCode */ '[%0]'             

/* SELECT FROM [dbo].[OITM] T0 */      

DECLARE @productohasta AS NVARCHAR(30)     

/* WHERE */   SET @productohasta = /* T0.ItemCode */ '[%1]' 

/* SELECT FROM [dbo].[OCRD] T3 */      

DECLARE @proveedordesde AS NVARCHAR(50)     

/* WHERE */   SET @proveedordesde = /* T3.CardName */ '[%2]'             

/* SELECT FROM [dbo].[OCRD] T3 */      

DECLARE @proveedorhasta AS NVARCHAR(50)     

/* WHERE */   SET @proveedorhasta = /* T3.Cardname */ '[%3]' 

/* SELECT FROM [dbo].[OITB] T1 */     

DECLARE @grupodesde AS NVARCHAR(30)     

/* WHERE */   SET @grupodesde = /* T1.ItmsGrpNam */ '[%4]'           

SELECT T0.ItemCode AS 'Referencia',  T0.ItemName AS 'Descripción', T0.Cardcode, T3.CardName, T0.CodeBars AS 'Codigo Barra', 

T0.OnHand AS 'Stock',

T2.AvgPrice 'Costo Promedio',  T0.LstEvlPric 'Ult.Precio Det.', T0.LastPurPrc 'Ult.Precio Compra',

(Select Price from ITM1 where PriceList = 2 and ItemCode = T0.ItemCode) AS 'P.#1',

(Select Price from ITM1 where PriceList = 3 and ItemCode = T0.ItemCode) AS 'P.#2 ',

(Select Price from ITM1 where PriceList = 4 and ItemCode = T0.ItemCode) AS 'P.#3 ',

(Select Price from ITM1 where PriceList = 5 and ItemCode = T0.ItemCode) AS 'P.#4 ',

(Select Price from ITM1 where PriceList = 6 and ItemCode = T0.ItemCode) AS 'P.#5 ',

(Select Price from ITM1 where PriceList = 7 and ItemCode = T0.ItemCode) AS 'P.#6 ',

(Select Price from ITM1 where PriceList = 8 and ItemCode = T0.ItemCode) AS 'P.#7 ',

(Select Price from ITM1 where PriceList = 9 and ItemCode = T0.ItemCode) AS 'P.#8'

FROM OITM T0  

INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod

INNER JOIN OITW T2 ON T0.ItemCode   = T2.ItemCode and T2.WhsCode = 03

INNER JOIN OCRD T3 ON T0.CardCode =  T3.CardCode

WHERE      

(T1.ItmsGrpNam =   @grupodesde    OR @grupodesde    = RTRIM(ISNULL('','')))   AND  

(T0.ItemCode  >=   @productodesde OR @productodesde = RTRIM(ISNULL('','')))   AND     

(T0.ItemCode  <=   @productohasta OR @productohasta = RTRIM(ISNULL('','')))   AND

(T3.CardName  >=   @proveedordesde OR @proveedordesde = RTRIM(ISNULL('','')))   AND     

(T3.CardName  <=   @proveedorhasta OR @proveedorhasta = RTRIM(ISNULL('','')))

GROUP BY T1.ItmsGrpNam, T0.ItemCode, T0.CodeBars, T0.CardCode, T3.CardName, T0.ItemName, T0.LstEvlPric, T0.LastPurPrc, T2.AvgPrice, t0.onhand, T0.VatGroupPu,T0.VatGourpSa

ORDER BY 1,2,3

for browse