Inventario - Análisis de Artículos - PIVOTE

Hola!, buen día 😀... les dejo esta consulta con dos finalidades:

1. Consultar en SAP la ultima entrada (fecha, total cantidad 1 año y cantidad ultima salida) y la ultima salida  (fecha, total cantidad 1 año y cantidad ultima salida) de los artículos.
2. Conozcas a través de un ejercicio como funciona el Pivote en SQL Server, en este caso utilizo a los almacenes que están en una misma columna para que se abra cada almacén en una solo columna una por cada almacén, de esta forma evito repeticiones en el Item de mi consulta.

Espero les ayude bastante, hasta la próxima!.

select ItemCode, ItemName, OnHand, ItmsGrpNam,[Fecha de producción],[Ultima Entrada], [Cantidad Ultima Salida], [Total Salidas]

, [Cantidad Ultima Entrada], [Total Entradas], [Ultima Salida], [ALM1], [ALM2], [ALM3],[ALM4] 

from (

select a.ItemCode, a.ItemName, a.OnHand, b.ItmsGrpNam

, a.createDate as [Fecha de producción]

, (select MAX(createDate) from OINM where ItemCode = a.ItemCode and  TransType in (20,69,10000071)) as [Ultima Entrada]

, (select SUM(InQty) from OINM where ItemCode = a.ItemCode and  TransType in (20,69,10000071) and createDate = ( select MAX(createDate) from OINM where ItemCode = a.ItemCode and  TransType in (20,69,10000071)  ) ) as [Cantidad Ultima Entrada]

, (select SUM(InQty) from OINM where ItemCode = a.ItemCode and  TransType in (20,69,10000071) and createDate  between '20180101' and '20181231'  ) as [Total Entradas 2018]

, (select MAX(createDate) from OINM where ItemCode = a.ItemCode and  TransType in (60)) as [Ultima Salida]

, (select SUM(OutQty) from OINM where ItemCode = a.ItemCode and  TransType in (60) and createDate = ( select MAX(createDate) from OINM where ItemCode = a.ItemCode and  TransType in (60)  ) ) as [Cantidad Ultima Salida]

, (select SUM(OutQty) from OINM where ItemCode = a.ItemCode and  TransType in (60) and createDate between '20180101' and '20181231' ) as [Total Salidas 2018]

, c.OnHand [Stock en WHSE], a.BuyUnitMsr,a.InvntryUom, c.WhsCode

, CASE  

WHEN a.validFor = 'Y' and a.frozenFor = 'N' THEN 'Activo'

WHEN a.validFor = 'N' and a.frozenFor = 'Y' THEN 'Inactivo'

EnD as Activo

  from OITM a

inner join OITB b on a.ItmsGrpCod = b.ItmsGrpCod 

left join OITW c on a.ItemCode = c.ItemCode

and c.WhsCode in ('ALM1', 'ALM2', 'ALM3','ALM4') 

) consulta

PIVOT

(

AVG (consulta.[Stock en WHSE])

FOR WhsCode in ([ALM1], [ALM2], [ALM3],[ALM4])

)AS pvt

Ventas - Ventas por cliente y articulo

Hola!, paso de forma muy rapída para compartirles esta consulta que me han solicitado mucho los agentes de ventas y que a ustedes les puede servir mucho de igual manera, saludos!.

select a.CardCode,

c.CardName,

a.DocDate,

DATEPART(month , a.DocDate) AS Periodo,

DATEPART(YEAR , a.DocDate) AS Año,

case a.DocSubType 

when 'DN' THEN 'Nota de débito'

when '--' THEN 'Factura'

END AS [Tipo de dcumento], 

a.DocNum,

a.DocEntry IrDocumento,

b.ItemCode,

case a.DocType When 'S' THEN b.dscription ELSE d.ItemName  END AS ItemName, 

b.Price,

case a.CANCELED

WHEN 'C' THEN case a.DocType When 'S' THEN -1 ELSE b.Quantity * -1  END

WHEN 'N' THEN case a.DocType When 'S' THEN 1 ELSE b.Quantity END

ELSE case a.DocType When 'S' THEN 1 ELSE b.Quantity END

END as Quantity,

