select IT.CODITM, IT.CODITMALTERNATIVO, IT.DESCRIPCION, IT.DESCRIPCIONLARGA, IAW.CODATRVAL AS WEB, IAR.CODATRVAL AS RUBCOD, IARV.DESCRIPCION AS RUBDES, IAS.CODATRVAL AS SUBCOD, IASV.DESCRIPCION AS SUBDES, IAM.CODATRVAL AS MARCOD, IAMV.DESCRIPCION AS MARDES, IAO.CODATRVAL AS OFERTA, IAN.CODATRVAL AS NUEVO, IM.IMAGEN, EXI.EXISTENCIA,
CASE WHEN LPC01.PRECIO > 0 THEN ROUND(ROUND(LPC01.PRECIO * CASE WHEN WPS.FACTORCONTADO IS NULL THEN WPR.FACTORCONTADO ELSE WPS.FACTORCONTADO END, 0) / 500, 0) * 500 ELSE ROUND(ROUND(LPB01.PRECIO * CASE WHEN WPS.FACTORCONTADO IS NULL THEN WPR.FACTORCONTADO ELSE WPS.FACTORCONTADO END, 0) / 500, 0) * 500 END AS CONTADO,
CASE WHEN LPC01.PRECIO > 0 THEN ROUND(ROUND(LPC01.PRECIO * CASE WHEN WPS.FACTORCREDITO IS NULL THEN WPR.FACTORCREDITO ELSE WPS.FACTORCREDITO END, 0) / 500, 0) * 500 ELSE ROUND(ROUND(LPB01.PRECIO * CASE WHEN WPS.FACTORCREDITO IS NULL THEN WPR.FACTORCREDITO ELSE WPS.FACTORCREDITO END, 0) / 500, 0) * 500 END AS CREDITO from ITEMS AS IT left join (SELECT CODITM, MAX(VIGENCIA) AS VIGENCIA, PRECIO FROM LISTASPRECIOS WHERE (CODLIS = 'B01') GROUP BY CODITM, PRECIO) AS LPB01 on [IT].[CODITM] = [LPB01].[CODITM] left join (SELECT CODITM, MAX(VIGENCIA) AS VIGENCIA, PRECIO FROM LISTASPRECIOS WHERE (CODLIS = 'C01') GROUP BY CODITM, PRECIO) AS LPC01 on [IT].[CODITM] = [LPC01].[CODITM] left join ITEMSATRIB AS IAW on [IT].[CODITM] = [IAW].[CODITM] and [IAW].[CODATR] = WEB left join ITEMSATRIB AS IAO on [IT].[CODITM] = [IAO].[CODITM] and [IAO].[CODATR] = OFE left join ITEMSATRIB AS IAN on [IT].[CODITM] = [IAN].[CODITM] and [IAN].[CODATR] = NEW left join ITEMSATRIB AS IAR on [IT].[CODITM] = [IAR].[CODITM] and [IAR].[CODATR] = AP1 left join ATRIBUTOSVAL AS IARV on [IARV].[CODATR] = [IAR].[CODATR] and [IARV].[CODATRVAL] = IAR.CODATRVAL left join ITEMSATRIB AS IAS on [IT].[CODITM] = [IAS].[CODITM] and [IAS].[CODATR] = AP4 left join ATRIBUTOSVAL AS IASV on [IASV].[CODATR] = [IAS].[CODATR] and [IASV].[CODATRVAL] = IAS.CODATRVAL left join ITEMSATRIB AS IAM on [IT].[CODITM] = [IAM].[CODITM] and [IAM].[CODATR] = MAR left join ATRIBUTOSVAL AS IAMV on [IAMV].[CODATR] = [IAM].[CODATR] and [IAMV].[CODATRVAL] = IAM.CODATRVAL left join (SELECT CODATRRUB, CODATRRUBVAL, FACTORCONTADO, FACTORCREDITO FROM WEB_POLITICAS WHERE CODATRSUB IS NULL) AS WPR on [IAR].[CODATR] = [WPR].[CODATRRUB] and [IAR].[CODATRVAL] = WPR.CODATRRUBVAL left join (SELECT CODATRSUB, CODATRSUBVAL, FACTORCONTADO, FACTORCREDITO FROM WEB_POLITICAS WHERE CODATRSUB IS NOT NULL) AS WPS on [IAS].[CODATR] = [WPS].[CODATRSUB] and [IAS].[CODATRVAL] = WPS.CODATRSUBVAL left join (SELECT EX.CODITM, SUM(EX.CANTIDAD) AS EXISTENCIA FROM (SELECT M.CODITM, M.TIPCANTI * M.CANTIDAD AS CANTIDAD FROM MVSITEMS AS M LEFT JOIN TRANSAC AS T ON M.NROTRANS = T.NROTRANS WHERE M.ORIGEN IN('F','C','I' , 'E') AND T.NROTRANSELIM IS NULL ) AS EX GROUP BY EX.CODITM) AS EXI on [IT].[CODITM] = [EXI].[CODITM] left join ITEMSPIC AS IM on [IT].[CODITM] = [IM].[CODITM] where IT.ITEMPREFI = 'B' AND IAW.CODATRVAL = 'S' AND IAO.CODATRVAL = 'S' AND
(LPB01.VIGENCIA IS NULL OR LPB01.VIGENCIA IN (SELECT MAX(VIGENCIA) AS VIGENCIA FROM LISTASPRECIOS AS LX WHERE CODLIS = 'B01' AND CODITM = IT.CODITM)) AND
(LPC01.VIGENCIA IS NULL OR LPC01.VIGENCIA IN (SELECT MAX(VIGENCIA) AS VIGENCIA FROM LISTASPRECIOS AS LX WHERE CODLIS = 'C01' AND CODITM = IT.CODITM)) and [IM].[IMAGEN] is not null