Hola buenas tardes.
A modo granito de arena adjunto recopilatorio de queries que pueden serles de ayuda.
No adjunto el autor de cada uno porque sería un trabajo minucioso. Si alguno de Ustedes quiere aportar algún otro sería estupendo.
Gracias a todos! Un saludo.
CLIENTES, TOTAL FACTURADO,IVA, GANACIA Y TOTAL FACTURADO.
SELECT T0.CardCode, T0.CardName, Count(T0.DocNum)'NºDoctos', SUM(T0.DocTotal)'Total Facturas', SUM(T0.VatSum)'Total Impuesto', SUM(T0.DocTotal-T0.VatSum)'Total Base o neto', SUM(T0.GrosProfit)'Ganancia', SUM(T0.DocTotal-T0.PaidToDate)'Pendiente'
FROM OINV T0
WHERE T0.DocDate BETWEEN '[%0]' AND '[%1]'
GROUP BY T0.CardCode, T0.CardName
UNION
SELECT T0.CardCode, T0.CardName, Count(T0.DocNum)'NºDoctos', -SUM(T0.DocTotal)'Total Facturas', -SUM(T0.VatSum)'Total Impuesto', -SUM(T0.DocTotal-T0.VatSum)'Total Base o neto', -SUM(T0.GrosProfit)'Ganancia', -SUM(T0.DocTotal-T0.PaidToDate)'Pendiente'
FROM ORIN T0
WHERE T0.DocDate BETWEEN '[%0]' AND '[%1]'
GROUP BY T0.CardCode, T0.CardName
PAGARÉS EN EL CAJÓN:
SELECT T0.BoeStatus As "Status de efecto", T0.CardCode As "Código IC", T0.CardName As "Nombre de interlocutor comercial", T0.BoeNum As "Número de efecto", T0.BoeType As "Clase de efecto", T0.DueDate As "Vencimiento de efecto", T0.BoeSumSC As "Importe de efecto (MS)", T0.DpsBankCod As "Proyecto", T0.BPBankCod As "IC Código de banco", T0.BPBankNam As "IC Nombre de banco", T0.BPBankAct As "Cuenta bancaria IC", T0.BPBankBrnc As "IC Sucursal bancaria", T0.BPBankCtr As "IC País del banco", T0.ControlKey As "ID interna control bancos IC", T0.PayMethCod As "Total del documento sin IVA", T0.PymMethNam As "Saldo Haber", T0.AgentCode As "Código de agente" FROM OBOE T0 WHERE T0.BoeStatus = N'G' ORDER BY T0.BoeStatus,T0.CardCode
LISTADO DE INCIDENCIAS
SELECT Num=T0.[ClgCode],Año=year(T0.[Recontact]),Mes=month(T0.[Recontact]) ,Fecha=T0.[Recontact], Hora=T0.[BeginTime],
T3.[Name] as 'Tipo',
T2.[Name] as 'Asunto',
T1.FirstName+' '+T1.LastName as 'Responsable',
T0.[Details] as 'Comentarios', T0.[Notes] as 'Contenido', T0.[U_SEIAcInm] as 'Acción Inmediata', T0.[U_SEI_feAc] as 'Fecha Acción Inmediata', T0.[U_SEI_Resp] as 'Responsable Acción Inmediata', T0.[U_SEI_Desc] as 'Descripción Cierre', T0.[U_SEI_RespC] as 'Responsable Cierre',T0.[Closed] as 'Cerrado', T0.[CloseDate] 'Fecha Cierre Sistema' , U_SEI_fecc as' Fecha Cierre', T0.[U_SEICOST] as 'Cost' FROM OCLG T0 LEFT JOIN OHEM T1 ON T0.AttendEmpl = T1.empID
LEFT JOIN [dbo].[OCLS] T2 ON T2.[Code] = T0.[CntctSbjct]
LEFT JOIN [dbo].[OCLT] T3 ON T3.[Code] = T2.[Type]
INCIDENCIAS SIN CERRAR:
SELECT T0.[ClgCode], T0.[CardCode], T0.[Notes], T0.[Action], T0.[CntctType]
FROM OCLG T0
WHERE ISNULL(cast(T0.[Notes] as varchar(10)), '') <> '' and closed='N'
QUE TRANSFERENCIA HEMOS RECIBIDO DEL CLIENTE Y EN QUE DIA:
SELECT DISTINCT
T1.DocDate,
T0.CardCode,
T0.CardName,
T2.DocNum AS 'Inc Pay Nbr',
T1.DocTotal AS 'Appl Amt'
FROM OCRD T0
LEFT OUTER JOIN ORCT T1
ON T0.CardCode = T1.CardCode
INNER JOIN ORCT T2
ON T1.DocNum = T2.DocNum
AND T2.TrsfrAcct IS NOT NULL
WHERE
T1.DocDate >= '[%0]'
AND T1.DocDate <= '[%1]'
ORDER BY
T1.DocDate DESC,
T0.CardCode,
T0.CardName,
T2.DocNum,
T1.DocTotal
FOR BROWSE
¿Cuánto DEBEN LOS CLIENTES Y EL TOTAL DE FACTURAS?:
SELECT T0.[CardCode], T0.[CardName], T0.[Balance], T0.[OrdersBal], T0.[DNotesBal], T0.[ChecksBal],
'Pedidos'=(SELECT SUM(T10.DocTotal) FROM ORDR T10 WHERE T10.CardCode = T0.CardCode
AND T10.DocDate BETWEEN '[%0]' AND '[%1]'),
/* Trae la suma de total de documento de pedidos en rango de fechas*/
'Facturas'=(SELECT SUM(T10.DocTotal) FROM OINV T10 WHERE T10.CardCode = T0.CardCode
AND T10.DocDate BETWEEN '[%0]' AND '[%1]' AND T10.DocSubType NOT LIKE 'DN'),
/*Trae suma facturas en el rango de fechas*/
'NC'=(SELECT SUM(T10.DocTotal) FROM ORPC T10 WHERE T10.CardCode = T0.CardCode AND T10.DocDate
BETWEEN '[%0]' AND '[%1]'),
'ND'=(SELECT SUM(T10.DocTotal) FROM OINV T10 WHERE T10.CardCode = T0.CardCode AND T10.DocDate
BETWEEN '[%0]' AND '[%1]' AND T10.DocSubType LIKE 'DN')
FROM OCRD T0
WHERE T0.[CardType] LIKE 'C'
TOP 10 CLIENTES:
SELECT TOP 10 T0.CardCode, MAX(T0.Cardname) as Customer, SUM(T0.doctotal) as "Amount(LC)"
FROM dbo.OINV T0
WHERE t0.docdate BETWEEN [%0] AND [%1]
GROUP BY T0.CardCode
Order by SUM(T0.doctotal) DESC
ALARMA STOCK AL MINIMO:
SELECT T0.ItemCode, T0.ItemName, T0.OnHand, T0.SuppCatNum, T0.MinLevel
FROM OITM T0
WHERE MinLevel ! = 0 and OnHand < MinLevel
¿QUE HAY EN EL ALMACEN?
SELECT T0.[WhsCode], T2.[WhsName], T0.[ItemCode], T1.[ItemName],
ROUND(SUM(CASE M.InQty
WHEN 0 THEN -1*M.CalcPrice*M.OutQty
ELSE M.CalcPrice*M.InQty
END),2) AS [Valor],
(ISNULL(SUM(M.InQty)-SUM(M.OutQty),0)) AS [Stock]
FROM OINM M
INNER JOIN OITW T0 ON M.ItemCode=T0.ItemCode AND M.Warehouse=T0.WhsCode
INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
INNER JOIN OWHS T2 ON T0.WhsCode = T2.WhsCode
WHERE M.DocDate <= '[%1]'
GROUP BY T0.WhsCode, T2.WhsName, T0.ItemCode, T1.ItemName
ORDER BY T0.[WhsCode], T0.[ItemCode]
FACTURAS DE PROVEEDORES PAGADAS POR MES Y DIA EN TABLA CALENDARIO:
SET LANGUAGE Spanish
DECLARE @TOP INT, @F_INI DATETIME, @F_FIN DATETIME, @COND_1 CHAR(1), @COND_2 CHAR(1)
SET @TOP=(SELECT TOP 1 A.TransId FROM [dbo].[JDT1] A WHERE A.RefDate>='[%0]' AND A.RefDate<='[%1]')
SET @F_INI='[%0]'
SET @F_FIN='[%1]'
SET @COND_1='N'
SET @COND_2='S'
SELECT DISTINCT B.DocDate AS DocDate, CONVERT(NVARCHAR, B.DocDate, 103) AS Fecha
INTO #FECHAS
FROM OVPM B
WHERE B.DocDate BETWEEN @F_INI AND @F_FIN
ORDER BY 1
DECLARE @pvt_table NVARCHAR(MAX)
SELECT @pvt_table = COALESCE(@pvt_table + ',[' + C.Fecha + ']', '[' + C.Fecha + ']')
FROM #FECHAS C
ORDER BY C.DocDate
DECLARE @Pvt NVARCHAR(MAX)
SET @Pvt =
N'
SELECT *
FROM (
SELECT DISTINCT T0.CardCode AS CardCode, T0.CardName AS CardName, CONVERT(NVARCHAR, T0.DocDate, 103) AS Fecha, SUM(T0.DocTotal) AS Pagos
FROM OVPM T0
WHERE T0.DocDate BETWEEN (@INI) AND (@FIN) AND T0.Canceled=(@C_1) AND T0.DocType=(@C_2)
GROUP BY T0.CardCode, T0.CardName, T0.DocDate ) AS A
PIVOT (
SUM(Pagos)
FOR Fecha IN ('+ @pvt_table +')
) AS Pvt
ORDER BY 1,3
'
EXEC sp_executesql @pvt, N'@pvt_table NVARCHAR(MAX), @INI DATETIME, @FIN DATETIME, @C_1 CHAR(1), @C_2 CHAR(1)',@pvt_table, @F_INI, @F_FIN, @COND_1, @COND_2
DROP TABLE #FECHAS
VACACIONES (RESUMEN EMPLEADOS)
SELECT T1.[empID], T1.[firstName], T1.[lastName], T0.[u_seiany], [Dias Pendientes]=SUM(T0.[U_Dias]) FROM HEM1 T0 INNER JOIN OHEM T1 ON T0.empID = T1.empID left JOIN [dbo].[@SEITIPVAC] T2 ON T0.U_SEITIPO = T2.Code
group by T1.[empID], T1.[firstName], T1.[lastName], T0.[u_seiany]
RELACION MERMAS LOTE:
SELECT T3.Docentry, T0.DocLine, T1.*,
--T0.ItemCode,T0.CardName,T0.ItemName ,T0.DocDate ,T0.DocType ,T0.DocNum as [Nº ALBARAN], T0.LocCode as [Cod. Almacén] ,t4.DistNumber as [Nº Lote], T1.Quantity, SELECT T3.Docentry, T0.DocLine, T1.*,
--T0.ItemCode,T0.CardName,T0.ItemName ,T0.DocDate ,T0.DocType ,T0.DocNum as [Nº ALBARAN], T0.LocCode as [Cod. Almacén] ,t4.DistNumber as [Nº Lote], T1.Quantity, T3.U_SEIECOE, T3.U_SEIECOC, T3.U_SEICALE, T3.U_SEITEOK,
Tipo= CASE T0.[DocType]
WHEN '59 ' Then 'Entrada'
WHEN '15 ' Then 'Salida'
Else 'Otros'
End,T4. U_SEIM1 ,T4.U_SEIM2,U_SEIMerma
FROM OITL T0
INNER JOIN [ITL1] T1 ON T1.[LogEntry] = T0.[LogEntry]
INNER JOIN OBTN T4 on T1.MdAbsEntry=T4.AbsEntry
INNER JOIN DLN1 T3 ON T3.[DocEntry] = T0.Docentry and T0.DocLine=T3.Linenum
WHERE isnull(U_SEIM1,0)+isnull(U_SEIM2,0)<>0 and T0.[DocType]='15'
and YEAR(T3.Docdate)>'2013'
LISTA DE FACTURAS QUE SE HAN PAGADO, COMO Y CUANDO:
SELECT distinct
T0.DocNum 'Factura',
T0.DocStatus 'Estatus',
T0.DocDate 'Fecha',
T0.CardCode 'Cliente',
T0.CardName 'Nombre',
T0.DocCur 'Moneda',
T0.DocTotal 'Total Factura',
T0.PaidToDate 'Total Pagado',
T1.SlpName 'Vendedor',
T2.PymntGroup 'Cond. Pago',
T0.DocTotalSy 'Total USD',
T3.DocNum 'No.Doc. Pago',
T3.CashSum 'Efectivo',
T3.CreditSum 'T.Crédito',
T3.CheckSum 'Cheque',
T3.TrsfrSum 'Transf',
T3.DocTotal, 'Total Pago',
T3.DocDate 'Fecha Pago'
FROM OINV T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
INNER JOIN OCTG T2 ON T0.GroupNum = T2.GroupNum
INNER JOIN ORCT T3 ON T0.[ReceiptNum] = T3.DocNum
INNER JOIN NNM1 T4 ON T3.Series = T4.Series
INNER JOIN RCT2 T5 ON T3.DocEntry = T5.DocNum
WHERE T0.DocDate <= '[%1]'
AND T3.Canceled = 'N'
ORDER BY T0.DocNum
FACTURAS PROVEEDORES PROXIMAS A VENDER PARA EL CEO DE LA EMPRESA:
SELECT T0.DocNum AS 'Factura', T0.DocDueDate AS 'Fecha de vencimiento', T0.CardCode AS 'Código cliente',
T0.CardName AS 'Nombre'
FROM OPCH T0
WHERE DATEDIFF (DD, T0.DocDueDate, GETDATE( )) BETWEEN -7 AND 365
AND T0.DocStatus = 'O'
ORDER BY T0.DocDueDate
FOR BROWSE
PARA SABER QUE PRODUCTOS ESTAN EN NEGATIVO EN LOS ALMACENES:
SELECT T0.ItemCode AS 'Código', T1.ItemName AS 'Descripción', T0.WhsCode AS 'Almacen',
T0.OnHand AS 'Cantidad', T1.OnHand AS 'Disponible total'
FROM OITW T0
LEFT OUTER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
WHERE T0.OnHand < 0
FOR BROWSE
PARA SABER DE QUE ARTICULOS TENGO MINIMOS Y HAY QUE COMPRAR:
SELECT
T0.CardCode AS 'Pref Vend',
T2.CardName AS 'Pref Vend Name',
T0.ItemCode AS 'Item Code',
T0.ItemName AS 'Item Description',
T1.ItmsGrpNam AS 'Item Group',
T0.LeadTime AS 'Lead',
CASE
WHEN T0.MinLevel - (T0.OnHand + T0.OnOrder - T0.IsCommited) > T0.MinOrdrQty
THEN T0.MinLevel - (T0.OnHand + T0.OnOrder - T0.IsCommited)
WHEN T0.MinLevel - (T0.OnHand + T0.OnOrder - T0.IsCommited) < T0.MinOrdrQty
THEN T0.MinOrdrQty
END AS 'To Purch',
T0.MinOrdrQty AS 'Min Ord',
T0.DfltWH AS 'Whs',
T0.MinLevel AS 'Min Inv',
T0.PrchseItem AS 'Buy',
T0.OnHand AS 'On Hand',
T0.OnOrder AS 'On Order',
T0.IsCommited AS 'Committed'
FROM OITM T0
LEFT OUTER JOIN OITB T1
ON T0.ItmsGrpCod = T1.ItmsGrpCod
LEFT OUTER JOIN OCRD T2
ON T0.CardCode = T2.CardCode
WHERE
T0.InvntItem = 'Y'
AND T0.PrchseItem = 'Y'
AND T0.MinLevel - (T0.OnHand + T0.OnOrder - T0.IsCommited) > 0
AND T0.ItmsGrpCod <> 108
ORDER BY
T2.CardCode,
T0.ItemCode
FOR BROWSE
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.