case a.CANCELED WHEN 'C'

THEN case a.DocType When 'S'

 THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1) * - 1 ELSE (b.Price * 1) * e.rate  * -1 END

ELSE case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)  * -1 ELSE (b.Price * b.Quantity) * e.rate * -1 END

END

WHEN 'N' THEN

case a.DocType When 'S'

        THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1)  ELSE (b.Price * 1) * e.rate END

ELSE          case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)  ELSE (b.Price * b.Quantity) * e.rate END

END

ELSE

case a.DocType When 'S'

        THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1)  ELSE (b.Price * 1) * e.rate END

ELSE          case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)  ELSE (b.Price * b.Quantity) * e.rate END

END

END

AS [Total MXN],

case a.CANCELED WHEN 'C'

THEN case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1) * -1 ELSE (b.Price * 1) / e.rate * -1 END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity)  * -1 ELSE (b.Price * b.Quantity)  / e.rate* -1 END

END

WHEN 'N' THEN

case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1)  ELSE (b.Price * 1)  / e.rate END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity) ELSE (b.Price * b.Quantity) / e.rate END

END

ELSE

case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1)  ELSE (b.Price * 1)  / e.rate END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity) ELSE (b.Price * b.Quantity) / e.rate END

END

END

 AS [Total USD],

a.CANCELED,

a.Comments

from OINV a

inner join INV1 b on  a.docentry = b.docentry

inner join OCRD c on a.CardCode = c.CardCode

left join OITM d on b.ItemCode = d.ItemCode 

left join ORTT e on a.CreateDate = e.RateDate and e.Currency = 'USD'

where a.docdate between [%0]  and [%1]

UNION ALL

select a.CardCode,

c.CardName,

a.DocDate,

DATEPART(month , a.DocDate) AS Periodo,

DATEPART(YEAR , a.DocDate) AS Año,

case a.ObjType 

when 14 THEN 'Nota de Crédito'

END AS [Tipo de dcumento], 

a.DocNum,

a.DocEntry IrDocumento,

b.ItemCode,

case a.DocType When 'S' THEN b.dscription ELSE d.ItemName  END AS ItemName, 

b.Price,

case a.CANCELED

WHEN 'C' THEN case a.DocType When 'S' THEN 1 ELSE b.Quantity  END

WHEN 'N' THEN case a.DocType When 'S' THEN -1 ELSE b.Quantity * - 1 END

ELSE case a.DocType When 'S' THEN -1 ELSE b.Quantity * -1 END

END as Quantity,

case a.CANCELED WHEN 'C'

THEN case a.DocType When 'S'

 THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1) ELSE (b.Price * 1) * e.rate   END

ELSE case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)   ELSE (b.Price * b.Quantity) * e.rate  END

END

WHEN 'N' THEN

case a.DocType When 'S'

        THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1) * -1 ELSE (b.Price * 1) * e.rate * -1 END

ELSE          case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity) * -1  ELSE (b.Price * b.Quantity) * e.rate * -1 END

END

ELSE

case a.DocType When 'S'

        THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1) * -1  ELSE (b.Price * 1) * e.rate * -1 END

ELSE          case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)  * -1 ELSE (b.Price * b.Quantity) * e.rate * -1 END

END

END

AS [Total MXN],

case a.CANCELED WHEN 'C'

THEN case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1)  ELSE (b.Price * 1) / e.rate  END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity)   ELSE (b.Price * b.Quantity)  / e.rate  END

END

WHEN 'N' THEN

case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1) * -1 ELSE (b.Price * 1)  / e.rate * -1 END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity) * -1 ELSE (b.Price * b.Quantity) / e.rate * -1 END

END

ELSE case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1) * -1 ELSE (b.Price * 1)  / e.rate * -1 END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity) * -1 ELSE (b.Price * b.Quantity) / e.rate * -1 END

END

END

 AS [Total USD],

--d.ItemName, 

--b.Price,

--b.Quantity * -1,

--case a.CurSource WHEN 'L' THEN  b.Price * b.Quantity * -1 ELSE (b.Price * b.Quantity * -1) * e.rate END AS [Total MXN],

