INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('CGN','CONTABILIDAD GENERAL COMO NIIF','BOOLEAN','0',5,'MAIN')
GO
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMCREV','MAIDOC','CON',8,'Revelaciones','FRMNIIFREV','NSNNNNNNNNNNN',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMCPOL','MAIDOC','CON',9,'Políticas Contables','FRMNIIFPOL','NSNNNNNNNNNNN',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMCREVTRA','TRADOC','CON',6,'Revelaciones','FRMNIIFREV','NSNSNNNNNNNNN',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMCPOLTRA','TRADOC','CON',7,'Políticas Contables','FRMNIIFPOL','NSNSNNNNNNNNN',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMCCXCMAI','MAIDOC','CON',10,'Ajustes Cuentas Por Cobrar','FRMNIIFCXC','NSNNNNNNNNNNN',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMCCXPMAI','MAIDOC','CON',11,'Ajustes Cuentas Por Pagar','FRMNIIFCXC','NSNNNNNNNNNNN',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMCCXTRA','TRADOC','CON',8,'Ajustes Cuentas Por Cobrar','FRMNIIFCXC','NSNSNNNNNNNNN',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMCCPTRA','TRADOC','CON',9,'Ajustes Cuentas Por Pagar','FRMNIIFCXC','NSNSNNNNNNNNN',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMAIC','MAIDOC','CON',12,'Comprobante de Ajustes a Inventarios','FRMNIIFINV','NSNNNNNNNNNNN',0,'')
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNiifRevelacLtc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNiifRevelacLtc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNiifPoliticasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNiifPoliticasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedidoDetFac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOpedidoDetFac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudInventarioOsm]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudInventarioOsm]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOpedidoDetFac] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
 SELECT O.TipDoc,O.Pedido,O.IdCia AS CdCia,Compania,O.Fecha,O.FechaVence,O.IdConcepto AS CdConcepto,Concepto,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,CodAgencia,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,O.TarifaCom
