drop view VW_CUSERGROUPS 
go
CREATE VIEW VW_CUSERGROUPS AS
SELECT USERID,USERNAME,GROUPID,GROUPNAME
FROM SE_GROUPREC 
CROSS JOIN SE_USERREC
go

drop view VW_GRUPOSPOSIBLES
go
CREATE VIEW VW_GRUPOSPOSIBLES AS
--MUESTRA TODOS LOS GRUPOS A LOS QUE PUEDE PERTENECER UN USUARIO Y QUE NO PERTENECE YA
--NECESITA LA VISTA VW_CUSERGROUPS QUE CONTIENE UN CROOS JOIN DE USUARIOS Y GRUPOS
SELECT GROUPID,GROUPNAME,USERID,USERNAME 
FROM VW_CUSERGROUPS A
WHERE GROUPID NOT IN (SELECT GROUPID FROM SE_USERGROUP WHERE A.USERID=USERID)
GO


drop view VW_DERECHOSGRUPOS
go
CREATE VIEW VW_DERECHOSGRUPOS AS
SELECT SE_ACCESSDETRIGHTS.SCREENID, SE_USERGROUP.USERID, SE_ACCESSDETRIGHTS.DELETERIGHTS, SE_ACCESSDETRIGHTS.INSERTRIGHTS, SE_ACCESSDETRIGHTS.UPDATERIGHTS, SE_ACCESSDETRIGHTS.VIEWRIGHTS, SE_ACCESSDETRIGHTS.PRINTRIGHTS, SE_ACCESSDETRIGHTS.TSTAMP
FROM SE_ACCESSDETRIGHTS, SE_USERGROUP, SE_GROUPREC
WHERE SE_USERGROUP.GROUPID = SE_GROUPREC.GROUPID
go

DROP VIEW vw_derechosusuarios
GO

CREATE VIEW vw_derechosusuarios
AS
SELECT SE_ACCESSDETRIGHTS.SCREENID, 
    SE_SCREEN.SCREENNAME, SE_USERREC.USERID, 
    SE_ACCESSDETRIGHTS.VIEWRIGHTS, 
    SE_ACCESSDETRIGHTS.INSERTRIGHTS, 
    SE_ACCESSDETRIGHTS.UPDATERIGHTS, 
    SE_ACCESSDETRIGHTS.DELETERIGHTS, 
    SE_ACCESSDETRIGHTS.PRINTRIGHTS
FROM SE_ACCESSDETRIGHTS INNER JOIN
    SE_SCREEN ON 
    SE_SCREEN.SCREENID = SE_ACCESSDETRIGHTS.SCREENID INNER
     JOIN
    SE_USERREC ON 
    SE_ACCESSDETRIGHTS.USERID = SE_USERREC.USERID
GO

drop view VW_USUARIOSGRUPOS 
go

CREATE VIEW VW_USUARIOSGRUPOS AS
SELECT ug.USERID, ug.GROUPID, g.GROUPNAME, g.TSTAMP
from se_usergroup ug join se_grouprec g on 
ug.GROUPID =g.GROUPID
go

drop view vw_derechos
go

CREATE VIEW vw_derechos
AS
SELECT screenid, userid, viewrights, insertrights, updaterights, 
    deleterights, printrights
FROM vw_derechosusuarios
UNION
SELECT screenid, userid, viewrights, insertrights, updaterights, 
    deleterights, printrights
FROM vw_derechosusuariosxgrupos

go



drop view VW_LISTAPRECIOS
go

CREATE VIEW VW_LISTAPRECIOS
AS
SELECT CAT.CLASSID, CAT.DESCR AS DESCR_CAT, 
    IN_SUBCATEGORY.DESCR AS DESCR_SUBCAT, 
    TAXS.VALUE AS valueigv, TYPE.INVTYPEID, 
    TYPE.DESCR AS DESCR_TYPE, INV.INVTIDSKU, INV.DESCR, 
    INV.STKUNITID, SITE.SITEID, SITE.NAME, DAT.PRECLVID, 
    DAT.SLS_PRICE, 
    IN_PRICELEVELTYPE.DESCR AS nombre_lista, 
    IN_PRICELEVELTYPE.curyid AS moneda_lista, DAT.QTYAVAIL, 
    INV.TAXIGV, IN_SUBCATEGORY.CLASSUBID
FROM SITE INNER JOIN
    IN_INVENTORY INV INNER JOIN
    IN_INVENTORYDAT DAT ON 
    INV.INVTIDSKU = DAT.INVTIDSKU INNER JOIN
    IN_INVENTORYTYPE TYPE ON 
    INV.INVTYPEID = TYPE.INVTYPEID ON 
    SITE.SITEID = DAT.SITEID INNER JOIN
    IN_PRICELEVELTYPE ON 
    DAT.PRECLVID = IN_PRICELEVELTYPE.PRECLVID INNER JOIN
    IN_SUBCATEGORY INNER JOIN
    IN_CATEGORY CAT ON 
    IN_SUBCATEGORY.CLASSID = CAT.CLASSID ON 
    INV.CLASSUBID = IN_SUBCATEGORY.CLASSUBID LEFT OUTER JOIN
    TAXS ON INV.TAXIGV = TAXS.TAXID