--case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity * -1) / e.rate ELSE (b.Price * b.Quantity * -1) END AS [Total USD],

a.CANCELED,

a.Comments

from ORIN a

--left join RIN1 e on a.Docentry = e.BaseEntry

inner join RIN1 b on  a.docentry = b.docentry

inner join OCRD c on a.CardCode = c.CardCode

left join OITM d on b.ItemCode = d.ItemCode 

left join ORTT e on a.CreateDate = e.RateDate and e.Currency = 'USD'

where a.docdate between [%0]  and [%1]

Compras - Partidas abiertas por Item

Para los que nos preguntemos que material de nuestros pedidos aún no están en los almacenes, pueden resolver esa inquietud con esta consulta que aquí les dejo, contempla las OC que van directamente a almacén y las OC que pasan previamente por una factura para después iniciar la o las entradas al almacén.

Saludos!.

Select a.docdate, a.DocNum, a.CardCode, a.CardName, b.ItemCode, b.Dscription
, b.WhsCode, b.Quantity, b.InvntSttus,  b.LineStatus, b.OpenQty, d.SlpName as [Comprador]
, (select DocNum from OPRQ where DocEntry =  (select Top 1 T1.BaseEntry from POR1 T1 inner join OPOR T0 on T0.DocEntry = T1.DocEntry
  where T0.Segment = 0 and T1.BaseEntry is not null and T0.DocNum = a.DocNum)) as SC
,(select ReqName from OPRQ where DocEntry =  (select Top 1 T1.BaseEntry from POR1 T1 inner join OPOR T0 on T0.DocEntry = T1.DocEntry
  where T0.Segment = 0 and T1.BaseEntry is not null and T0.DocNum = a.DocNum)) as Solicitante
from OPOR a
inner join POR1 b on a.DocEntry = b.DocEntry
inner join OITM c on b.ItemCode = c.ItemCode
inner join OSLP d on a.SlpCode = d.SlpCode
and b.InvntSttus = 'O' and b.TargetType in (20,-1)
and a.CANCELED = 'N'
and c.frozenFor = 'N'
and c.validFor = 'Y'
and c.InvntItem = 'Y'
and a.DocStatus <> 'C'

union all

select a.docdate, a.DocNum, a.CardCode, a.CardName, b.ItemCode, b.Dscription
, b.WhsCode, b.Quantity, d.InvntSttus, b.LineStatus, d.OpenQty, f.SlpName as [Comprador]
 ,   (select DocNum from OPRQ where DocEntry = (select Top 1 b.BaseEntry from POR1 b inner join OPOR a on a.DocEntry = b.DocEntry
  where a.Segment = 0 and b.BaseEntry is not null and a.DocNum = (select distinct DocNum from OPOR where DocEntry = d.BaseEntry and d.BaseType = 22))) as SC
 ,   (select ReqName from OPRQ where DocEntry = (select Top 1 b.BaseEntry from POR1 b inner join OPOR a on a.DocEntry = b.DocEntry
  where a.Segment = 0 and b.BaseEntry is not null and a.DocNum = (select distinct DocNum from OPOR where DocEntry = d.BaseEntry and d.BaseType = 22))) as Solicitante
from OPOR a
inner join POR1 b on a.DocEntry = b.DocEntry
inner join OPCH c on b.TrgetEntry = c.DocEntry
inner join PCH1 d on c.DocEntry = d.DocEntry
inner join OITM e on b.ItemCode = e.ItemCode
inner join OSLP f on a.SlpCode = f.SlpCode
and d.InvntSttus = 'O' and b.TargetType in (18,-1)
and e.frozenFor = 'N'
and e.validFor = 'Y'
and e.InvntItem = 'Y'
and d.BaseLine = b.LineNum and d.ItemCode = b.ItemCode
and a.CANCELED = 'N'

Tesorería - Pagos efectuados

Hola!, ¿como están?... hoy les comparto una consulta con la cual podrán generar infinidad de reportes ya sean masivos o individuales para cada uno de sus proveedores, por ejemplo desde un Crystal Report.

Esta consulta tiene la información necesaria para poder aprovecharla de la mejor manera que ustedes decidan, si algo les hiciera falta no duden colocarlo en los comentarios.