,O.VrFletes,O.VrOtros,O.VrCargos,O.VrOtrDcto,O.VrNeto,O.DirEnvio,O.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LugarEnvio,O.DiasEntraga,O.NitContac AS NitContacto,O.NomContac AS NomContacto,O.TelContac AS TelContacto,O.emlContac AS EmailContacto,O.CargoContac
,O.IdForma AS CdForma,FormaPago,O.DetallePago,O.MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,O.NitEmpTrans,O.EmpTrans,O.pVehiculo AS PlacaVeh,O.CdConductor,TC.RazonSocial AS Conductor,O.CdRuta,Ruta,RefPedido,O.TipFac,O.Factura,O.IdCiaFac,O.FechaFact,O.TipRem,O.Remision,O.IdCiaRem,O.FechaRem
,O.NumCotizac,CdCiaCotizac,O.NumAutoriza,O.Modalidad,O.Vigencia,O.NumAprob,O.IdCiaApr,FecAprob,CdUsuAprob,DetalleAprob,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.ZonaFrontera,TipoTrans,O.TipoOrden,TipoModifica,O.TimeSys AS FechaCrea,O.IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
--detalles
,D.Item,D.IdProducto AS CdProducto,DescripProd,PM.TipoRef,D.IdBodega AS CdBodega,Bodega,D.CdTanque,D.Salidas AS CantOrden,D.IdUnd AS CdUnd,UM.Unidad,D.VrUnitario AS CostoUnd,D.VrPrecio AS VlrUnitario,D.TarifaIva,D.VrIvaSal,D.TarifaDct,D.VrDctoSal
,D.TarifaRet,D.VrReteSal,D.TarifaIca,D.VrIcaSal,D.ListaPrec,D.VrBruto,D.VrBase,D.Unidades,D.Descripcion AS DetDescripcion,D.Referencia,D.Referencia2,D.Servcios,D.NoVentas,D.EsCombo,D.EsProdBase
,D.TipOrd AS DetTipoFac,D.NumOrden AS DetNumFac,D.IdCiaOrd AS DetIdCiaFac,D.galsneto AS CantTotFact,D.CantObseq,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,SG.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca
--datos de item facturado
,DF.TipDoc AS FacTipo,DF.Documento AS FacNumero,DF.IdCia AS FacIdCia,DF.FecFact,DF.Salidas AS FacCantidad,DF.VrUnitario AS FacCostoUnd,DF.VrPrecio AS FacVrUnitario,DF.TarifaIva AS FacTarifIva,DF.VrIvaSal AS FacVrIva
,DF.TarifaDct AS FacTarifDcto,DF.VrDctoSal AS FacVrDcto,DF.TarifaRet AS FacTarifRet,DF.TarifaIca AS FacTarifIca,DF.ListaPrec AS FacLista,DF.VrBruto AS FacVrBruto,DF.VrBase AS FacVrBase,D.Unidades AS FacUnidades,DF.Servcios AS FacServicio
,DF.EsCombo AS FacEsCombo,DF.EsProdBase AS FacProdBase,DF.galsbruto AS FacCantTotPed,DF.VrRetencion AS FacRetencion,DF.VrReteICA AS FacReteICA,DF.VrReteIVA AS FacReteIva,DF.VrFletes AS FacFletes,DF.VrOtros AS FacVrOtros,DF.VrCargos AS FacVrCargos,DF.VrOtrDcto AS FacOtrDcto
,DF.VrNeto AS FacVrNeto,DF.BaseRet,DF.VrReteCREE AS FacReteCREE
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono
,T.Fax AS TercFax,T.e_mail AS TercEmail,T.SitioWeb AS TercSitioWeb
,NitRepLeg,NomRepLeg,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,ExcIva,LiqFletes,Autoret,VrCupo,VrSaldo,CdDiaEnt,DiaEntrega,CLI.IdEstrato AS CdEstrato,Estrato
 FROM Trn_Opedido AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Conceptos AS C ON O.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON O.IdVend=VN.IdTercero
 INNER JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia INNER JOIN Formaspago AS FP ON O.IdForma=FP.IdForma
 INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN Trn_Kardex AS D ON O.TipDoc=D.TipDoc AND O.Pedido=D.Documento AND O.IdCia=D.IdCia
 INNER JOIN ProdMcias AS PM ON D.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON D.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS SG ON PM.IdSubgrupo=SG.IdSubgrupo INNER JOIN Grupos AS G ON SG.IdGrupo=G.IdGrupo
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 LEFT JOIN Localidades AS LE ON O.IdLocEnv=LE.IdLocal LEFT JOIN Terceros AS TC ON O.CdConductor=TC.IdTercero
 LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta LEFT JOIN TiposEnt AS EG ON CLI.CdDiaEnt=EG.IdEnt LEFT JOIN Estratos AS ETT ON CLI.IdEstrato=ETT.IdEstrato
 --facturas>
 LEFT JOIN (SELECT D.TipDoc,D.Documento,D.IdCia,D.Item,D.Fecha,D.IdProducto,D.IdBodega,Salidas,VrUnitario,VrPrecio,D.TarifaIva,D.VrIvaSal,D.TarifaDct,D.VrDctoSal,D.TarifaRet,VrReteSal
,D.TarifaIca,D.VrIcaSal,ListaPrec,D.VrBruto,VrBase,D.Unidades,D.TipOrd,D.NumOrden,D.IdCiaOrd,D.Remision,D.IdCiaRem,ItemCombo,Servcios,EsCombo,EsProdBase,galsbruto
,F.Fecha AS FecFact,F.IdConcepto AS CdConcFact,F.IdCliente,F.IdAgencia,F.VrRetencion,F.VrReteICA,F.VrReteIVA,VrFletes,F.VrOtros,VrCargos,VrOtrDcto,VrNeto,F.BaseRet,F.TarifaRet AS TarifRetFact,F.TarifaIca AS TarifIcaFact,F.TarifaRiv AS TarifRivFact
,F.TarifaRtc,F.VrReteCREE,F.Observacion FROM Trn_Kardex AS D INNER JOIN Trn_Facturas AS F ON D.TipDoc=F.TipDoc AND D.Documento=F.Factura AND D.IdCia=F.IdCia 
 WHERE D.TipDoc IN ('FCR','FC1','FC2','FC3','FC4','FC5') AND F.Anulado=0
 UNION ALL
 SELECT D.TipDoc,D.Documento,D.IdCia,D.Item,D.Fecha,D.IdProducto,D.IdBodega,Salidas,VrUnitario,VrPrecio,D.TarifaIva,D.VrIvaSal,D.TarifaDct,D.VrDctoSal,D.TarifaRet,VrReteSal
,D.TarifaIca,D.VrIcaSal,ListaPrec,D.VrBruto,VrBase,D.Unidades,D.TipOrd,D.NumOrden,D.IdCiaOrd,D.Remision,D.IdCiaRem,ItemCombo,Servcios,EsCombo,EsProdBase,galsbruto
,F.Fecha AS FecFact,F.IdConcepto AS CdConcFact,F.NitCliente,F.CdAgencia,F.VrRetencion,F.VrReteICA,F.VrReteIVA,VrFletes,0,F.VrCargos,F.VrOtrDcto,F.VrNeto,F.BaseRet,F.TarifaRet,F.TarifaIca,F.TarifaRiv,F.TarifaRtc,F.VrReteCREE,F.Observacion
 FROM Trn_Kardex AS D INNER JOIN Trn_Factcon AS F ON D.TipDoc=F.TipDoc AND D.Documento=F.Factura AND D.IdCia=F.IdCia 
 WHERE D.TipDoc IN ('FCO','FO1','FO2','FO3','FO4','FO5') AND F.Anulado=0 AND D.TipOrd='PED' AND D.NumOrden>0) AS DF ON D.TipDoc=DF.TipOrd AND D.Documento=DF.NumOrden AND D.IdCia=DF.IdCiaOrd AND D.Item=DF.ItemCombo AND D.IdProducto=DF.IdProducto
