Dynamic Rates Management Module Research

Rates maintenance is a common requirement in today’s business applications. This solution allows systems to maintain rates in more dynamic approach where the administrative or the operational user could define rate formulas.
The solution is based on rate bases where a rate base could be any artifact that to be considered when defining a rate for a service or good. And these rate bases could be combined together in order to create dynamic rates. And at the time of combining rate bases additional information such as measuring unit, operating currency, taxes etc. could be defined. And the rate bases could be created, deleted and updated any time where the values are not hard-coded in table formats within the data store.
The approach could be elaborated using following example. Rate bases could consider as building blocks in a Lego system and the templates are built using the building blocks. And the template in the below example is a blue print of a house built out of selected building blocks.
Blank Flowchart - New Page(4)
Further each building block has its own attributes such as color, line thickness, dimensional view (2d, 3d). A rate base with specific attributes could be identify as a real artifact from the universe.
Blank Flowchart - New Page(2)
And the rate template in the above example is a blue print, not a real world artifact. Same example could be extended to below in order to express the real artifacts.
Blank Flowchart - New Page(5)
The implementation if this research could be elaborate in deep using the following example where it express the application of this solution for a vehicle parking system. Rate Bases within the vehicle parking system could be Parking Location, Vehicle Type, Parking Day etc. And each Rate Base will have its own attributes as below;
Blank Flowchart - New Page(7)
Once the rate bases are combined in to templates, they could be used to record operational information against the actual attribute combinations of the template as below;
Blank Flowchart - New Page(8)
As per this operational information could be recorded as in below example where the billing operation could be conducted at any given time including consumed services.
Blank Flowchart - New Page(9)
When there are multiple Rate Templates are being used, there will be more templates with different number of Rate Bases. Since the values are not hard-coded in to a specific data structure as table, modifications of the rate combinations or introduction of new rate combinations will not require any structural changes in the system.
Information of the Rate Templates could be used to generate customer specific quotations and this solution could be further extended such as to incorporate advance tax configurations.
The database design of the proposed system could be presented in a form of Entity Relationship Diagram as below;
rate
Further the role of each entity within the above design could be elaborated along with the entity and the functional descriptions.
  • Customer Group
Grouping of the customers are done in this table where the data could be usable in the event of generating reports, querying, bulk invoice generation etc.
CUSTOMER_GROUP
Field name data type length key is null default
cg_id int pk false
cg_name varchar 512 unique false
cg_is_on_workflow boolean false false
cg_close_by_workflow boolean false false
cg_description varchar 2048 true
cg_date_created date true current date
cg_time_created time true current time
cg_date_modified date true current date
cg_time_modified time true current time
cg_created_by varchar 256 true current user
cg_modified_by varchar 256 true current user
cg_status int true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Customer

Maintain details of each customer is maintained over this entity.

CUSTOMER

data type length key is null default
c_id int pk false
c_reg_number varchar 512 unique false
c_business_type varchar 1024 true
c_business_reg_number varchar 64 unique true
c_business_reg_copy_revised boolean true
c_is_credit_customer boolean true
c_is_credit_application_revised boolean true
c_company_name varchar 512 unique false
c_company_address varchar 1024 true
c_invoice_delivery_name varvhar 512 false
c_invoice_delivery_address varchar 1024 false
c_Quotation_delivery_name varchar 512 true
c_quotation_delivery_address varchar 1024 true
c_contact_number_1 varchar 16 true
c_contact_number_2 varchar 16 true
c_fax_number_1 varchar 16 true
c_fax_number_2 varchar 16 true
c_email_address varchar 512 true
c_web_site varchar 512 true
c_vat_reg_type varchar 4 false VAT
c_vat_svat_number varchar 32 true
c_contact_person varchar 512 true
c_is_on_workflow boolean false false
c_close_by_workflow boolean false false
c_description varchar 2048 true
c_date_created date true current date
c_time_created time true current time
c_date_modified date true current date
c_time_modified time true current time
c_created_by varchar 256 true current user
c_modified_by varchar 256 true current user
c_status int true 1
cg_id int fk false

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Currency Rate

Maintenance of the periodic updates of currency rates are maintained over this entity.

CURRENCY_RATE