go


drop view VW_LISTASTOCK
go
CREATE VIEW VW_LISTASTOCK
AS
SELECT CAT.CLASSID, CAT.DESCR AS DESCR_CAT, 
    IN_SUBCATEGORY.DESCR AS DESCR_SUBCAT, 
    INV.INVTIDSKU, INV.DESCR, INV.STKUNITID, SITE.SITEID, 
    SITE.NAME, DAT.QTYAVAIL, 
    IN_SUBCATEGORY.CLASSUBID
FROM SITE INNER JOIN
    IN_INVENTORY INV INNER JOIN
    IN_INVENTORYDAT DAT ON 
    INV.INVTIDSKU = DAT.INVTIDSKU ON 
    SITE.SITEID = DAT.SITEID INNER JOIN
    IN_SUBCATEGORY INNER JOIN
    IN_CATEGORY CAT ON 
    IN_SUBCATEGORY.CLASSID = CAT.CLASSID ON 
    INV.CLASSUBID = IN_SUBCATEGORY.CLASSUBID
go

drop view vw_ingresoscompraproveedor
go
CREATE VIEW vw_ingresoscompraproveedor
AS
SELECT IN_INTRAHEAD.SITEID, IN_INTRAHEAD.BATNBR, 
    IN_INTRAHEAD.TYPEDOCID, IN_INTRAHEAD.VENDIDSS, 
    IN_INTRAHEAD.DOCTYPEID, IN_INTRAHEAD.DOCSTATUSID, 
    IN_INTRAHEAD.REFNBR, IN_INTRAHEAD.CURYRATE, 
    IN_INTRAHEAD.TOCURYID, IN_INTRAHEAD.SITEPROCES, 
    IN_INTRAHEAD.NBRORDER, 
    IN_INTRAHEAD.DATENBRORDER, 
    IN_INTRAHEAD.USERIDORDER, IN_INTRAHEAD.INTRDATE, 
    IN_INTRAHEAD.INTRDATEPROCE, IN_INTRAHEAD.SITEDEST, 
    IN_INTRAHEAD.INTRTOTPEN, IN_INTRAHEAD.INTRTAXPEN, 
    IN_INTRAHEAD.INTRTOTUSD, IN_INTRAHEAD.INTRTAXUSD, 
    IN_INTRAHEAD.PERPOST, IN_TYPEDOC.STKBUY, 
    IN_TYPEDOC.DESCR, IN_VENDOR.DESCR AS NPROVEEDOR, 
    IN_VENDOR.TAXREGNBR AS RUC, SITE.NAME, 
    DOCTYPE.MINDES AS tdocumento, 
    CURRENCY.DESCRMONEY
FROM IN_INTRAHEAD INNER JOIN
    SITE ON IN_INTRAHEAD.SITEID = SITE.SITEID INNER JOIN
    IN_VENDOR ON 
    IN_INTRAHEAD.VENDIDSS = IN_VENDOR.VENDIDSS INNER JOIN
    IN_TYPEDOC ON 
    IN_INTRAHEAD.TYPEDOCID = IN_TYPEDOC.TYPEDOCID INNER
     JOIN
    DOCTYPE ON 
    IN_INTRAHEAD.DOCTYPEID = DOCTYPE.DOCTYPEID INNER JOIN
    CURRENCY ON 
    IN_INTRAHEAD.FROMCURYID = CURRENCY.CURYID
go

drop view vw_derechosusuariosxgrupos
go
CREATE VIEW vw_derechosusuariosxgrupos
AS
SELECT SE_ACCESSDETRIGHTS.SCREENID, 
    SE_SCREEN.SCREENNAME, SE_USERGROUP.USERID, 
    SE_ACCESSDETRIGHTS.VIEWRIGHTS, 
    SE_ACCESSDETRIGHTS.INSERTRIGHTS, 
    SE_ACCESSDETRIGHTS.UPDATERIGHTS, 
    SE_ACCESSDETRIGHTS.DELETERIGHTS, 
    SE_ACCESSDETRIGHTS.PRINTRIGHTS
FROM SE_ACCESSDETRIGHTS INNER JOIN
    SE_SCREEN ON 
    SE_ACCESSDETRIGHTS.SCREENID = SE_SCREEN.SCREENID INNER
     JOIN
    SE_USERGROUP INNER JOIN
    SE_GROUPREC ON 
    SE_USERGROUP.GROUPID = SE_GROUPREC.GROUPID ON 
    SE_ACCESSDETRIGHTS.USERID = SE_GROUPREC.GROUPID