--<Facturas
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND (@pmIdCia IS NULL OR O.IdCia=@pmIdCia) AND (@pmIdCliente IS NULL OR O.IdCliente=@pmIdCliente)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNiifPoliticasLta]
 AS 
 SELECT [Id],P.IdPolitica,P.Descripcion,ClasePol,Objetivo,Alcance,Reconocimiento,Med_Inicial,Med_Final
,PdoVigencia,Vigencia,Notas,Anexo,P.Inactivo,P.FechaCrea,P.IdUsuario AS CdUsuario,Usuario
,CdCuenta,NomCuenta
 FROM Trn_NiifPoliticas AS P INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 LEFT JOIN Trn_NiifPolCuentas AS C ON P.IdPolitica=C.IdPolitica
 LEFT JOIN (SELECT IdCuenta,NomCuenta,Movimiento FROM Puc UNION SELECT IdCuenta,NomCuenta,Movimiento FROM PucNiif) AS PC ON C.CdCuenta=PC.IdCuenta
 WHERE P.Inactivo=0
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudInventarioOsm] @pmTipOsm VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT M.TipDoc,TipoDoc,M.NumInvent,M.IdCia AS CdCia,Compania,M.Fecha,M.FecDespacho,M.IdTipoServ AS CdTipoServ,TipoServicio,M.IdCliente,T.RazonSocial AS NomCliente,M.IdAgencia AS CdAgencia,Agencia
,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest
,M.IdOrigen,LO.Localidad AS CiuOrigen,DPO.Departamento AS DptoOrigen,M.IdDestino,LD.Localidad AS CiuDestino,DPD.Departamento AS DptoDestino,M.pVehiculo,M.CdConductor,NC.RazonSocial AS Conductor
,M.nRemolque,M.TipoAfiVehic,M.NitEmpresa,M.NomEmpresa,CajasEmp,CajasClie,M.VrDeclarado,M.VrServicio,M.NumCotizac,M.NumPlanilla,NumActa,M.TipOsm,M.NumOrden,M.IdCiaOsm,M.NomContacto,M.TelsContacto
,M.emlContacto,M.Responsable,M.FormaPago,M.Remitente AS ContactoCargue,M.Destinatario AS ContactoDestino,M.Anulado,M.FecDev,M.Observacion AS Observ,M.IdEstado AS CdEstado,ED.Estado
,M.OrigenAdd,M.TimeSys AS FechaCrea,M.FecUpdate,M.IdCiaCrea AS CdCiaCrea,M.IdUsuario AS CdUsuario,U.Usuario
,D.Item,D.IdArticulo AS CdArticulo,D.Descripcion,D.Cantidad,D.CantSalida,D.IdEstado AS CdEstadoArt,EA.Estado AS EstadoArt,D.Observacion AS DetObserv,Articulo,MA.IdSeccion AS CdSeccion,Seccion,EA.NColor AS NColorEst
,D.TipoIngreso,D.FechaReg,D.CdUsuario AS DetCdUsuario,UD.Usuario AS DetUsuario
FROM Trn_MudInventario AS M INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia INNER JOIN Terceros AS T ON M.IdCliente=T.IdTercero
INNER JOIN MudServicios AS TS ON M.IdTipoServ=TS.IdTipoServ INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado
INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON M.TipDoc=TD.IdDoc
INNER JOIN Localidades AS LO ON M.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
INNER JOIN Localidades AS LD ON M.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
INNER JOIN Trn_MudInventDet AS D ON M.TipDoc=D.TipDoc AND M.NumInvent=D.NumInvent AND M.IdCia=D.IdCia
INNER JOIN MudArticulos AS MA ON D.IdArticulo=MA.IdArticulo INNER JOIN MudSecciones AS SC ON MA.IdSeccion=SC.IdSeccion
INNER JOIN MudEstados AS EA ON D.IdEstado=EA.IdEstado
LEFT JOIN Terceros AS NC ON M.CdConductor=NC.IdTercero LEFT JOIN Agencias AS A ON M.IdAgencia=A.IdAgencia 
LEFT JOIN adm_Usuarios AS UD ON D.CdUsuario=UD.IdUsuario 
WHERE M.TipDoc='MUD' AND M.TipOsm=@pmTipOsm AND M.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND M.IdCiaOsm=@pmIdCia
AND M.Anulado=0
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNiifRevelacLtc] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmTipCom VARCHAR(3),@pmIdCia CHAR(2)
 AS 
 SELECT R.TipCom,TipoCom,R.Comprobante AS NumComp,R.IdCia AS CdCia,Compania,C.Fecha,C.IdTercero AS NitTercero,T.RazonSocial,C.VrTotal,C.IdCta AS CdCta,NumeroCta