field name data type length key is null default
cr_id int pk false
cr_currency_name varchar 512 unique / ck false
cr_currency_abbreviation varchar 8 false
cr_currency_rate double false
cr_currency_rate_date date false
cr_is_on_workflow boolean false false
cr_close_by_workflow boolean false false
cr_description varchar 2048 true
cr_date_created date true current date
cr_time_created time true current time
cr_date_modified date true current date
cr_time_modified time true current time
cr_created_by varchar 256 true current user
cr_modified_by varchar 256 true current user
cr_status                               int true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Unit Of Measure

Measuring units of the services are being maintained within this entity. E.g.:- Kg, cm, Each, Feet;

UNIT_OF_MEASURE

field name data type length key is null default
uom_id int pk false
uom_uom_title varchar 512 unique false
uom_abbreviation varchar 512 unique false
uom_is_on_workflow boolean false false
uom_close_by_workflow boolean false false
uom _description varchar 2048 true
uom _date_created date true current date
uom _time_created time true current time
uom _date_modified date true current date
uom _time_modified time true current time
uom _created_by varchar 256 true current user
uom _modified_by varchar 256 true current user
uom _status int true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Rate Base

Rate bases where the rates are to be defined for services or goods are maintained within this entity E.g.:- Day;

RATE_BASE

field name data type length key is null default
rb_id int pk false
rb_rate_base_name varchar 512 unique false
rb_rate_base_abbr_name varchar 16 unique false
rb_is_on_workflow boolean false false
rb_close_by_workflow boolean false false
rb _description varchar 2048 true
rb _date_created date true current date
rb _time_created time true current time
rb _date_modified date true current date
rb _time_modified time true current time
rb _created_by varchar 256 true current user
rb _modified_by varchar 256 true current user
rb _status int true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Rate Base Value

Values of each rate base are maintained within this entity E.g.:- Sunday, Monday, Holiday;

RATE_BASE_VALUE

field name data type length key is null default
rbv_id int pk false
rbv_rate_base_value varchar 512 ck / unique false
rb_id int
rbv_is_on_workflow boolean false false
rbv_close_by_workflow boolean false false
rbv_description varchar 2048 true
rbv _date_created date true current date
rbv_time_created time true current time
rbv_date_modified date true current date
rbv _time_modified time true current time
rbv_created_by varchar 256 true current user
rbv_modified_by varchar 256 true current user
rbv_status int true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Rate

This is the central entity which acts as the central hub between Rate Base, Rate Base Values and Rate – Rate Base mappings.

RATE

field name data type length key is null default
r_id int pk false
r_abbreviation varchar 512 unique false
r_rate_title varchar 512 false
r_is_on_workflow boolean false false
r_close_by_workflow boolean false false
r _description varchar 2048 true
r _date_created date true current date
r _time_created time true current time
r _date_modified date true current date
r _time_modified time true current time
r _created_by varchar 256 true current user
r _modified_by varchar 256 true current user
r _status int true 1

Functions:- This will be a background process where the creation will commit upon mapping of Rate Bases and Values in to Templates.

Algorithms:- N/A

Security:- Logged user security scope

Validation:- N/A

  • Rate Rate Base

This will be maintaining mapping between Rate and Rate Bases.

RATE__RATE_BASE

field name data type length key is null default
rrb_id int pk false
r_id int ck / unique false
rb_id int
rrb_is_on_workflow boolean false false
rrb_close_by_workflow boolean false false
rrb _description varchar 2048 true
rrb _date_created date true current date
rrb _time_created time true current time
rrb _date_modified date true current date
rrb _time_modified time true current time
rrb _created_by varchar 256 true current user
rrb _modified_by varchar 256 true current user
rrb _status int true 1

Functions:- This will be a background process where the creation will commit upon mapping of Rate Bases and Values in to Templates.

Algorithms:- N/A

Security:- Logged user security scope

Validation:- N/A

  • Order Type

Different order types could be maintained for the purpose of grouping and it will be convenient at the time of generating bills.

ORDER_TYPE

field name data type length key is null default
ot_id int pk false
ot_order_type_name varchar 512 unique false
ot_is_on_workflow boolean false false
ot_close_by_workflow boolean false false
ot_description varchar 2048 true
ot_date_created date true current date
ot_time_created time true current time
ot_date_modified date true current date
ot_time_modified time true current time
ot_created_by varchar 256 true current user
ot_modified_by varchar 256 true current user
ot_status int true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Customer Rate Profile

This entity will be maintaining customer specific rates defined using the pre-created rate templates. And the Rate-RateBase value mapping will be isolated with Rate Rate Base Value entity.

