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.
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
