261548
261548-PA-7NGM2N Inc Fuel Surcharge in Base Cost Calculation
Version :
|
Copyright OBS Logistics © 2009
The information contained herein is the property of OBS Logistics and is supplied without liability for errors or omissions. No part may be reproduced or used except as authorised by contract or other written permission. The copyright and foregoing restriction on reproduction and use extend to all media in which the information may be embodied
Functional Overview
Client Requirement
- Add a Fuel Surcharge field in Customer details to allow input of a varying fuel surcharge percentage which will calculate a Fuel Surcharge charge in an Orders Revenue Summary based on the auto-rated base cost multiplied by the percentage - example attached
- Using the same process as above add 7 by Day Tick Boxes with associated % box to allow calculation of Premium Charges based on Day of the week and associated percentage values (see attachment) to show as a specific additional line in the Revenue Summary of an Order.Show these values in separate fields as part of Data Extract or CN Trip & Order
Detail Added by SS 25/03/09: It may be worth adding a surcharge tab to the customer form - this can then be controlled in future by access control for users within consumer who should not have access but also in other databases.
Solution
Fuel Surcharge
Create 2 new fields in the ORG_CUSTOMER table called FUEL_SURCHARGE (Y or N) and FUEL_SURCHARGE_AMT (Numeric).
A new table trigger will be added to the ACC_PAYMENT table which will fire every time a new record is inserted. If the record has a payment_type ORD_COST, the DEBIT_ACC will be checked for a FUEL_SURCHARGE. If this is active, another record will be inserted , the AMOUNT of the new record will be calculated based on the ord_cost AMOUNT * FUEL_SURCHARGE_AMT, no VAT will be applied.
The additional payment record will be displayed in the PAYMENTS screen and will be included in the TOTAL at the bottom of the screen. Within the ORDERS screen in the FINANCE tab, REVENUE should be displayed as the sum of ORD-CHARGE and FUEL-CHARGE. This screen will be updated to display the combined amount, Standard Cost will remain unchanged.
Premium Charges
Create 14 new fields in the ORG_CUSTOMER table labelled
SUN_ACTIVE (Y or N), SUN_CHARGE (numeric)
MON_ACT (Y or N), MON_CHARGE(numeric)
TUES_ACT (Y or N), TUES_CHARGE (numeric) etc for each day of the week.
As before , a table trigger will fire after an ORD_CHARGE record has been inserted. Based on the SCHED_DATE and the DEBIT_ACC, a PREMIUM_CHARGE record may be added into the table.
This will be displayed in the PAYMENTS screen and will be included in the TOTAL field at the bottom of the screen. This will again be included in the REVENUE_TOTAL displayed in the FINANCE tab of the ORDERS screen
Controlling Access to Customer Charges
A USER_GROUP will control if the Fuel and Premium Charge fields are update-able in the Customer screen . Using Access_Control a new GROUP will be created called ‘CUSTOMER_CHARGES’ and the relevant users will be added to this group.
A function will be created ‘Amend_Customer_Charges’. In the pre_query trigger on the form, the current user having access to the function will determine if the fields are display only.
CN Trip and Order Detail Extract
Two fields will be added to the end of the existing extract, Customer_Fuel_Charge and Premium_Charge. This extract is at trip stop level, while the payments are held at order level. As more than one stop can occur on the same order, the charges will be duplicated across each stop.
If there are 4 stops extracted from the same order, the premium and customer fuel charges will be displayed 4 times, against each stop. Order Costs and Revenue are currently duplicated for each stop.
Set-up
Data
New records will be created to store fuel charges and Premium charges. The records will be inserted into the ACC_PAYMENT table and will be created when a table trigger on ACC_PAYMENTS is fired. Within the table trigger will be criteria which will control when these records are created
Functional Description
The new Customer Surcharges tab on the Cost-Centre/Customer screen will be created to include fuel charge and premium charge information. Fuel charge information will include a text field to enter the fuel charge percentage and a check box to indicate that the fuel charge is active for the customer.
The premium charge information will consist of 21 new fields, 7 text fields to enter a premium surcharge percentage, 7 boxes to enter a fixed cost and 7 check boxes to indicate if the premium charge is relevant for the specific customer on each day.
If the user enters a percentage value for a specific day, the fixed cost field for that day will be made blank. Likewise, if a fixed cost field is populated for a specific day, the percentage field for that day will be set to blank. This will enable the payment records to be created automatically without confusion and will be controlled using the WHEN-VALIDATE_ITEM trigger on each of the 14 fields for percentage and fixed costs.
Access to these fields will be controlled using a new function and user group which will created and maintained using the Access Control screen. If a user has access to the new function ‘Amend_Customer_charges’ they will be able to change the values of the customer charge fields, if they do not have access to the new function, the customer charge fields will be displayed but will be disabled.
Every time an ORD CHARGE record is created, a trigger will run which will check if the DEBIT_ACC customer has any active charges. If fuel charges are active, a new record will be added into ACC_PAYMENT in the following format:
| PAYMENT_NO | SEQ_ACC_PAYMENT.nextval |
| PAYMENT_TYPE | FUEL-CHARGE |
| AMOUNT | Amount taken from ORD CHARGE record * by CUST_FUEL_CHARGE amount / 100 |
| CREATED_DATE | SYSTEM_DATE |
All other fields in the table will be populated with the data taken from the ORD CHARGE record.
The trigger will then convert the schedule date to a day of the week using the code:
select to_char(to_date(SCHED_NAME, 23.06.2008', 'dd.mm.yyyy'), 'Day'l;
To find the SCHED_NAME we must link the ACC_PAYMENT back to the sched_name on the ORDER record using ACC_PAYMENT.EVENT_REF = SCH_ORD.OMS_REF.
Once we have established the day of the week, we then check if premium charges are active for the DEBIT_ACC on that day. If they are, a new record will be added into ACC_PAYMENT in the following format
| PAYMENT_NO | SEQ_ACC_PAYMENT.nextval |
| PAYMENT_TYPE | PREMIUM-CHARGE |
| AMOUNT | Amount taken from (ORD CHARGE record * by CUST_PREM _CHARGE amount / 100) OR (FIXED_COST) |
| CREATED_DATE | SYSTEM_DATE |
All other fields in the table will be populated with the same data as the ORD CHARGE record.
The newly entered payment records will be available to view in the PAYMENTS screen and the amounts will be incorporated into the REVENUE field on the FINANCE tab of the ORDER screen.
The two new payment amounts will also be displayed in the CN Trip and Order Detail Extract. To add these fields to the extract we will amend the existing procedure CSV.WRITE_CN_TRIP_ORDER_DETAIL. We will create 2 new cursors, one for each charge and pass the cursors the current OMS_REF
The ORD_REVENUE amount in this extract will include any customer charges applied, in the same way that the forms reflect the charges in the REVENUE amounts.
Table updates Required
The following fields will be added to the ORG_CUSTOMER table
FUEL_SURCHARGE (Y or N) FUEL_SURCHARGE_AMT (Number) SUN_ACTIVE (Y or N), SUN_CHARGE (numeric) SUN_FIXED(numeric) MON_ACT (Y or N), MON_CHARGE(numeric) MON_FIXED(numeric) TUES_ACT (Y or N), TUES_CHARGE (numeric) TUES_FIXED(numeric) WED_ACTIVE (Y or N), WED_CHARGE (numeric) WED_FIXED(numeric) THURS_ACT (Y or N), THURS_CHARGE(numeric) THURS_FIXED(numeric) FRI_ACT (Y or N), FRI_CHARGE (numeric) FRI_FIXED(numeric) SAT_ACT (Y or N), SAT_CHARGE (numeric) SAT_FIXED(numeric)
Two new fields will be added to the ACC_PAYMENT_TYPE table for fuel charges and premium charges.
References
| 261548 PA-7NGM2N Inc Fuel SCharge in Base Cost calc |
Document History
| Initial version | ||||
| Reviewed and Issued |
Authorised By
| Matt Crisford | Development Manager | |
| Suk Sandhu | TMSCC MTS Product Manager |