CUSTOMER_RATE_PROFILE

field name data type length key is null default
crp_id int pk false
crp_rate_value double false 0
crp_is_on_workflow boolean false false
crp_close_by_workflow boolean false false
crp_description varchar 2048 true
crp_date_created date true current date
crp_time_created time true current time
crp_date_modified date true current date
crp_time_modified time true current time
crp_created_by varchar 256 true current user
crp_modified_by varchar 256 true current user
crp _status int true 1
c_id int fk false
r_id int fk false
ot_id int fk false
cr_id int fk false
uom_id int fk false

Functions:- CRUD, upon creation Rate – Rate Base Value entity will be updated in background.

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Rate Rate Base Value

This will be maintaining customer specific rate base value configurations.

RATE__RATE_BASE_VALUE

field name data type length key is null default
rrbv_id int pk false
crp_id int ck / unique false
rbv_id int
r_id int
rrbv_is_on_workflow boolean false false
rrbv_close_by_workflow boolean false false
rrbv _description varchar 2048 true
rrbv _date_created date true current date
rrbv _time_created time true current time
rrbv _date_modified date true current date
rrbv _time_modified time true current time
rrbv _created_by varchar 256 true current user
rrbv _modified_by varchar 256 true current user
rrbv _status int true 1

Functions:- This will be a background process where the creation will commit upon committing of the Customer Rate Profile.

Algorithms:- N/A

Security:- Logged user security scope

Validation:- N/A

  • Quotation Template

This entity will be maintaining customer specific or generic templates of quotations where they could be re-used.

QUOTATION_TEMPLATE

field name data type length key is null default
qt_id int pk false
qt_salutation varchar 512 unique false
qt_first_ paragraph varchar 2048 true
qt_second_paragraph varchar 4096 true
qt_third_paragraph varchar 4096 true
qt_optional_paragraph varchar 4096 true
qt_helpdesk_contacts varchar 2048 true
qt_conclusion_clause varchar 2048 true
qt_validity_paragraph varchar 4096 true
qt_detention_paragraph varchar 4096 true
qt_insurance_paragraph varchar 4096 true
qt_payments_paragraph varchar 4096 true
qt_weight_restrictions varchar 1024 true
qt_other_details varchar 4096 true
qt_is_on_workflow boolean false false
qt_close_by_workflow boolean false false
qt_description varchar 2048 true
qt_date_created date true current date
qt_time_created time true current time
qt_date_modified date true current date
qt_time_modified time true current time
qt_created_by varchar 256 true current user
qt_modified_by varchar 256 true current user
qt_status int true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Quotation

Maintenance of Quotations generated for customers will be maintained within this entity.

QUOTATION

field name data type length key is null default
q_id int pk false
q_quotation_number varchar 9 unique false 112365001

(1yydddxxx)

q_valid_days int false 0
q_extra_free_waiting_hours double false 0.0
q_additional_waiting_hour_fee double false 0.0
q_is_approved boolean false false
q_is_on_workflow boolean false false
q_close_by_workflow boolean false false
q_description varchar 2048 true
q_date_created date true current date
q_time_created time true current time
q_date_modified date true current date
q_time_modified time true current time
q_created_by varchar 256 true current user
q_modified_by varchar 256 true current user
q_status int true 1
c_id int fk false
qt_id int fk false

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Quotation Line

Quotation lines will represent the Rate Profiles created for customers. And those information will be useful in the phases of operations and invoicing.

QUOTATION_LINE

field name data type length key is null default
ql_id int pk false
ql_is_on_workflow boolean false false
ql_close_by_workflow boolean false false
ql_description varchar 2048 true
ql_is_extra_charge boolean false false
ql_date_created date true current date
ql_time_created time true current time
ql_date_modified date true current date
ql_time_modified time true current time
ql_created_by varchar 256 true current user
ql_modified_by varchar 256 true current user
ql_status int true 1
crp_id int fk false
q_id int fk false

Functions:- This will be a background process where the creation will commit upon committing of the Quotation. Or lines of the quotation could be handled as separate CRUD operation.

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Order Job Status

Status of the Jobs conducted from the Customer Rate Profile will be maintained in mater level.

ORDER_JOB_STATUS