SELECT T5.Street, T5.StreetNo, T5.Block, T5.City, T5.County, UPPER(t6.Name) as State
, UPPER(T7.Name) as Country, T5.ZipCode, convert(varchar,T1.[DocDate],103) as Fecha, T1.DocEntry, T1.[DocNum]  as Pago, T3.[DocNum]  as Provision
, CASE when t3.isIns = 'Y'  then 'OC(s) ' +
ISNULL                          ((SELECT     STUFF
                                                        ((SELECT  distinct   ',' + CAST(DocNum AS VARCHAR(15))
                                                            FROM         OPOR a
                                                            inner join POR1 b on a.DocEntry = b.DocEntry
                                                            WHERE     b.TrgetEntry = t3.DocEntry
                                                            and a.CANCELED ='N' FOR XML PATH('')), 1, 1, '')), ' - ')
 else
 t3.[FolioPref] + '-' + CAST(T3.[FolioNum] as VARCHAR (15)) end as Factura
, T1.[CardCode] as [Codigo Proveedor]
,  T1.[CardName] as [Nombre Proveedor], T9.E_mail, 'OP' as [Operacion Terminada]
, case t1.DocCurr when  'USD' then t3.DocTotalFC when 'MXN' Then t3.DocTotal end as [Monto Factura]
, case t1.DocCurr when  'USD' then AppliedFC when 'MXN' Then SumApplied end as [Monto Pagado]
,  t1.DocCurr as 'Moneda'
, CASE when t3.isIns = 'Y' then '' else  t3.NumAtCard end as UUID, convert(varchar,T1.TrsfrDate,103) as TrsfrDate
, CONVERT(varchar,convert(money,case t1.DocCurr when 'USD' then TrsfrSumFC when 'MXN' then T1.TrsfrSum end), 1) as [Monto Transferencia]
, T8.AcctName
FROM [dbo].[OVPM]  T1 INNER JOIN VPM2 T2 ON T1.DocEntry = T2.DocNum
 INNER JOIN OPCH T3 ON T2.DocEntry = T3.DocEntry
 INNER JOIN OCRD T4 ON T4.CardCode = T1.CardCode
 LEFT JOIN CRD1 T5 ON T5.CardCode = T4.CardCode and T5.AdresType = 'B'
 LEFT JOIN OCST T6 ON T6.Code = T5.State
 LEFT JOIN OCRY T7 ON t7.Code = T5.Country
 LEFT JOIN OACT T8 on T8.AcctCode =  T1.TrsfrAcct
 LEFT JOIN OCRD T9 on T9.CardCode = T1.CardCode
WHERE T1.[CreateDate] between [%0] and [%1]
and T1.Canceled = 'N'

Compras - Ultimo precio de compra

Para conocer el ultimo precio de compra de un articulo o servicio posiblemente habrán muchas maneras, la mas fácil es la siguiente consulta hacia la tabla de los Datos Maestros de Artículo: 

SELECT T1.[LastPurPrc] FROM OITM T1 Where T1.ItemCode = 'XxXxX'

Pero la consulta anterior no nos permite saber cuando fue la fecha de esa ultima compra, ni tampoco quien fue el proveedor por poner solo unos ejemplos, para estos casos les dejo la siguiente consulta.

select distinct * from (
select b.DocDate, b.CardCode, b.CardName, a.ItemCode, a.Dscription, a.Price from PCH1 a
inner join OPCH b on a.DocEntry = b.DocEntry
where a.ItemCode = 'XxXxX'
and b.CANCELED = 'N'
) tabla
inner join (select e.ItemCode , MAX(f.docdate) docdate from PCH1 e
inner join OPCH f on e.DocEntry = f.DocEntry and e.ItemCode = 'XxXxX') and f.CANCELED = 'N' group by e.ItemCode) tabla2
on tabla.ItemCode = tabla2.ItemCode and tabla.DocDate = tabla2.docdate
order by  tabla.ItemCode, tabla.docdate

Nota: Si este Item se repite, es por que en la misma fecha hubo compras con diferentes precios o diferentes proveedores.

Espero les sirva, saludos!.

