Hola compañeros hace algún tiempo baje un query del portal de SAP, el cual modifique para el departamento de Contabilidad, el cual querían los pagos recibidos para pagar los impuestos ( IVA) y me pidieron adicionar el UUID para ver cual tenia complemento de pago.
Esto es para México.
espero les ayude en algo o para que inicien a analizar y modificar sus Querys
--------------------------------------------
-- Bajado https://www.consultoria-sap.com/
-- Modificado LxRealmtz (lxrealmtz@gmail.com)
----------------------------------------------
SELECT Distinct T1.DocDate 'F.Pago', T1.DocNum 'No.Pago', T9.SeriesName 'Serie',
-- Seleccion segun tipo de docuemnto para asignarlo al Campo
'Factura' = Case When T3.InvType = '13' Then T4.DocNum
When T3.InvType = '14' Then T11.DocNum
When T3.InvType = '203' Then T10.DocNum
When T3.InvType = '30' Then T12.TransId
End,
-- selecion de fecha segun documento
'Fecha Fact' = Case When T3.InvType = '13' Then T4.DocDate
When T3.InvType = '14' Then T11.DocDate
When T3.InvType = '203' Then T10.DocDate
When T3.InvType = '30' Then T12.RefDate End,
T1.CardCode as "Codigo", T1.CardName as "Cliente",
-- Asignacion segun documento
Documento = Case When T3.InvType = '13' Then 'Factura'
When T3.InvType = '14' Then 'N. Credito'
When T3.InvType = '203' Then 'T 203'
When T3.InvType = '30' Then 'T 30'
End,
T1.TrsfrSum,
--- Case para Facturas en positivo y NOtas de Credito en Negativo solo se mulitiplica la cantidad por -1
CASE WHEN T3.InvType = '14' THEN ((T4.DocTotal+ T4.WTAppliedS)/1.16)* -1 ELSE (T4.DocTotal+ T4.WTAppliedS)/1.16 END 'Sub-Total',
CASE WHEN T3.InvType = '14' THEN (((T4.DocTotal+ T4.WTAppliedS) /1.16)*0.16)*-1 ELSE ((T4.DocTotal+ T4.WTAppliedS) /1.16)*0.16 END ' I V A',
CASE WHEN T3.InvType = '14' THEN T4.WTAppliedS * -1 ELSE T4.WTAppliedS END 'Retención 6%',
CASE WHEN T3.InvType = '14' THEN T4.DocTotal * -1 ELSE T4.DocTotal END 'Total Documento',
T1.[DocNum]as 'Codigo del Pago',
--- Case para Facturas en positivo y NOtas de Credito en Negativo solo se mulitiplica la cantidad por -1
CASE WHEN T3.InvType = '14' THEN (T3.VatApplied / 0.16) * -1 ELSE T3.VatApplied / 0.16 END 'Sub Total Pago',
CASE WHEN T3.InvType = '14' THEN (T3.VatApplied) * -1 ELSE T3.VatApplied END 'IVA Pago',
CASE WHEN T3.InvType = '14' THEN T3.WtInvCatSS * -1 ELSE T3.WtInvCatSS END 'Retencion Pago',
CASE WHEN T3.InvType = '14' THEN T3.SumApplied * -1 ELSE T3.SumApplied END 'Total Pago',
SaldoFac = case when T4.Doctotal-T3.[SumApplied] = '0' then '0'
when T4.Doctotal-T3.[SumApplied]<> '0' then T4.Doctotal-T4.PaidToDate
end,
T4.[DocDate]as 'Fecha factura',
T1.DocDate as 'Fecha Pago',
Cancelado = Case When T1.Canceled = 'Y' Then 'SI'
When T1.Canceled = 'N' Then 'NO'
End,
T13.ReportID
FROM OCRD T0
INNER JOIN ORCT T1 ON T0.CardCode = T1.CardCode
INNER JOIN RCT2 T3 ON T3.DocNum = T1.DocNum
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
LEFT JOIN OINV T4 ON T4.DocEntry = T3.DocENtry
LEFT JOIN RCT3 T5 ON T1.DocEntry = T5.DOcNum
LEFT JOIN RCT1 T6 ON T1.DocEntry = T6.DocNum
LEFT JOIN OCRP T7 ON T5.CrTypeCode = T7.CrTypeCode
LEFT JOIN ODSC T8 ON T6.BankCode = T8.BankCode
INNER JOIN NNM1 T9 ON T1.Series = T9.Series
LEFT JOIN ODPI T10 ON T3.DocEntry = T10.DocEntry
LEFT JOIN ORIN T11 ON T3.DocENtry = T11.DocEntry
INNER JOIN OJDT T12 ON T3.DocTransId = T12.TransId
LEFT JOIN ECM2 T13 ON T1.DocNum = T13.[SrcObjAbs] and T13.ActType = 'I'
WHERE T1.DocType = 'C' and T1.DocDate Between [%1] and [%2] Order By T1.DocNum
Fuente del Query
h_tps://answers.sap.com/questions/9634357/reporte-pagos-recibidos.html