DynamicsGP 2018 Reporte: Kardex de inventario

 Reporte 

Kardex de inventario, el movimiento que tiene un ítem dentó de empresa (Transferencias, ventas, devoluciones, ingresos, ajustes).

Parámetros: 

• Nombre del ítem
• Lugar del moviente 

SELECT DISTINCT a1.TRXSORCE, a1.DOCNUMBR,  CAST(a1.DOCDATE AS DATE) as DOCDATE , a1.DOCTYPE, 
CASE WHEN a1.DOCTYPE=1 THEN 'Ajustes' ELSE 
CASE WHEN  a1.DOCTYPE=2 THEN 'Varianza' ELSE 
CASE WHEN a1.DOCTYPE=3 THEN 'Transferencia' ELSE 
CASE WHEN a1.DOCTYPE=4 THEN 'Recibido' ELSE
CASE WHEN a1.DOCTYPE=5 THEN 'Devoluciones' ELSE 
CASE WHEN a1.DOCTYPE=6 THEN 'Ventas' ELSE 
CASE WHEN a1.DOCTYPE=7 THEN 'Ensamblaje' END END END END END END END AS  DOCTYPEDes, 
TRIM(a3.ITEMDESC) as ITEMDESC, TRIM(a3.ITMSHNAM) as ITMSHNAM, TRIM(a1.ITEMNMBR) as ITEMNMBR, a1.UOFM,  
a5.cantIngTrans,  a5.cantEgreTrans,  a1.UNITCOST,a7.UNITPRCE, a1.EXTDCOST, TRIM(a1.TRXLOCTN) as TRXLOCTN, TRIM(a1.TRNSTLOC) as  TRNSTLOC, 
a1.DEX_ROW_ID, a6.DOCID,a6.SOPNUMBE , a6.CUSTNAME  
FROM IV30300 as a1
INNER JOIN IV00101 as a3 ON (a1.ITEMNMBR = a3.ITEMNMBR)
INNER JOIN IV00102 as a2 ON (a1.ITEMNMBR = a2.ITEMNMBR AND a1.TRXLOCTN = a2.LOCNCODE)
INNER JOIN (
/* EGRESOS */
SELECT a1.TRXSORCE, a1.DOCNUMBR,  CAST(a1.DOCDATE AS DATE) as DOCDATE , a1.DOCTYPE, 
a1.ITEMNMBR , a1.UNITCOST, a1.TRXLOCTN, a1.TRNSTLOC, 
a1.DEX_ROW_ID , 
CASE WHEN a1.DOCTYPE IN (1,5)  THEN a1.TRXQTY ELSE 0 END as cantIngTrans, 
CASE WHEN a1.DOCTYPE = 3 THEN (a1.TRXQTY*-1) ELSE 
CASE WHEN a1.DOCTYPE IN (1,5) THEN 0 ELSE a1.TRXQTY END END as cantEgreTrans
FROM IV30300 as a1 
INNER JOIN IV00101  as a2 ON (a1.ITEMNMBR = a2.ITEMNMBR)
WHERE (a2.ITEMNMBR =@NombreItem)  AND a1.TRXLOCTN = @LugarVenta
UNION
/* INGRESOS */
SELECT a1.TRXSORCE, a1.DOCNUMBR,  CAST(a1.DOCDATE AS DATE) as DOCDATE , a1.DOCTYPE, 
a1.ITEMNMBR , a1.UNITCOST, a1.TRXLOCTN, a1.TRNSTLOC, 
a1.DEX_ROW_ID , a1.TRXQTY   as cantIngTrans , 0 as cantEgreTrans 
FROM IV30300 as a1 
INNER JOIN IV00101  as a2 ON (a1.ITEMNMBR = a2.ITEMNMBR)
WHERE (a2.ITEMNMBR =@NombreItem)  AND a1.TRNSTLOC  = @LugarVenta
)as a5 ON (  a1.DOCNUMBR=a5.DOCNUMBR and a1.ITEMNMBR = a5.ITEMNMBR AND a1.DEX_ROW_ID = a5.DEX_ROW_ID)
LEFT OUTER JOIN SOP30200 as a6 ON (a1.DOCNUMBR = a6.SOPNUMBE)
LEFT OUTER JOIN SOP30300 as a7 ON (a6.SOPNUMBE = a7.SOPNUMBE and a1.ITEMNMBR=a7.ITEMNMBR) 
ORDER BY a1.DEX_ROW_ID ASC