Edit Report:CUSTOMER BALANCES W/NOTES
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- CUSTOMER BALANCES W/NOTES
-- PLACE HOLDER REPORT DESCRIPTION
-- PLACE HOLDER REPORT FILTERS AND DRILL DOWN
-- ignore
SELECT
salesman.salesmancode,
salesman.salesmanname,
debtorsmaster.name,
debtorsmaster.debtorno,
currencies.currency,
currencies.decimalplaces,
(select terms from paymentterms inner join debtorsmaster on paymentterms.termsindicator = debtorsmaster.paymentterms where debtorno = debtortrans.debtorno) as terms,
debtorsmaster.creditlimit,
holdreasons.dissallowinvoices,
holdreasons.reasondescription,
(select group_concat(note) from custnotes where debtorno = debtorsmaster.debtorno) as NOTES,
round(SUM(
debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc
),2) AS balance,
CAST(
SUM(
CASE WHEN (paymentterms.daysbeforedue > 0) THEN CASE WHEN (
TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate)
) <= paymentterms.daysbeforedue THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END ELSE CASE WHEN TO_DAYS(Now()) - TO_DAYS(
CONCAT(
YEAR (
DATE_ADD(trandate, INTERVAL 1 MONTH)
),
'-',
LPAD(
MONTH (
DATE_ADD(trandate, INTERVAL 1 MONTH)
),
2,
'0'
),
'-',
LPAD(dayinfollowingmonth, 2, '0')
)
) <= 0 THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END END
) AS DECIMAL (10, 2)
) AS current,
CAST(
SUM(
CASE WHEN (paymentterms.daysbeforedue > 0) THEN CASE WHEN (
TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate)
) > paymentterms.daysbeforedue
AND (
TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate)
) < (paymentterms.daysbeforedue + 30) THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END ELSE CASE WHEN TO_DAYS(Now()) - TO_DAYS(
CONCAT(
YEAR (
DATE_ADD(trandate, INTERVAL 1 MONTH)
),
'-',
LPAD(
MONTH (
DATE_ADD(trandate, INTERVAL 1 MONTH)
),
2,
'0'
),
'-',
LPAD(dayinfollowingmonth, 2, '0')
)
) > 0
AND (
TO_DAYS(Now()) - TO_DAYS(
DATE_ADD(
DATE_ADD(
debtortrans.trandate, INTERVAL 1 MONTH
),
INTERVAL (
paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate)
) DAY
)
) < 30
) THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END END
) AS DECIMAL (10, 2)
) AS due,
CAST(
SUM(
CASE WHEN (paymentterms.daysbeforedue > 0) THEN CASE WHEN TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) > paymentterms.daysbeforedue
AND TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) >= (paymentterms.daysbeforedue + 30)
AND TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) < (paymentterms.daysbeforedue + 60) THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END ELSE CASE WHEN (
TO_DAYS(Now()) - TO_DAYS(
DATE_ADD(
DATE_ADD(
debtortrans.trandate, INTERVAL 1 MONTH
),
INTERVAL (
paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate)
) DAY
)
) >= 30
)
AND (
TO_DAYS(Now()) - TO_DAYS(
DATE_ADD(
DATE_ADD(
debtortrans.trandate, INTERVAL 1 MONTH
),
INTERVAL (
paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate)
) DAY
)
) < 60
) THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END END
) AS DECIMAL (10, 2)
) AS overdue1,
CAST(
SUM(
CASE WHEN (paymentterms.daysbeforedue > 0) THEN CASE WHEN TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) > paymentterms.daysbeforedue
AND TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) >= (paymentterms.daysbeforedue + 60) THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END ELSE CASE WHEN (
TO_DAYS(Now()) - TO_DAYS(
CONCAT(
YEAR (
DATE_ADD(trandate, INTERVAL 1 MONTH)
),
'-',
LPAD(
MONTH (
DATE_ADD(trandate, INTERVAL 1 MONTH)
),
2,
'0'
),
'-',
LPAD(dayinfollowingmonth, 2, '0')
)
) >= 60
) THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END END
) AS DECIMAL (10, 2)
) AS overdue2
FROM
debtorsmaster,
paymentterms,
holdreasons,
currencies,
debtortrans
join custbranch on debtortrans.debtorno = custbranch.debtorno and custbranch.branchcode = debtortrans.branchcode
join salesman on custbranch.salesman = salesman.salesmancode
WHERE
debtortrans.payterms = paymentterms.termsindicator
AND debtorsmaster.currcode = currencies.currabrev
AND debtorsmaster.holdreason = holdreasons.reasoncode
AND debtorsmaster.debtorno = debtortrans.debtorno
AND debtorsmaster.debtorno >= '0'
AND debtorsmaster.debtorno <= 'zzzzzz'
GROUP BY
debtorsmaster.debtorno,
currencies.currency,
debtorsmaster.creditlimit,
holdreasons.dissallowinvoices,
holdreasons.reasondescription
HAVING
ROUND(
SUM(
debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc
),
currencies.decimalplaces
) > 0
order by salesmancode asc, name asc