- Formatting Amount in Words to Lakhs:
- Implemented logic to convert the amount into words in lakhs format for larger amounts.
- This was accomplished through a NetSuite formula in the default value by adding a logic expression.
Logic Expression:
Implemented logic to convert the amount into words in lakhs format for larger amounts.
CASE
WHEN {total} = 0 THEN ‘ZERO’
ELSE
(CASE
WHEN TRUNC({total} / 10000000, 0) > 0 THEN
TO_CHAR(TO_DATE(TRUNC({total} / 10000000, 0), ‘J’), ‘JSP’) || ‘ CRORE ‘ ||
(CASE
WHEN MOD(TRUNC({total} / 100000, 0), 100) > 0 THEN
TO_CHAR(TO_DATE(TRUNC(MOD(TRUNC({total} / 100000, 0), 100), 0), ‘J’), ‘JSP’) || ‘ LAKH ‘
ELSE ”
END) ||
(CASE
WHEN MOD(TRUNC({total} / 1000, 0), 100) > 0 THEN
TO_CHAR(TO_DATE(TRUNC(MOD(TRUNC({total} / 1000, 0), 100), 0), ‘J’), ‘JSP’) || ‘ THOUSAND ‘
ELSE ”
END) ||
(CASE
WHEN MOD(TRUNC({total}), 1000) > 0 THEN
TO_CHAR(TO_DATE(TRUNC(MOD(TRUNC({total}), 1000), 0), ‘J’), ‘JSP’) || ‘ ‘
ELSE ”
END)
WHEN TRUNC({total} / 100000, 0) > 0 THEN
TO_CHAR(TO_DATE(TRUNC({total} / 100000, 0), ‘J’), ‘JSP’) || ‘ LAKH ‘ ||
(CASE
WHEN MOD(TRUNC({total} / 1000, 0), 100) > 0 THEN
TO_CHAR(TO_DATE(TRUNC(MOD(TRUNC({total} / 1000, 0), 100), 0), ‘J’), ‘JSP’) || ‘ THOUSAND ‘
ELSE ”
END) ||
(CASE
WHEN MOD(TRUNC({total}), 1000) > 0 THEN
TO_CHAR(TO_DATE(TRUNC(MOD(TRUNC({total}), 1000), 0), ‘J’), ‘JSP’) || ‘ ‘
ELSE ”
END)
ELSE
(CASE
WHEN MOD(TRUNC({total} / 1000, 0), 100) > 0 THEN
TO_CHAR(TO_DATE(TRUNC(MOD(TRUNC({total} / 1000, 0), 100), 0), ‘J’), ‘JSP’) || ‘ THOUSAND ‘
ELSE ”
END) ||
(CASE
WHEN MOD(TRUNC({total}), 1000) > 0 THEN
TO_CHAR(TO_DATE(TRUNC(MOD(TRUNC({total}), 1000), 0), ‘J’), ‘JSP’) || ‘ ‘
ELSE ”
END)
END) ||
(CASE
WHEN ({total}-TRUNC({total}, 0)) > 0 THEN
‘ AND ‘ ||
(CASE
WHEN LENGTH(TO_CHAR(REGEXP_REPLACE(TO_CHAR({total}), ‘^[0-9]+.’, ”))) = 1 THEN
TO_CHAR(TO_DATE(TO_CHAR(TRUNC(REGEXP_REPLACE(TO_CHAR({total}), ‘^[0-9]+.’, ”), 0)*10),’J’),’JSP’) || ‘ PAISE ONLY ‘
ELSE
TO_CHAR(TO_DATE(TO_CHAR(TRUNC(REGEXP_REPLACE(TO_CHAR({total}), ‘^[0-9]+.’, ”), 0)),’J’),’JSP’) || ‘ PAISE ONLY ‘
END)
ELSE
”
END) || ‘ ‘
END