Buenas tardes, Como siempre acudo a usted para solucionar un inconveniente que tenemos al generar un query de DETALLE RETENCIÓN EN COMPRAS el query me muestra la información pero hay unas facturas proveedores faltantes. me falta alguna tabla? de antemano gracias por sus respuestas…
```/*SELECT FROM [dbo].[OPCH] P0*/ declare @fromdate as datetime/* WHERE */set @fromdate = /* P0.DocDate */ '[%0]'
/*SELECT FROM [dbo].[OPCH] P1*/ declare @tilldate as datetime/* WHERE */set @tilldate = /* P1.DocDate */ '[%1]'
/*SELECT FROM [dbo].[OWHT] P2*/declare @Tipo as nvarchar(20)/* WHERE */set @Tipo = /* P2.U_Tipo */ '[%2]'
/*SELECT FROM [dbo].[@BPCO_MU] P5*/declare @Mpio as nvarchar(40)/* WHERE */set @Mpio= /* P5.NAME */ '[%3]'
Select(T6.Cod_Ret +' - '+ T6.Retencion) as 'Retencion', str(T6.Wret,10,2) as 'W% ret',
(select TAc.FormatCode FROM OACT TAc WHERE TAc.AcctCode=T6.WAcc) as Cuenta,
T5.Cardcode, T5.CardName,
T5.LicTradNum, T6.Ref as Referencia , T6.ND as 'Numero Documento', T6.Fecha as Fecha, T6.TA as 'Importe Sujeto',
T6.WA as 'Importe Retencion', t7.Name
FROM (
SELECT T1.U_BPCO_MUN as mun, T2.DocDate as Fecha, 'Fact '+ convert(nvarchar(20),T2.Docnum) as ND, T2.NumAtCard as Ref, T1.U_Tipo as TR, T2.Cardcode,
T0.WTCode as Cod_Ret, T1.WTName as 'Retencion', T1.PrctBsAmnt as
'Wret', T0.U_Base_ML as TA, T0.U_Ret_ML as WA, T1.Account as WAcc from PCH5 T0 inner join OWHT T1 On
T0.WTCode=T1.WTCode inner join OPCH T2 On T0.AbsEntry=T2.DocEntry where T2.DocDate between
@fromdate and @tilldate
union all
SELECT T1.U_BPCO_MUN as mun, T2.DocDate as Fecha, 'NC '+ convert(nvarchar(20),T2.Docnum) as ND, T2.NumAtCard as Ref, T1.U_Tipo as TR, T2.Cardcode,
T0.WTCode as 'Cod_Ret', T1.WTName as 'Retencion',T1.PrctBsAmnt as
'Wret', -T0.U_Base_ML as TA, -T0.U_Ret_ML as WA, T1.Account as WAcc from RPC5 T0 inner join OWHT T1 On
T0.WTCode=T1.WTCode inner join ORPC T2 On T0.AbsEntry=T2.DocEntry where T2.DocDate between
@fromdate and @tilldate
union all
Select (select T9.U_BPCO_MUN from OWHT T9 where T9.WTCode=T1.U_WTCode ) as mun,
T2.DocDate as Fecha, 'NC '+ convert(nvarchar(20),T2.Docnum) as ND,T2.NumAtCard as Ref,
(select T9.U_Tipo from OWHT T9 where T9.WTCode=T1.U_WTCode ) as TR, T2.Cardcode,
T1.U_WTCode as 'Cod_Ret', T1.U_WTName as 'Retencion', T1.U_Rate as 'Wret', -T1.U_SbAmnt as 'TA',
-T1.U_WTAmnt as 'WA', T1.U_AcctCode as WAcc from [@OK1_NC_RET_HED] T0 inner join [@OK1_NC_RET_LIN] T1 On
T0.Code=T1.U_DocEntry inner join ORPC T2 On T2.DocEntry=Substring(T0.Code,2,10) where
left(T0.Code,1)='P' and T2.DocDate between @fromdate and @tilldate
union all
SELECT T1.U_BPCO_MUN as mun, T0.RefDate as Fecha, 'Rec. Fact '+ convert(nvarchar(20),T2.Docnum) + ' AS No.' + convert(nvarchar(20), T3.Number) AS ND,
T2.NumAtCard as Ref,
T1.U_Tipo as TR, T2.Cardcode, T1.WTCode as 'Cod_Ret', T1.WTName as 'Retencion',
T1.PrctBsAmnt as 'Wret', T0.U_BaseRet as TA, T0.Credit-T0.Debit as WA, T1.Account as WAcc
FROM JDT1 T0 inner join OJDT T3 On T0.TransID=T3.TransID inner join OWHT T1 On
T0.U_CodRet=T1.WTCode inner join OPCH T2 On T3.U_ClaveDoc=T2.DocEntry
WHERE T3.U_TipoDoc='OPCH' AND T0.RefDate between @fromdate and @tilldate
union all
SELECT T1.U_BPCO_MUN as mun, T0.RefDate as Fecha, 'Rec. NC '+ convert(nvarchar(20),T2.Docnum) + ' AS No.' + convert(nvarchar(20), T3.Number) as ND, T2.NumAtCard as Ref,
T1.U_Tipo as TR, T2.Cardcode, T1.WTCode as 'Cod_Ret', T1.WTName as 'Retencion',
T1.PrctBsAmnt as 'Wret', T0.U_BaseRet as TA, T0.Credit-T0.Debit as WA, T1.Account as WAcc
FROM JDT1 T0 inner join OJDT T3 On T0.TransID=T3.TransID inner join OWHT T1 On
T0.U_CodRet=T1.WTCode inner join ORPC T2 On T3.U_ClaveDoc=T2.DocEntry
WHERE T3.U_TipoDoc='ORPC' AND T0.RefDate between @fromdate and @tilldate
union all
select (select T9.U_BPCO_MUN from OWHT T9 where T9.WTCode=T1.U_CodRet ) as mun,T0.RefDate as Fecha, 'AS '+ convert(nvarchar(20),T0.Number) as ND
, T3.LicTradNum as Ref, T2.U_Tipo as TR, T1.U_InfoCo01, T2.WTCode as 'Cod_Ret'
, T2.WTName as 'Retencion', T2.PrctBsAmnt as 'Wret', T1.U_BaseRet as TA, T1.Credit-T1.Debit as WA, T2.Account as WAcc
from OJDT T0 INNER JOIN JDT1 T1 ON T0.TransID=T1.TransID INNER JOIN OWHT T2 ON T1.U_CodRet=T2.WTCode INNER JOIN OCRD T3 on T3.CardCode =T1.U_InfoCo01
where T0.RefDate between @fromdate and @tilldate and T3.CardType='S'
and isnull(T0.TransCode,'')< >'RECR'
) T6 Inner join OCRD T5 On T5.CardCode=T6.CardCode left join [@BPCO_MU] T7 ON T6.mun=T7.Code
where T6.TR=@Tipo AND T6.WA < >0
and (T7.Name = @Mpio or isnull(@Mpio,'')='')