AR Customer level query for SAP B1 Hana version

[former_member813554]

Dear Experts,

I'm having issues with a couple of errors on a AR Query that should show only one line per Customer and the following columns:

- Customer Code

- Customer Name

- Customer Balance 0-30

- Customer Balance 30-60

- Customer Balance 60-90

- Customer Balance 90-120

No need to >120.

I have this thread, but it is still giving many errors: https://answers.sap.com/questions/13787559/ar-query-error.html

Your help would be greatly appreciated,

Marli Schutte

[former_member813554]

Experts,

Please see the query that works, EXCEPT for the fact that it is pulling ALL JE's.

I need it to pull only Open Balances (like the AR Report).

I thought that using the ITR1 table and hiding JE's with Reconciliation numbers, would work, BUT this also hides transactions that was partially reconciled (Invoice that was partially paid).

• What is the best way to get all Open Balances to show - is there a way from a JE? Or will I have to link the Originating Document and look at the Doc.Status?

SELECT 
T1."CardCode", T1."CardName", T1."Phone1", T1."Phone2", T1."Cellular", T1."E_Mail", T1."Balance", 
/*T0."Debit", 
T0."Credit", */
T0."DueDate",
'[%0]' AS "Selected AR Date", 
T0."Debit" - T0."Credit" AS "Amount",
DAYS_BETWEEN(T0."DueDate",'[%0]') AS "Days Due",

CASE 
WHEN 
DAYS_BETWEEN(T0."DueDate",'[%0]') < 0 
THEN 
T0."Debit" - T0."Credit"
ELSE 
0 
END
AS "Future",

CASE 
WHEN 
DAYS_BETWEEN(T0."DueDate",'[%0]') >= 0  AND
DAYS_BETWEEN(T0."DueDate",'[%0]') <= 30 
THEN 
T0."Debit" - T0."Credit"
ELSE 
0 
END
AS "Current",

CASE 
WHEN 
DAYS_BETWEEN(T0."DueDate",'[%0]') > 30  AND
DAYS_BETWEEN(T0."DueDate",'[%0]') <= 60 
THEN 
T0."Debit" - T0."Credit"
ELSE 
0 
END
AS "31-60 Days",

CASE 
WHEN 
DAYS_BETWEEN(T0."DueDate",'[%0]') > 60 AND
DAYS_BETWEEN(T0."DueDate",'[%0]') <= 90 
THEN 
T0."Debit" - T0."Credit"
ELSE 
0 
END
AS "61-90 Days",

CASE 
WHEN 
DAYS_BETWEEN(T0."DueDate",'[%0]') > 90 AND
DAYS_BETWEEN(T0."DueDate",'[%0]') <= 120 
THEN 
T0."Debit" - T0."Credit"
ELSE 
0 
END
AS "91-120 Days",

CASE 
WHEN 
DAYS_BETWEEN(T0."DueDate",'[%0]') > 120 
THEN 
T0."Debit" - T0."Credit"
ELSE 
0 
END
AS "Over 120 Days",

T1."CreditLine", T2."PymntGroup", T0."TransId", T0."BaseRef", T4."ReconNum" 
FROM 
JDT1 T0
INNER JOIN OCRD T1 ON T0."ShortName" = T1."CardCode" 
INNER JOIN OCTG T2 ON T1."GroupNum" = T2."GroupNum" 
INNER JOIN OJDT T3 ON T0."TransId" = T3."TransId" 
LEFT OUTER JOIN ITR1 T4 ON T3."TransId" = T4."TransId" 

WHERE 
/*T4."ReconNum" IS NULL AND*/
T0."ShortName" = 'C10017' AND
T1."CardType" = 'C' AND
T0."Debit" - T0."Credit" <> 0
 

ORDER BY T0."ShortName" ASC