How can we store and use money variables in a custom field?
Environment:
Custom Fields, Data Types
Oracle B2C Service Versions 20C and earlier
Resolution:
The Decimal Custom Attribute Field Type has been added to versions 20D and later. For details on this feature, see the What's New document for Oracle B2C Service 20D.
In versions prior to 20D, to store money data which can use decimal values, you have to select text data-type for that specific custom field.
Now when using these values in report and manipulating them with math functions, they need to be converted to number type first. The c$custom_field should have at least two digits after the decimal point. This can be done using a formula as below within reports:
to_number(incidents.c$custom_field) + to_number(substr(incidents.c$custom_field, instr(incidents.c$custom_field, '.') + 1))/100
Where c$custom_field for incidents table holds the money data (up to two decimals) with type text.
Use the following formula to handle any number of digits after the decimal point.
to_number(incidents.c$custom_field) + to_number(substr(incidents.c$custom_field, instr(incidents.c$custom_field, '.') + 1))/power(10, length(substr(incidents.c$custom_field, instr(incidents.c$custom_field, '.') + 1)))
For additional information, refer to the 'Functions' section in the Online Help User Guide documentation. To access Oracle B2C Service manuals and documentation online, refer to the Documentation for Oracle B2C Service Products.
