Customer Outstanding

[Former Member]

Hi,

Can you help me to generate a query report like this,

A Report for selected one customer for his outstanding balance showing only pending A/R invoices,A/R Credit memo,Incoming payment & Journal vouchers. (Not internal reconciled or Document status is open documents)   

Parameters-1-customer name 2-from date 3-to date

report Headers,

Document No----Posting date---sales employee---Document total---Document pending Value---Cumulative Value

I tried to get this report with General ledger but i wasn't success.

Thank You

saman

[Rafaee_Mustafa]

Hi,

The issue is If you go with the JDT it wont show you the exact amount of back dates as it updates the field as balance received so you cant go with the BalDue__ form JDT

The best is you use Customer Aging report

or extract data from oinv  UNION orin UNION orct UNION ojdt (filter with Series or Type)

Feel free for any help

Regards,

Rafaee

[Former Member]

Hi Rafaee

It is not giving results with Customer Aging report for my requirement.

saman

[Rafaee_Mustafa]

Hi Saman,

Then with the query there is always a issue of in consistency. If you go with the JDT it updates the balance as it receive and you cannot get the right balance of back dates hence it will Perfectly tell you the current situation.

Wats your requirement which is not fulfilling with that ?

[Former Member]

Hi

thank you for reply. it does not give the pending documents . it is appear all of documents.

other one is there are limited details with aging reports. i want pending documents with doc no, posting date, sales employee,amount, pending balance ect.

saman 

[Rafaee_Mustafa]

If you Uncheck the Display Reconciled Transactions (bottom of the parameter window) it will shows the pending Docs only

Your SalesEmployee and Amount is not there

[KennedyT21]

HI Saman,

Try This

SELECT T0.[TransId],T0.[RefDate], T0.[TransType] AS 'Origin Doc.Type', T1.[BaseRef] AS 'Origin Doc.No', T1.[ShortName], T2.[CardName],

T1.[Account], T1.[ContraAct], T1.[CreatedBy], T1.[Debit], T1.[Credit], T1.[BalDueDeb], T1.[BalDueCred], T1.[LineMemo]

FROM dbo.OJDT T0 

INNER JOIN dbo.JDT1 T1 ON T0.TransId = T1.TransId

INNER JOIN dbo.OCRD T2 on T2.CardCode = T1.[ShortName]

WHERE   T2.[CardName] LIKE '[%0]%'

Regards

Kennedy

[Former Member]

Hi Kennedy

i tried above query early . it is already in forum, but it wasn't help me

saman

[KennedyT21]

HI saman

/* select * from oinv t1 */

DECLARE @D1 DATETIME

DECLARE @D2 DATETIME

DECLARE @D3 nvarchar(100)

SET @D1=/* t1.DocDATE*/ '[%0]'

SET @D2=/* t1.DocDATE*/ '[%1]'

set @d3 =/* t1.cardname */ '[%2]'

SELECT  T0.BalDueDeb , T0.BalDueCred,

T1.CardCode AS 'Cust Num',

T1.CardName AS 'Cust Name',

T0.SysDeb AS 'Debit Amt',

T0.SysCred * -1 AS 'Credit Amt',

CASE

WHEN T0.TransType = 13 THEN 'AR Invoice'

WHEN T0.TransType = 14 THEN 'AR Cred Memo'

WHEN T0.TransType = 24 THEN 'Payment'

ELSE 'Other'

END AS 'Trans Type',

T0.Ref1 AS 'Reference'

FROM JDT1 T0

INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode  AND T1.CardType = 'C'

WHERE t1.CardName= @d3 and (t0.DueDate between @d1 and @d2) and

T0.IntrnMatch = '0' and (T0.BalDueDeb) +( T0.BalDueCred)<>'0'

ORDER BY T1.CardCode,T0.TaxDate

Regards

Kennedy

[Former Member]

Hi,

Try this with modification...

SELECT

T1.SlpName AS 'Salesperson',
--T0.SlpCode AS 'SP Code',
COUNT(T0.DocNum) AS 'Total Count',
'Credit Memos' AS 'Doc Type',
SUM ((T0.DocTotal) * -1) AS 'Total Docs',
SUM (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) AS 'Product Sales',
SUM ((T0.GrosProfit) * -1) AS 'Gross Profit',

CASE
WHEN (SUM (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1)) = 0.00 THEN 0.00
ELSE (SUM ((T0.GrosProfit) * -1)/SUM (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1)) * 100
END AS 'Gross Profit %',

SUM ((T0.PaidToDate) * -1) AS 'Paid To Date',
SUM ((T0.DocTotal - T0.PaidToDate) *-1) AS 'Open on Docs'

FROM ORIN T0

INNER JOIN OSLP T1
ON T0.SlpCode = T1.SlpCode

WHERE

(T1.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ')
AND T0.TaxDate >= '[%1]'
AND T0.TaxDate <= '[%2]'

GROUP BY T0.SlpCode, T1.SlpName

UNION

SELECT

T1.SlpName AS 'Salesperson',
--T0.SlpCode AS 'SP Code',
COUNT(T0.DocNum) AS 'Total Count',
'Invoices' AS 'Doc Type',
SUM (T0.DocTotal) AS 'Total Docs',
SUM ((T0.DocTotal - T0.VatSum) - T0.TotalExpns) AS 'Product Sales',
SUM (T0.GrosProfit) AS 'Gross Profit',

CASE
WHEN (SUM ((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) = 0.00 THEN 0.00
ELSE (SUM (T0.GrosProfit)/SUM ((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) * 100
END AS 'Gross Profit %',

SUM (T0.PaidToDate) AS 'Paid To Date',
SUM (T0.DocTotal - T0.PaidToDate) AS 'Open on Docs'

FROM OINV T0

INNER JOIN OSLP T1
ON T0.SlpCode = T1.SlpCode

WHERE

(T1.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ')
AND T0.TaxDate >= '[%1]'
AND T0.TaxDate <= '[%2]'

GROUP BY T0.SlpCode, T1.SlpName

Regards,

Sudhir B

[Former Member]

Hi sudhir,

in your reply parameter should be for customer name not for sales employee.

there is no document number in report

can you modify it with above fields & according to my requirement fields.

saman