Buenos días, saludos desde Ecuador, comparto un script donde extrae las carteras de sap y las puedes llevar a crystal.
SELECT
TO_DATE(T0."RefDate") AS "F.Emi",
TO_DATE(T0."DueDate") AS "F.Ven",
T2."CardName",
T2."CardCode",
T1."BaseRef",
T1."Account",
T3."AcctName",
T1."LineMemo" AS "Comentario",
CASE
WHEN T1."TransType" = 13 THEN 'FV- ' || (
SELECT F."FolioNum"
FROM OINV F
WHERE F."DocNum" = T1."BaseRef"
)
WHEN T1."TransType" = 14 THEN 'NCV- ' || T1."BaseRef"
WHEN T1."TransType" = 24 THEN 'PR- ' || T1."BaseRef"
WHEN T1."TransType" = 46 THEN 'PE- ' || T1."BaseRef"
WHEN T1."TransType" = 18 THEN 'FP- ' || T1."BaseRef"
WHEN T1."TransType" = 19 THEN 'NCP- ' || T1."BaseRef"
ELSE T1."TransType" || '- ' || T1."BaseRef"
END AS "Folio",
CASE
WHEN T3."GroupMask" = 1 THEN
CASE
WHEN T1."Credit" > 0 THEN
(T1."Debit" + (T1."Credit") * -1) + IFNULL(RECON."TP", 0)
ELSE
(T1."Debit" + T1."Credit") - IFNULL(RECON."TP", 0)
END
ELSE
CASE
WHEN T1."Debit" > 0 THEN
((T1."Debit" * -1) + T1."Credit") + IFNULL(RECON."TP", 0)
ELSE
(T1."Debit" + T1."Credit") - IFNULL(RECON."TP", 0)
END
END AS "Saldo",
T1."TransType"
FROM OJDT T0
INNER JOIN JDT1 T1
ON T0."TransId" = T1."TransId"
INNER JOIN OCRD T2
ON T2."CardCode" = T1."ShortName"
INNER JOIN OACT T3
ON T3."AcctCode" = T1."Account"
LEFT JOIN (
. SCRIPT QUE REVISA TODAS LAS OPERACIONES DE RECONCILIACIONES POR DOCUMENTO
SELECT
I."TransId" AS "TID",
I."TransRowId" AS "TROW",
SUM(I."ReconSum") AS "TP"
FROM ITR1 I
INNER JOIN OITR O
ON O."ReconNum" = I."ReconNum"
WHERE O."ReconDate" <= :FECHA_CORTE
GROUP BY
I."TransId",
I."TransRowId"
ORDER BY
I."TransId" DESC
) AS RECON
ON RECON."TID" = T0."TransId"
AND T1."Line_ID" = RECON."TROW"
WHERE
T0."RefDate" <= :FECHA_CORTE
AND T3."LocManTran" = 'Y'
AND T1."TransType" <> -3
AND (T1."Debit" + T1."Credit") - IFNULL(RECON."TP", 0) <> 0;
Cualquier pregunta, responder aquí, el codigo cumple con todo sin necesidad de evaluar por documento