INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('IPT','PERMITIR INGRESO PARA TERCEROS - FACTURACION DE SERVICIOS','BOOLEAN','0',5,'MAIN')
GO
INSERT INTO ClaseTar (IdClase,NomClase,Inactivo)
 VALUES ('CSE','COSTO DE SERVICIOS',0)
GO
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMDFCR','COS','Modificar Costo de Servicios - Ingreso para terceros')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMDFCO','COS','Modificar Costo de Servicios - Ingreso para terceros')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMDFCC','COS','Modificar Costo de Servicios - Ingreso para terceros')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryClaseTarImp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryClaseTarImp]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardexSubOpe]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryKardexSubOpe]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexSubOpe]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexSubOpe]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexFco]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexFco]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexFco] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmSalidas,tmVrPrecio,tmListaPrec,tmTarifaDct,tmVrDcto,tmTarifaIva,tmTarifaIco
,((tmVrPrecio*tmSalidas)-tmVrDcto)+tmVrIva+tmVrImpCon AS ValorTotal,((tmVrPrecio*tmSalidas)-tmVrDcto)+tmVrIva AS ValorNeto
,tmVrIva,tmVrImpCon,tmUnidades,tmIdUnd,Unidad,tmIdBodega,Bodega,tmReferencia,tmDescripcion,tmVrBruto,tmVrUnitario,tmVrUnitario*tmSalidas AS CostoTotal
,tmIdVend,tmComision,tmCodTarDct,tmCodTarIva,tmCodTarCom,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmServcios,Tanques,tmCdTanque
,tmEsCombo,tmEsProdBase,tmItemCbo,tmTipDoc,tmDocumento,tmIdCia,tmCdOperario,tmComisnOper,tmCodTarCmc,tmpVehiculo,tmRec_Costo
FROM tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto 
 INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 LEFT JOIN UndMed AS U ON K.tmIdUnd=U.IdUnd
 WHERE  tmNumero=@pmtmNumero AND tmEsProdBase=0
 ORDER BY  tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexSubOpe] @pmtmNumero VARCHAR(5)
 AS 
 SELECT IdSubgrupo,tmIdProducto,DescripProd,TipoRef,Combo,tmEsProdBase,IdProv,ExcluidoImp,tmCdOperario
,COUNT(tmItem) AS SCANT,SUM(tmEntradas) AS SENT,SUM(tmSalidas) AS SSAL
,SUM(tmEntradas*tmVrUnitario) AS SCOSENT,SUM(tmSalidas*tmVrUnitario) AS SCOSSAL
,SUM(tmVrPrecio*tmEntradas) AS SVALENT,SUM(tmVrPrecio*tmSalidas) AS SVALSAL
,SUM(tmVrIva) AS SIVA,SUM(tmVrDcto) AS SDCT,SUM(tmVrRete) AS SRET,SUM(tmVrIca) AS SICA
,SUM(tmVrBruto*tmEntradas) AS SBRUENT,SUM(tmVrBruto*tmSalidas) AS SBRUSAL
,SUM(tmEntradas*tmImpGlobal) AS SGLOENT,SUM(tmSalidas*tmImpGlobal) AS SGLOSAL
,SUM(tmSobretasa*tmEntradas) AS SSOBENT,SUM(tmSobretasa*tmSalidas) AS SSOBSAL
,SUM(tmTasaNac*tmEntradas) AS SNACENT,SUM(tmTasaNac*tmSalidas) AS SNACSAL
,SUM(tmTasaDep*tmEntradas) AS SDEPENT,SUM(tmTasaDep*tmSalidas) AS SDEPSAL
,SUM(tmTasaMun*tmEntradas) AS SMUNENT,SUM(tmTasaMun*tmSalidas) AS SMUNSAL
,SUM(tmSoldicom*tmEntradas) AS SSOLENT,SUM(tmSoldicom*tmSalidas) AS SSOLSAL
,SUM(tmOtroImpto*tmEntradas) AS SOTRENT,SUM(tmOtroImpto*tmSalidas) AS SOTRSAL
--Para los ajustes con cantidad en cero (0)
,SUM(tmVrUnitario) AS SCOSAJU,SUM(tmOtroImpto) AS SDVEAJU
,SUM(tmRec_Costo*tmEntradas) AS SRCOSENT,SUM(tmRec_Costo*tmSalidas) AS SRCOSSAL
,SUM(tmMgenCont*tmEntradas) AS SMGENENT,SUM(tmMgenCont*tmSalidas) AS SMGENSAL
,SUM(tmVrImvCosto) AS SIMVCOS,SUM(tmVrImpCon) AS SVICO
,SUM(CASE WHEN tmCantObseq>0 THEN tmIvaObseq ELSE 0 END) AS SIVAOBSQ
,SUM(tmCantObseq*tmVrUnitario) AS SBASCOSOBSQ,SUM(tmCantObseq*tmVrPrecio) AS SBASEOBSQ
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto
 WHERE tmNumero=@pmtmNumero
 GROUP BY IdSubgrupo,tmIdProducto,DescripProd,TipoRef,Combo,tmEsProdBase,IdProv,ExcluidoImp,tmCdOperario
 ORDER BY IdSubgrupo,tmIdProducto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryClaseTarImp]
 AS 
  SELECT IdClase,NomClase
  FROM ClaseTar
  WHERE Inactivo=0 AND IdClase IN ('DEP','GLO','ICA','IVA','MUN','NAC','RET','RIV','SOB','SOL','MGM','PRE','TSN','TSD','TSM','SUB','MAR','COS','IVI','IGP','MGC','MGN','CSE')
  ORDER BY NomClase
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryKardexSubOpe] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT IdSubgrupo,K.IdProducto AS tmIdProducto,DescripProd,TipoRef,Combo,EsProdBase AS tmEsProdBase,IdProv,ExcluidoImp,CdOperario AS tmCdOperario
,COUNT(Item) AS SCANT,SUM(Entradas) AS SENT,SUM(Salidas) AS SSAL
,SUM(CASE TipDoc WHEN 'ENT' THEN VrCostoEnt-(NumInicial*Entradas) WHEN 'COM' THEN VrCostoEnt-(NumInicial*Entradas)
 ELSE VrCostoEnt END) AS SCOSENT