go

DROP VIEW VW_BuscarArticulos
GO

Create View VW_BuscarArticulos
as
  Select INVTIDSKU,DESCR from in_inventory

GO


DROP VIEW VW_KARDEXFS210
GO

Create View VW_KARDEXFS210
as
SELECT INTRAMOV.SITEID,IN_INVENTORY.CLASSID, 
    INTRAMOV.INTRDATEPROCE,INTRAMOV.BATNBR, 
    DOCTYPE.MINDES,INTRAMOV.REFNBR, 
    INTRAMOV.TYPEDOCID, IN_TYPEDOC.DESCR, 
    SUM(CASE SWTINOUT WHEN  1 THEN QTY WHEN 0 THEN
    (QTY * - 1) END) AS QTYRESULT
FROM INTRAMOV INNER JOIN
    DOCTYPE ON 
    INTRAMOV.DOCTYPEID = DOCTYPE.DOCTYPEID INNER JOIN
    IN_TYPEDOC ON 
    INTRAMOV.TYPEDOCID = IN_TYPEDOC.TYPEDOCID INNER JOIN
    SITE ON INTRAMOV.SITEID = SITE.SITEID INNER JOIN
    IN_INVENTORY ON 
    INTRAMOV.INVTIDSKU = IN_INVENTORY.INVTIDSKU
GROUP BY INTRAMOV.SITEID,IN_INVENTORY.CLASSID, 
    INTRAMOV.INTRDATEPROCE,INTRAMOV.BATNBR, 
    DOCTYPE.MINDES,INTRAMOV.REFNBR, 
    INTRAMOV.TYPEDOCID, IN_TYPEDOC.DESCR 
GO


DROP VIEW VW_KARDEXFS210SUBCATEG
GO

Create View VW_KARDEXFS210SUBCATEG
as
SELECT INTRAMOV.SITEID,IN_INVENTORY.CLASSUBID, 
    INTRAMOV.INTRDATEPROCE,INTRAMOV.BATNBR, 
    DOCTYPE.MINDES,INTRAMOV.REFNBR, 
    INTRAMOV.TYPEDOCID, IN_TYPEDOC.DESCR, 
    SUM(CASE SWTINOUT WHEN  1 THEN QTY WHEN 0 THEN
    (QTY * - 1) END) AS QTYRESULT
FROM INTRAMOV INNER JOIN
    DOCTYPE ON 
    INTRAMOV.DOCTYPEID = DOCTYPE.DOCTYPEID INNER JOIN
    IN_TYPEDOC ON 
    INTRAMOV.TYPEDOCID = IN_TYPEDOC.TYPEDOCID INNER JOIN
    SITE ON INTRAMOV.SITEID = SITE.SITEID INNER JOIN
    IN_INVENTORY ON 
    INTRAMOV.INVTIDSKU = IN_INVENTORY.INVTIDSKU
GROUP BY INTRAMOV.SITEID,IN_INVENTORY.CLASSUBID, 
    INTRAMOV.INTRDATEPROCE,INTRAMOV.BATNBR, 
    DOCTYPE.MINDES,INTRAMOV.REFNBR, 
    INTRAMOV.TYPEDOCID, IN_TYPEDOC.DESCR
GO

DROP VIEW VW_ListarArticulos
GO
Create View VW_ListarArticulos
AS
  SELECT INVTIDSKU, DESCR, TAXISC, TAXIGV, CLASSUBID, 
    INVTYPEID, STKUNITID, CLASSID, WTSTK1, STKFUELS, 
    STKSTORE, STKOTHER, STKITEM, KIT, DISCDSCTO1, 
    DISCDSCTO2, DISCMTO, STKRECEPT, STKSALES, STATUS, 
    CONSIGNMENT, DISASSEMBLE
  FROM IN_INVENTORY
GO

DROP VIEW VW_SITECATEGORY
GO
CREATE VIEW VW_SITECATEGORY
AS
select SITE.SITEID,IN_CATEGORY.CLASSID,"" AS [INTRDATEPROCE],"" AS [BATNBR],"" AS [MINDES],"" AS [REFNBR],
       "" AS [TIPO],"Total Ventas Expresadas en Cantidad " AS [DESCR] FROM SITE CROSS JOIN IN_CATEGORY 

GO
DROP VIEW VW_SITESUBCATEGORY
GO
CREATE VIEW VW_SITESUBCATEGORY
AS
select SITE.SITEID,IN_SUBCATEGORY.CLASSUBID,"" AS [INTRDATEPROCE],"" AS [BATNBR],"" AS [MINDES],"" AS [REFNBR],
       "" AS [TIPO],"Total Ventas Expresadas en Cantidad " AS [DESCR] FROM SITE CROSS JOIN IN_SUBCATEGORY 

GO