Buenas Tardes, después de trabajar durante un par de meses para llegar a este resultado les dare un aporte espero que a los usuarios de sap b1 Hana les ayude mucho. El query en cuestión trata de los pagos efectuados a proveedor por fecha y trae todo lo relacionado a la factura que avalan los pagos incluidas notas de crédito que no tengan relación directa o de reconciliación interna.
SELECT DISTINCT T2."CardCode",T2."CardName",
T2."DocNum" AS "Factura Proveedor",
T2."DocDate" AS "Fecha Fac Prov",
(SELECT sum(AQ."LineTotal") FROM PCH1 AQ WHERE AQ."DocEntry"=T2."DocEntry" and AQ."VatPrcnt"=0) as "Base Fac I.V.A. 0%",
(SELECT sum(AQ."LineTotal") FROM PCH1 AQ WHERE AQ."DocEntry"=T2."DocEntry" and AQ."VatPrcnt"=8) AS "Base Fac I.E.P.S. 8%",
(SELECT sum(AQ."VatSum") FROM PCH1 AQ WHERE AQ."DocEntry"=T2."DocEntry" and AQ."VatPrcnt"=8) AS "Importe Fac I.E.P.S. 8%",
(SELECT sum(AQ."LineTotal") FROM PCH1 AQ WHERE AQ."DocEntry"=T2."DocEntry" and AQ."VatPrcnt"=16) AS "Base Fac I.V.A. 16%",
(SELECT sum(AQ."VatSum") FROM PCH1 AQ WHERE AQ."DocEntry"=T2."DocEntry" and AQ."VatPrcnt"=16) AS "Importe Fac I.V.A. 16%",
T2."VatSum" AS "Total I.V.A. Fac.",
T2."DocTotal" AS "Total Fac",
A2."DocNum" AS "Nota Credito",
A2."DocDate" AS "Fecha N/C",
(SELECT sum((AW."LineTotal")*-1) FROM RPC1 AW WHERE AW."DocEntry"=A2."DocEntry" and AW."VatPrcnt"=0) as "Base N/C I.V.A. 0%",
(SELECT sum((AW."LineTotal")*-1) FROM RPC1 AW WHERE AW."DocEntry"=A2."DocEntry" and AW."VatPrcnt"=8) AS "Base N/C I.E.P.S. 8%",
(SELECT sum((AW."VatSum")*-1) FROM RPC1 AW WHERE AW."DocEntry"=A2."DocEntry" and AW."VatPrcnt"=8) AS "Importe N/C I.E.P.S. 8%",
(SELECT sum((AW."LineTotal")*-1) FROM RPC1 AW WHERE AW."DocEntry"=A2."DocEntry" and AW."VatPrcnt"=16) AS "Base N/C I.V.A. 16%",
(SELECT sum((AW."VatSum")*-1) FROM RPC1 AW WHERE AW."DocEntry"=A2."DocEntry" and AW."VatPrcnt"=16) AS "Importe N/C I.V.A. 16%",
(A2."DocTotal" *-1) AS "Total NC",
B2."DocNum" AS "Num Pago",
B2."DocDate" AS "Fecha Pago",
B2."CashSum" as "Efectivo",
B2."CheckSum" as "Cheque",
B2."TrsfrSum" as "Transf",
B2."DocTotal" as "Total",
B2."DocDate" as "Fecha",
(SELECT BA2."SumApplied" FROM VPM2 BA2 where BA2."DocNum" = B2."DocEntry" and BA2."DocEntry"=T2."DocEntry" ) AS "Importe Aplicado",
B2."DocTotal" as "Monto Total Pago"
FROM OITR T0
INNER JOIN ITR1 T1 ON T0."ReconNum" = T1."ReconNum"
INNER JOIN OPCH T2 ON T1."TransId" = T2."TransId"
LEFT JOIN ITR1 A1 ON T0."ReconNum" = A1."ReconNum"
LEFT JOIN ORPC A2 ON A1."TransId" = A2."TransId" AND A2."CANCELED" = 'N'
LEFT JOIN ITR1 B1 ON T0."ReconNum" = B1."ReconNum"
LEFT JOIN OVPM B2 ON B1."TransId" = B2."TransId" AND B2."Canceled" = 'N'
WHERE T0."ReconType" IN (0,4,3) AND (A2."DocNum" IS NOT NULL OR B2."DocNum" IS NOT NULL) AND (
B2."DocDate" BETWEEN [%0] AND [%1]
OR T2."DocNum" IN (
SELECT T2_Sub."DocNum"
FROM OITR T0_Sub
INNER JOIN ITR1 T1_Sub ON T0_Sub."ReconNum" = T1_Sub."ReconNum"
INNER JOIN OPCH T2_Sub ON T1_Sub."TransId" = T2_Sub."TransId"
LEFT JOIN ITR1 A1_Sub ON T0_Sub."ReconNum" = A1_Sub."ReconNum"
LEFT JOIN ORPC A2_Sub ON A1_Sub."TransId" = A2_Sub."TransId" AND A2."CANCELED" = 'N'
LEFT JOIN ITR1 B1_Sub ON T0_Sub."ReconNum" = B1_Sub."ReconNum"
LEFT JOIN OVPM B2_Sub ON B1_Sub."TransId" = B2_Sub."TransId" AND B2_Sub."Canceled" = 'N'
WHERE B2_Sub."DocDate" BETWEEN [%0] AND [%1] and (A2."DocNum" IS NOT NULL )
)
)
ORDER BY 1,3,12