Producción - Costo de fabricación Con BOM (Lista de Materiales)

Que tal?, como han estado?, les comparto una consulta que puede ser de utilidad, en la cual podrán ver los costos que esta generando su producción.

Para este ejemplo en particular ya se encuentran la lista de materiales de los productos a producir cargada (La mejor practica), lista que se consume a diario a través de un almacén ficticio destinado para el consumo de la producción, el cual tendrían que colocar en las XxXx que están en negritas.

Por lo que al final esta consulta les dará detalles de los movimientos (Entradas, Transferencias y Salidas) de los materiales consumidos por día en un rango de fechas.

select Case TransType
when 60 then 'Salida'
when 59 then 'Entrada'
when 67 then 'Transferencia' end as [Movimiento], a.Currency
, a.DocDate, BASE_REF as [No. Documento], c.filler as [From Whse], c.ToWhsCode as [To Whse], a.Warehouse
, d.DocNum as [OF], case d.Type when 'S' then 'Estandár'
when 'D' then 'Desmonte'
when 'P' then 'Especial' End as [Tipo OF], d.PlannedQty, d.Uom, d.ItemCode as [Codigo Producto], e.ItemName
, a.ItemCode as [Codigo Material], a.Dscription, a.InQty, a.OutQty * -1 as [OutQty]
, CASE TransType
when 60 then g.unitMsr
when 59 then f.unitMsr
when 67 then h.unitMsr
end as [UM]
, case a.currency when 'USD' then a.Price * a.Rate
  when 'MXN' then a.Price end as Price
,  a.CalcPrice
, Case TransType
when 60 then  (a.OutQty * a.CalcPrice) * -1
when 59 then  (a.InQty * a.CalcPrice)
when 67 then
case c.ToWhsCode
when 'XxXx' then (a.InQty * a.CalcPrice)
else (a.OutQty * a.CalcPrice) * -1  end end as [Total MXN]
, Case TransType
when 60 then (a.OutQty * a.CalcPrice) * -1  / b.Rate
when 59 then (a.InQty * a.CalcPrice) / b.Rate
when 67 then
case c.ToWhsCode
when 'XxXx' then  (a.InQty * a.CalcPrice)  / b.Rate
else (a.OutQty * a.CalcPrice) * -1 / b.Rate end end as [Total USD]
, a.Comments, a.JrnlMemo
from OINM a Inner join ORTT b on a.DocDate = b.RateDate
left join OWTR c on c.DocEntry = a.CreatedBy
left join OWOR d on d.DocEntry = a.AppObjAbs and a.ApplObj = 202
left join OITM e on e.ItemCode = d.ItemCode
left join IGN1 f on f.DocEntry = a.CreatedBy and a.ItemCode = f.ItemCode and a.DocLineNum = f.LineNum
left join IGE1 g on g.DocEntry = a.CreatedBy and a.ItemCode = g.ItemCode and a.DocLineNum = g.LineNum
left join WTR1 h on h.DocEntry = a.CreatedBy and a.ItemCode = h.ItemCode and a.DocLineNum = h.LineNum
where TransType in (67,60,59)
and a.DocDate >= [%0]
and  a.DocDate <= [%1]
and (a.Warehouse = XxXx)
and b.Currency = 'USD'
order by DocDate

Compras - Mercadería en transito

Les dejo una consulta que puede serles de mucha utilidad, en ella podrán ver las facturas reserva que no presentan aún entradas al almacén, solo deberán colocar en donde existe la condición de cuenta (remarcado en negro) sus cuentas contables de mercadería en transito, esta debe cuadrar contra la del balance.

Saludos!.

