
DROP VIEW VW_RPT_VTAS_ART_PEN
GO
CREATE VIEW VW_RPT_VTAS_ART_PEN
AS
SELECT S.SITEID,A.NAME,S.DOCTYPEID,TD.MINDES,S.NBRDOCUMENT,S.DATEDOC,S.DATEPROCESALES,O.SALESPERID,V.SALESPERNAME,S.INVTIDSKU,I.DESCR,I.CLASSID,C.DESCR AS DESCRC,I.CLASSUBID,SC.DESCR AS DESCRS,D.QTYAVAIL,
CASE (S.DOCSTATUSID) WHEN 'AN' THEN 0 ELSE CASE(S.DOCTYPEID) WHEN (SELECT DOCTYPEIDNC FROM OP_SETUP) THEN (-1)*S.QTY ELSE S.QTY END END AS Cantidad,
CASE (S.DOCSTATUSID) WHEN 'AN' THEN 0 ELSE CASE(S.DOCTYPEID) WHEN (SELECT DOCTYPEIDNC FROM OP_SETUP) THEN (-1)*S.TOTALPEN ELSE S.TOTALPEN END END AS Total 
FROM (((((((OP_SALESDET AS S JOIN IN_INVENTORY AS I
ON S.INVTIDSKU=I.INVTIDSKU) JOIN IN_INVENTORYDAT AS D
ON S.INVTIDSKU=D.INVTIDSKU AND S.SITEID=D.SITEID) JOIN IN_CATEGORY AS C
ON I.CLASSID=C.CLASSID) JOIN IN_SUBCATEGORY AS SC
ON I.CLASSUBID=SC.CLASSUBID) JOIN SITE AS A
ON S.SITEID=A.SITEID) JOIN DOCTYPE AS TD
ON S.DOCTYPEID=TD.DOCTYPEID) JOIN OP_SALES AS O
ON S.DOCTYPEID=O.DOCTYPEID AND S.NBRDOCUMENT=O.NBRDOCUMENT AND S.SITEID=O.SITEID) JOIN OP_SALESPERSON AS V
ON O.SALESPERID=V.SALESPERID  

GO

DROP VIEW VW_RPT_VTAS_ART_USD
GO
CREATE VIEW VW_RPT_VTAS_ART_USD
AS
SELECT S.SITEID,A.NAME,S.DOCTYPEID,TD.MINDES,S.NBRDOCUMENT,S.DATEDOC,S.DATEPROCESALES,O.SALESPERID,V.SALESPERNAME,S.INVTIDSKU,I.DESCR,I.CLASSID,C.DESCR AS DESCRC,I.CLASSUBID,SC.DESCR AS DESCRS,D.QTYAVAIL,
CASE (S.DOCSTATUSID) WHEN 'AN' THEN 0 ELSE CASE(S.DOCTYPEID) WHEN (SELECT DOCTYPEIDNC FROM OP_SETUP) THEN (-1)*S.QTY ELSE S.QTY END END AS Cantidad,
CASE (S.DOCSTATUSID) WHEN 'AN' THEN 0 ELSE CASE(S.DOCTYPEID) WHEN (SELECT DOCTYPEIDNC FROM OP_SETUP) THEN (-1)*S.TOTALUSD ELSE S.TOTALUSD END END AS Total 
FROM (((((((OP_SALESDET AS S JOIN IN_INVENTORY AS I
ON S.INVTIDSKU=I.INVTIDSKU) JOIN IN_INVENTORYDAT AS D
ON S.INVTIDSKU=D.INVTIDSKU AND S.SITEID=D.SITEID) JOIN IN_CATEGORY AS C
ON I.CLASSID=C.CLASSID) JOIN IN_SUBCATEGORY AS SC
ON I.CLASSUBID=SC.CLASSUBID) JOIN SITE AS A
ON S.SITEID=A.SITEID) JOIN DOCTYPE AS TD
ON S.DOCTYPEID=TD.DOCTYPEID) JOIN OP_SALES AS O
ON S.DOCTYPEID=O.DOCTYPEID AND S.NBRDOCUMENT=O.NBRDOCUMENT AND S.SITEID=O.SITEID) JOIN OP_SALESPERSON AS V
ON O.SALESPERID=V.SALESPERID  
GO

SELECT * FROM VW_RPT_VTAS_ART_PEN