,CTA.IdBanco AS CdBanco,Banco,EnEfectivo,C.NumCheque AS ComNumCheque,FecCheque,Anulado,NumDev,FecDev,pVehiculo,C.VehPropio,CedCondtor,CD.RazonSocial AS NomConductor
,C.CodConce,CN.Concepto,Beneficiario,C.Integrado AS EsIntegracion,C.EsEgreso AS ComEsEgreso,C.Observacion AS Observ,C.IdEstado AS CdEstado,NiifTipo,NiifNumero,FisTipo,FisNumero
,R.Item,R.TipoRev,R.Titulo,R.Comentario,R.CdCuenta,NomCuenta,R.FechaCrea,R.IdUsuario AS CdUsuario,Usuario
 FROM Trn_NiifRevelac AS R INNER JOIN Trn_Comprobantes AS C ON R.TipCom=C.TipCom AND R.Comprobante=C.Comprobante AND R.IdCia=C.IdCia
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia  INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 INNER JOIN TiposCom AS TC ON R.TipCom=TC.IdCom INNER JOIN Terceros AS T ON C.IdTercero=T.IdTercero
 LEFT JOIN Conceptos AS CN ON C.CodConce=CN.IdConcepto LEFT JOIN Terceros AS CD ON C.CedCondtor=CD.IdTercero 
 LEFT JOIN CtasCorrientes AS CTA ON C.IdCta=CTA.IdCta LEFT JOIN Bancos AS BCT ON CTA.IdBanco=BCT.IdBanco
 LEFT JOIN Puc AS P ON R.CdCuenta=P.IdCuenta
 WHERE C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND R.TipCom LIKE ISNULL(@pmTipCom,'%')
 AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO