The following query can used to create to create customer statement, i am still working on the layout
SELECT tA.customer AS customer_acc,
tC.customer_name AS name,
tA.address_line1 AS address_line1,
tA.address_line2 AS address_line2,
tA.city AS city,
tA.country AS country,
tC.default_currency AS curr,
tSI.name AS trx_no,
tSI.creation AS trx_date,
tSI.due_date AS due_date,
tSI.tc_name AS trx_type,
tSI.grand_total AS trx_amount,
tSI.outstanding_amount AS outs_amount
FROM ((tabCustomer tC
LEFT JOIN tabAddress tA
ON ((tC.customer_name = tA.customer_name)))
LEFT JOIN tabSales Invoice tSI
ON ((tA.customer = tSI.customer)))
WHERE (tSI.outstanding_amount IS NOT NULL)
UNION ALL
SELECT tA.customer AS customer_acc,
tC.customer_name AS name,
tA.address_line1 AS address_line1,
tA.address_line2 AS address_line2,
tA.city AS city,
tA.country AS country,
tC.default_currency AS curr,
tE.voucher_no AS trx_no,
tE.creation AS trx_date,
NULL AS due_date,
tE.voucher_type AS trx_type,
(CASE
WHEN (tE.credit <> 0) THEN (tE.credit * -(1))
ELSE tE.debit
END)
AS trx_amount,
(CASE
WHEN (tE.credit <> 0) THEN (tE.credit * -(1))
ELSE tE.debit
END)
AS outs_amount
FROM ((tabGL Entry tE
LEFT JOIN tabCustomer tC ON ((tE.party = tC.name)))
LEFT JOIN tabAddress tA
ON ((tC.customer_name = tA.customer_name)))
WHERE ((tE.party_type = âCustomerâ) AND isnull(tE.against_voucher));
For the Aging
SELECT VCS.customer_acc AS customer_acc,
VCS.name AS customer_name,
VCS.address_line1 AS address_line1,
VCS.address_line2 AS address_line2,
VCS.city AS city,
VCS.country AS country,
VCS.trx_no AS trx_no,
VCS.trx_date AS trx_date,
VCS.trx_type AS trx_type,
VCS.trx_amount AS trx_amount,
VCS.outs_amount AS outs_amount,
ifnull(
(CASE WHEN (VCS.outs_amount < 0) THEN VCS.outs_amount END),
0)
AS Unalloc,
ifnull(
(CASE WHEN (VCS.outs_amount >= 0) THEN VCS.outs_amount END),
0)
AS Func,
(year(curdate()) + month(curdate())) AS curr,
(year(VCS.trx_date) + month(VCS.trx_date)) AS trx,
( (year(curdate()) + month(curdate()))
- (year(VCS.trx_date) + month(VCS.trx_date)))
AS aging,
ifnull(
(CASE
WHEN ( (( (year(curdate()) + month(curdate()))
- (year(VCS.trx_date) + month(VCS.trx_date))) =
0)
AND (VCS.outs_amount >= 0))
THEN
VCS.outs_amount
END),
0)
AS current,
ifnull(
(CASE
WHEN ( (( (year(curdate()) + month(curdate()))
- (year(VCS.trx_date) + month(VCS.trx_date))) =
1)
AND (VCS.outs_amount >= 0))
THEN
VCS.outs_amount
END),
0)
AS age_30,
ifnull(
(CASE
WHEN ( (( (year(curdate()) + month(curdate()))
- (year(VCS.trx_date) + month(VCS.trx_date))) =
2)
AND (VCS.outs_amount >= 0))
THEN
VCS.outs_amount
END),
0)
AS age_60,
ifnull(
(CASE
WHEN ( (( (year(curdate()) + month(curdate()))
- (year(VCS.trx_date) + month(VCS.trx_date))) >=
3)
AND (VCS.outs_amount >= 0))
THEN
VCS.outs_amount
END),
0)
AS age_90abv
FROM VVL_Customer_Statement VCS;