,SUM(CASE TipDoc WHEN 'DVE' THEN VrCostoSal-(NumInicial*Salidas) WHEN 'DEI' THEN VrCostoSal-(NumInicial*Salidas)
 ELSE VrCostoSal END) AS SCOSSAL
,SUM(VrPrecio*Entradas) AS SVALENT,SUM(VrPrecio*Salidas) AS SVALSAL
,SUM(VrBruto*Entradas) AS SBRUENT,SUM(VrBruto*Salidas) AS SBRUSAL
,SUM(Entradas*ImpGlobal) AS SGLOENT,SUM(Salidas*ImpGlobal) AS SGLOSAL
,SUM(Sobretasa*Entradas) AS SSOBENT,SUM(Sobretasa*Salidas) AS SSOBSAL
,SUM(TasaNac*Entradas) AS SNACENT,SUM(TasaNac*Salidas) AS SNACSAL
,SUM(TasaDep*Entradas) AS SDEPENT,SUM(TasaDep*Salidas) AS SDEPSAL
,SUM(TasaMun*Entradas) AS SMUNENT,SUM(TasaMun*Salidas) AS SMUNSAL
,SUM(Soldicom*Entradas) AS SSOLENT,SUM(Soldicom*Salidas) AS SSOLSAL
,SUM(CASE WHEN  Salidas>0 THEN VrIvaSal ELSE VrIvaEnt END) AS SIVA
,SUM(CASE WHEN  Salidas>0 THEN VrDctoSal ELSE VrDctoEnt END) AS SDCT
,SUM(CASE WHEN  Salidas>0 THEN VrReteSal ELSE VrReteEnt END) AS SRET
,SUM(CASE WHEN  Salidas>0 THEN VrIcaSal ELSE VrIcaEnt END) AS SICA
,SUM(OtroImpto*Entradas) AS SOTRENT,SUM(OtroImpto*Salidas) AS SOTRSAL
,SUM(Rec_Costo*Entradas) AS SRCOSENT,SUM(Rec_Costo*Salidas) AS SRCOSSAL
,SUM(MgenCont*Entradas) AS SMGENENT,SUM(MgenCont*Salidas) AS SMGENSAL,SUM(VrImpCon) AS SVICO
,SUM(CASE WHEN CantObseq>0 THEN VrIvaObseq ELSE 0 END) AS SIVAOBSQ
,SUM(CantObseq*VrUnitario) AS SBASCOSOBSQ,SUM(CantObseq*VrPrecio) AS SBASEOBSQ
--Para los ajustes con cantidad en cero (0)
,SUM(VrUnitario) AS SCOSAJU,SUM(OtroImpto) AS SDVEAJU,SUM(VrImvCosto) AS SIMVCOS
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
 GROUP BY  IdSubgrupo,K.IdProducto,DescripProd,TipoRef,Combo,EsProdBase,IdProv,ExcluidoImp,CdOperario
 ORDER BY IdSubgrupo,K.IdProducto
GO