field name data type length key is null default
ojs_id int pk false
ojs_status_title varchar 512 unique false
ojs_display_on_dropdowns int false 1
ojs_is_on_workflow boolean false false
ojs_close_by_workflow boolean false false
ojs _description varchar 2048 true
ojs _date_created date true current date
ojs _time_created time true current time
ojs _date_modified date true current date
ojs _time_modified time true current time
ojs _created_by varchar 256 true current user
ojs _modified_by varchar 256 true current user
ojs _status int true 1

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Order Job Status Order Type

Mapping of Job status / steps to each Order Type is maintained on this entity.

ORDER_JOB_STATUS__ORDER_TYPE

field name data type length key is null default
ojsot_id int pk false
ojsot_is_on_workflow boolean false false
ojsot_close_by_workflow boolean false false
ojsot_description varchar 2048 true
ojsot _date_created date true current date
ojsot _time_created time true current time
ojsot _date_modified date true current date
ojsot _time_modified time true current time
ojsot _created_by varchar 256 true current user
ojsot _modified_by varchar 256 true current user
ojsot _status int true 1
ojs_id int unique / ck false
ot_id int

Functions:- Basic CRUD

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Order

This entity will be maintaining customer orders created out of respective quotations. Operational details will be recorded against the created orders.

ORDER

field name data type length key is null default
o_id int pk false
o_order_number varhcar unique false 512365001

(5yydddxxx)

o_order_date date false
o_deadline_date date true
o_deadline_time time true
o_is_completed boolean false false
o_completed_date date true
o_is_started boolean false false
o_started_date date true
o_is_invoiced boolean false false
o_is_jobs_created boolean false false
o_is_on_workflow boolean false false
o_close_by_workflow boolean false false
o_description varchar 2048 true
o_date_created date true current date
o_time_created time true current time
o_date_modified date true current date
o_time_modified time true current time
o_created_by varchar 256 true current user
o_modified_by varchar 256 true current user
o_status int true 1
q_id int fk false

Functions:- CRUD Operation

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Order Extra Charges

Ad-Hoc expenses / charges which are not identified in advance, are to be recorded on this entity.

ORDER_EXTRA_CHARGE

field name data type length key is null default
oec_id int pk false
oec_qty int false 0
oec_from_date date true null
oec_to_date date true null
oec_is_on_workflow boolean false false
oec_close_by_workflow boolean false false
oec_description varchar 2048 true
oec_date_created date true current date
oec_time_created time true current time
oec_date_modified date true current date
oec_time_modified time true current time
oec_created_by varchar 256 true current user
oec_modified_by varchar 256 true current user
oec_status int true 1
o_id int fk false
ql_id int fk false

Functions:- CRUD Operation

Algorithms:- N/A

Security:- Logged user security scope

Validation:- Common validations

  • Order Job

Multiple jobs are could be conducted over a single order. And each job details are maintained over this entity.

ORDER_JOB

field name data type length key is null default
oj_id int pk false
o_id int
oj_number int false
oj_is_completed boolean false false
oj_is_aborted boolean false false
oj_is_on_workflow boolean false false
oj_close_by_workflow boolean false false
oj _description varchar 2048 true
oj _date_created date true current date
oj _time_created time true current time
oj _date_modified date true current date
oj _time_modified time true current time
oj _created_by varchar 256 true current user
oj _modified_by varchar 256 true current user
oj _status int true 1

Functions:- This will be a background process where the creation will commit upon committing of the Order with the required numbers of jobs. Or this could maintain as a direct CRUD operation based on the requirement.

Algorithms:- N/A

Security:- Logged user security scope

Validation:- N/A

  • Order Job Status Audit Trail

Status tracking of each job is maintained on this entity where it will be an audit trail of each job.

ORD_JOB_ST

field name data type length key is null default
ojs_id int pk false
ojsot_id int unique / ck false
oj_id int
ojs_date int false 0
ojs _is_on_workflow boolean false false
ojs _close_by_workflow boolean false false
ojs _description varchar 2048 true
ojs _date_created date true current date
ojs _time_created time true current time
ojs _date_modified date true current date
ojs _time_modified time true current time
ojs _created_by varchar 256 true current user
ojs _modified_by varchar 256 true current user
ojs _status int true 1

Functions:- This will be a background process where the creation will commit upon committing of the respective Order Status changes.

Algorithms:- N/A

Security:- Logged user security scope

Validation:- N/A

Specific modifications required based on business requirement could be modified over the above solution design. And the dynamic rates will not make any impact on the system structure as per the expected outcome of this research.

~ By Thisara

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s