select t0.DocStatus, T0.CreateDate, t0.DocDate,T0.DocNum, T0.CardCode, T0.CardName, T1.ItemCode, T1.Dscription, t1.AcctCode, t2.acctname
, t1.Price, t1.Currency, T1.Quantity as [Cantidad Solicitada], T1.LineTotal  as [LineTotal]
,  T1.LineTotal / (select T10.Rate from ORTT T10 where T10.Currency = 'USD' and T10.RateDate = T0.DocDate)  as [LineTotal USD]
,T1.Quantity - isnull(( select SUM(T10.Quantity) from PDN1 T10 inner join OPDN T11 on T10.DocEntry = t11.DocEntry
where    T10.ItemCode = t1.ItemCode and T11.CANCELED = 'N' and t10.BaseEntry = t1.DocEntry and T10.BaseLine = T1.LineNum ),0) as [Cantidad en Transito]
, CASE T0.DocCur when 'MXN' THEN T1.Price * (T1.Quantity - isnull(( select SUM(T10.Quantity) from PDN1 T10 inner join OPDN T11 on T10.DocEntry = t11.DocEntry
where    T10.ItemCode = t1.ItemCode and T11.CANCELED = 'N' and t10.BaseEntry = t1.DocEntry and T10.BaseLine = T1.LineNum ),0))
                 when 'USD' THEN  T1.Price * (T1.Quantity - isnull(( select SUM(T10.Quantity) from PDN1 T10 inner join OPDN T11 on T10.DocEntry = t11.DocEntry
where    T10.ItemCode = t1.ItemCode and T11.CANCELED = 'N' and t10.BaseEntry = t1.DocEntry and T10.BaseLine = T1.LineNum ),0)) * (select T10.Rate from ORTT T10 where T10.Currency = 'USD' and T10.RateDate = T0.DocDate)
                 End as [LineTotal en Transito]
, CASE T0.DocCur when 'MXN' THEN T1.Price * (T1.Quantity - isnull(( select SUM(T10.Quantity) from PDN1 T10 inner join OPDN T11 on T10.DocEntry = t11.DocEntry
where    T10.ItemCode = t1.ItemCode and T11.CANCELED = 'N' and t10.BaseEntry = t1.DocEntry and T10.BaseLine = T1.LineNum ),0)) / (select T10.Rate from ORTT T10 where T10.Currency = 'USD' and T10.RateDate = T0.DocDate)
                 when 'USD' THEN  T1.Price * (T1.Quantity - isnull(( select SUM(T10.Quantity) from PDN1 T10 inner join OPDN T11 on T10.DocEntry = t11.DocEntry
where    T10.ItemCode = t1.ItemCode and T11.CANCELED = 'N' and t10.BaseEntry = t1.DocEntry and T10.BaseLine = T1.LineNum ),0))
                 End as [LineTotal en Transito USD]
, (select SUM(T10.linetotal) from PCH1 T10 where T10.DocEntry = T0.DocEntry)   as [Sub Total Fact]
, (select SUM(T10.LineTotal) / (select T10.Rate from ORTT T10 where T10.Currency = 'USD' and T10.RateDate = T0.DocDate) from PCH1 T10 where T10.DocEntry = T0.DocEntry) as [Sub Total Fact USD]
, T0.VatSum as [IVA Fact]
, CASE T0.DocCur when 'MXN' THEN T0.VatSum / (select T10.Rate from ORTT T10 where T10.Currency = 'USD' and T10.RateDate = T0.DocDate)
                 when 'USD' THEN  T0.VatSumFC
                 End as [IVA USD Fact]
, T0.[DocTotal] as [Total Fact]
, CASE T0.DocCur when 'MXN' THEN T0.[DocTotal] / (select T10.Rate from ORTT T10 where T10.Currency = 'USD' and T10.RateDate = T0.DocDate)
                 when 'USD' THEN  T0.[DocTotalFC]
                 End as [Total Fact USD]
from OPCH T0
INNER JOIN PCH1 T1 on T0.DocEntry = T1.DocEntry
INNER JOIN OACT T2 on T1.AcctCode = T2.AcctCode
where T0.isIns = 'Y'
and T1.InvntSttus = 'O'
and T1.TargetType in (20,-1)
and T0.CANCELED in ('C','N')
and T1.AcctCode in ('XXXXXX')
order by DocDate

Current user has no permissions for this operation

Si al intentar editar un reporte Crystal Report desde SAP B1 te surge el siguiente mensaje: "Current user has no permissions for this operation", es muy posible que el reporte sea del sistema y por lo tanto solo tienes permiso de solo lectura, puedes cerciorarte de ello desde el Manage Layout, el reporte de Crystal te dirá entre paréntesis si es así.

