Detallete Retencion En Compras

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,'')='')
1 me gusta

Alguna sugerencia?? ayuda???

Hola @Carmenza_Aren:

Buenas tardes, quisiera saber si ya resolvió esto y cómo lo hizo?

Saludos :raised_hand_with_fingers_splayed:t5:

Hola Carmenza
Revisa el informe estandar que trae SAP para esto.
Finanzas->Informes Financieros-> Finanzas->Impuesto->Informe de retencion de impuestos

Andres Ramirez Jaramillo

1 me gusta

Este tema se cerró por inactividad por parte del autor.

Copia la URL de este debate, y abre un nuevo tema en #feedback si:

  • El autor del debate no marcó ninguna respuesta como solución, y tú crees tener la solución
  • Crees tener otra solución a la que actualmente está marcada.

Si, en cambio tienes una duda parecida a la que se debatió, o la misma duda, abre un nuevo tema en la categoría que corresponda y pon que el tema se debatió oportunamente (pega el enlace a este debate), así los otros lectores pueden saber de qué hablas.

Ayúdanos a tener una comunidad organizada.