La única opción que nos queda es copiar este reporte abriéndolo y dándole en Guardar Como, para después importarlo desde el mismo Manage Layout, una vez cargado podrás editarlo cuanto quieras 😃.

Gestión - Autorizador por Modelo de Autorización en SAP

¿Necesitas saber quienes autorizan los modelos de autorización activos?, esta sencilla consulta te arroja esa información:

select a.Name 'Nombre Modelo', a.Remarks 'Descripción Modelo', c.Name as 'Nombre Etapa' , c.Remarks as 'Descripción Etapa'
,  e.U_NAME as  'Autorizador', c.MaxReqr as 'Autorizaciones requeridas', c.MaxRejReqr as 'Rechazos requeridos'
from OWTM a
inner join WTM2 b on a.WtmCode = b.WtmCode
inner join OWST c on b.WstCode = c.WstCode
inner join WST1 d on c.WstCode = d.WstCode
inner join OUSR e on d.UserID = e.USERID
where a.Active = 'Y'
order by 
[Nombre Modelo] , Autorizador

Si además quieres saber los autores de cada Modelo de Autorización, ocupa también esta Query:

select a.Name as 'Nombre Modelo', a.Remarks as 'Descripción Modelo'
, e.U_NAME as 'Autor'
from OWTM a
inner join WTM1 b on a.WtmCode = b.WtmCode
inner join OUSR e on b.UserID = e.USERID
where a.Active = 'Y'
order by [autor], [Nombre Modelo]

TN - Bloquear fecha de vencimiento facturas proveedor SAP

Esta necesidad de bloquear la fecha de vencimiento en las facturas es muy usual y para los que administramos SAP 9.0 se nos complica un poco dado que no existe a nivel sistema una forma de bloquear el campo al usuario, pero esto lo arreglamos con este sencillo TN:

if @object_type = '18' AND @transaction_type IN ('A', 'U')
Begin
    If EXISTS ( SELECT T0.DocNum
FROM OPCH T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
inner join OCTG t2 on t2.GroupNum=t1.GroupNum
WHERE T0.[DocEntry] = @list_of_cols_val_tab_del
AND t0.DocDueDate <> (t0.DocDate+t2.ExtraDays)
)
    Begin
        SET @error = 0201006
        SET @error_message = 'Fecha de vencimiento no permitida de acuerdo a las condiciones de pago con el proveedor'
    End   
End

hasta la próxima!.

Quitar carácter especial en consulta SQL Server

Tuve por varios días un problema que surgió por una comilla simple que SQL Server traducía como caracter especial las cuales venían de un sistema auxiliar de SAP y que cargaba por DTW, esto hacia que no pudiera hacer un Join pues las descripciones eran distintas entre un sistema y otro, como no hallaba la solución a través de alguna función o de forma nativa desde SQL Server para eliminarla y ante mi carga de trabajo que me impedía seguir buscando, decidí darle rienda suelta a mi imaginación y pues aquí les dejo mi solución, si tienen una mejor, no sean malos déjenla en los comentarios.

Utilizando el CHARINDEX y el SUBSTRING creo nuevas descripciones uniformes dentro de una tabla creada a partir de una subconsulta la cual uno con otra subconsulta con las tablas de SAP, para ya sin el caracter especial hacer el match.

select * from (
 select NOM_GEN_POL as [Nombre Poliza Sistema Aux], replace(Ref1,'Pól','Pól')  as [Nombre Poliza SAP], FECHA_GEN_POL as [Fecha Poliza], [Importe Poliza SAP]  , [Importe Poliza Sistema Aux]
from (
select CONVERT(Decimal(15,2),SUM(c.IMPORTE),2) as [Importe Poliza Sistema Aux]
,CASE when b.NOM_GEN_POL like '%PEÑ%' then SUBSTRING(b.NOM_GEN_POL,0, CHARINDEX('PEÑ', b.NOM_GEN_POL) + 2) + SUBSTRING(b.NOM_GEN_POL,CHARINDEX('PEÑ', b.NOM_GEN_POL) + 3, 20) else b.NOM_GEN_POL end as NOM_GEN_POL, b.FECHA_GEN_POL
from Sistema_Aux.dbo.RH_ENC_POL  b
inner join  Sistema_Aux.dbo.RH_POLIZA_NOMINA a on a.CLA_GEN_POL = b.CLA_GEN_POL
inner join  Sistema_Aux.dbo.RH_DET_POL_IND c on a.NUM_NOMINA = c.NUM_NOMINA
and c.TIPO_CAR_CRE = 2
and c.CLA_PERIODO = a.CLA_PERIODO
and b.FECHA_GEN_POL > '20180531'
group by b.NOM_GEN_POL, b.FECHA_GEN_POL) Tabla_Sistema_Aux
left join (
select  CONVERT(Decimal(15,2),sum(Loctotal),2) as [Importe Poliza SAP], TransCode
, CASE when Ref1 like '%PEÃ%' then SUBSTRING(isnull(Ref1,''),0, CHARINDEX('PEÃ', isnull(Ref1,'')) +2) + SUBSTRING(isnull(Ref1,''),CHARINDEX('PEÃ', isnull(Ref1,'')) + 4, 20) else Ref1 end as ref1--, RefDate
from OJDT
where  TransType = 30 and TransCode in ('NO01','FN01')
and RefDate >  '20180531'
group by transcode, Ref1)--, RefDate)
Tabla_SAP On (replace(replace(Tabla_SAP.Ref1,'Pól','Pól'), ' - [', '- [') = replace(Tabla_Sistema_Aux.NOM_GEN_POL COLLATE Modern_Spanish_CS_AS, ' - [', '- [')
 )
 ) Consulta where isnull([Importe Poliza SAP],0) <> isnull([Importe Poliza Sistema Aux],0)
order by [Fecha Poliza]

Nota: mi versión de SQL Server es 2008 R2.

TN - Validar campo de horas y minutos en SAP

Como programador descubrí el poder de las expresiones regulares y como estas te pueden salvar la vida 😅, les mostrare algo que seguramente en algún momento necesitaran ocupar, es un ejemplo de lo que se puede hacer con las expresiones regulares en SQL Server, para esto necesitaremos ocupar el operador Like.

Si requieren validar los datos de hora y minutos que ingresan sus usuarios al momento de realizar alguna transacción, esto lo pueden hacer a través del Transaction Notification, les dejo abajo el script (la parte en negrita es lo que hace la magia), espero les sea de provecho.

Saludos!.

if @object_type = '23' and @transaction_type in ('U','A')

BEGIN

if EXISTS(select DocEntry from OQUT

where Docentry = @list_of_cols_val_tab_del

and (U_Observaciones <> ''

and U_Observaciones not like  '[2][0-3]:[0-5][0-9]'

and U_Observaciones not like '[0-1][0-9]:[0-5][0-9]')

OR DATEDIFF(MINUTE,U_destino,U_Observaciones) < 0

and Series in ('16'))

begin

Set @error = 60003

Set @error_message = 'Verifique el dato a ingresar'

end

END 

Importe de reconciliación debe ser inferior a saldo vencido para esta transacción

¿Que tal?, estoy de vuelta con un caso que me surgió la semana pasada el cual a las personas que usen SAP 9.2 PL 8 o mas recientes ya no lo presentarán, ya que es un bug de SAP el cual ya fue resuelto a partir de esa versión, el mensaje de error generalmente sucede cuando se cancela un pago en dolares de una factura en dolares, esta al querer volver a generar el pago efectuado nos arroja el siguiente error: "Importe de reconciliación debe ser inferior a saldo vencido para esta transacción", buscando en Internet me encontré que lo que se tiene que hacer es informar a SAP para que nos realice un update a nuestra BD, esto nos puede demorar hasta 5 días en promedio esto si ya cubren todos los requisitos que SAP solicita, ellos se demoran hasta dos días en enviar la solución que se tiene que realizar primero en ambiente de pruebas, pero esto es posible evitarlo realizando el pago directo a cuenta a nombre del Socio de negocio y después reconciliando este con la factura, de esta forma el saldo del socio de negocio queda en cero y el pago es cubierto en su totalidad.

Espero les sea de ayuda esta solución para evitar todo el proceso con SAP, Saludos!.