287162
DHL C-TMS
Change to Invoicing on Outbound Scan
FUNCTIONAL SPECIFICATION - 10.6
27/05/2011 - 1.0
Reference: 287162 OB-8EZLWB
Functional Overview
Client Requirement
Change Request Summary:
Change To Invoicing on Outbound Scan. Suzanne York/Milton Keynes/UK/Exel
Change Request Details:
The business requires a change to only invoice for items with a 'Scan Out' code against them.As OMS_REFS are scanned out of the building, once all the items for that OMS_REF are scanned out (the scan out codes need to work either if they have been added to the order systemically or been added manually by someone [in order to catch items that were not scanned out but have actually been sent]) , the finance against the order should set to ACTUAL status, without the order having to be changed to a delivered status (currently happening when the trip status is set to confirmed).In order to ensure correct operational process, actualised finance should only be allowed to be applied to an invoice if the delivery trip status is at 'COMPLETED'.Obviously, as part of both the inbound and outbound changes, new OMS_REFS are created where applicable, with this change, all finance should be taken from the new OMS_REFS (if created). This will stop the requirement for ABORTED orders having to be rated before an invoicing run, as the newly created OMS_REF will hold the revenue. As a result of this change original OMS_REFS will only hold revenue it some or all of the items against them are bought in on plan. It is assumed however, that currently this is currently the case because of the set up of the suppliers in the system (that they are set to charge based on plan rather than actual despatch).Where a OMS_REF has some items against it that have been scanned in but not scanned out, only 2 scenarios could have happened. 1. the stock is still in the building, and 2. the stock has been loaded out without being scanned. In this case, the OMS_REF finance should not set to ACTUAL, until 1. the items that remain in the building are scanned out (which will create a new OMS_REF thus actualising both OMS_REFS as all the items against them both would have been scanned out), or 2. a 'MANL' code is applied by a user to the items that we know we have sent but have failed to scan.If an item is manually identified as loaded, the code against it will be 'MANL', and there will be no description. As part of this RIO, the business will also require an extract called 'Dunelm Manually Loaded Items Extract' which can be run by the management team here to be used to improve the operational performance. The extract should have a date range parameter, and display all items with a manually loaded code. Please see the attached design for this extract for more detail.
Benefits identified as a result of the change:
Better, more accurate production of Quicks and finance reports
Solution
Current Functionality
- Orders are rated based on planned quantities captured through supplier manifest confirmation.
- When a delivery trip is set to CONFIRMED, the orders on the trip are set to DELIVERED, actualising the payments.
- Based on revenue date (collection date), any relevant actualised payments are applied to invoices
Required Functionality
- Orders are rated based on scan outs from the X-dock.
- Once all the items on an order have been scanned out, the order payment is actualised
- Even though the order payment is actualised, it cannot be assigned to an invoice until the delivery trip is COMPLETED
Assumptions
- If an order is split at scan in or scan out, the original item identifier prefixed X will be considered excluded from the order when checking all items for the order have been successfully loaded.
- If an order has missing scan outs, the payment will never be actualised and invoiced
- One item not being scanned out will affect the payment of the whole order
- If part of an order is scanned out and the related delivery trip is set to COMPLETED, the order will not be invoiced as the payment is not ACTUALISED.
- Rating will be driven by the despatched quantity
Development
Currently ORDER payments are updated from FORECAST status to ACTUALISED when an order is set to DELIVERED. It is now required that there are two types of ACTUALISED status, actualised and able to be invoiced and actualised and not able to be invoiced.
Actualised and able to be invoiced is identified as all items on the order have been successfully scanned out and the delivery trip has been completed.
Actualised and not able to be invoiced is identified as all items on the order have been successfully scanned out and the delivery trip has not yet been completed.
To accommodate the two versions of Actualised payments, the system requires an additional status called ‘I’(Items scanned). ‘I’ will be defined as the status when all items on an order have been successfully scanned out. When a delivery trip is set to COMPLETED, the related order payments will be set to status ‘A’ only if they are currently status ‘I’.
Events
Currently the event of a trip being set to COMPLETED status in turn updates the order status to DELIVERED and the payment status to ‘A’.
A new event will be based on the final item on the order being scanned out, which will trigger a status change from’ F’ to ‘I’.
A daily batch job will run every morning automatically to process all FORECAST order payments. The number of items conforming to plan will be compared with the number of scan outs (SL, UL, OL, XL, MANL). If scans and items match, the payment status will be set to ‘I’
All ‘I’ payments will then be processed. If the delivery trip is set to ‘COMPLETED’, the payment status will be set to ‘A’, ready to be invoiced.
A button will be added to the Load Management screen to allow users to manually start the process. This can be completed just before an invoice run to ensure all payments are at the latest status.
| Event | Old status | New Status |
| All items have been scanned out | F | I |
| Delivery trip is set to Completed | F | F |
| Delivery trip is set to completed | I | A |
Extract
A new CSV extract will be created based on schedule from and to parameters OR revenue date from and to parameters. Item and order information will be returned for all items which have a ‘MANL’ reason code assigned. The extract will be available to run from the export screen.
Transition
A detailed transition plan will need to be agreed to ensure that suppliers are not double charged or charges missed.
Important Note to Analyst
Adding a new reason code means that an impact analysis across all the Dunelm specific reports and extracts is required to ensure MANL is report the same as the other loaded positive reason codes.
Scope
This change will be applied to system version 10.6.0 on DUNTST and once approved DUNPRD.
Set-up
Pre-requisites
None
Menu Structure
‘Unchanged’
Data
Insert a record into REP_REPORT to define the extract.
Insert 4 records into REP_REPORT_PARAMS to define the four parameters for the extract, SCHED_FROM, SCHED_TO, REVENUE_DATE_FROM, REVENUE_DATE_TO.
Create sequence SEQ_SCH_ORD_SCANS
MINVALUE 1
MAXVALUE 9999999999
START WITH 1
INCREMENT BY 1
CACHE 1;
CREATE PUBLIC SYNONYM SEQ_SCH_ORD_SCANS FOR SEQ_SCH_ORD_SCANS;
GRANT ALTER, SELECT ON SEQ_SCH_ORD_SCANS TO MTS_USER;
Functional Description
There are currently two payment statuses FORECAST and ACTUALISED. Payments are forecast until the TRIP they are related to is set to COMPLETED, when the ORDERS are set to DELIVERED and the payments are set to ACTUALISED. Once a payment is ACTUALISED, it is available to be included on an invoice.
A new status will be created which will sit between FORECAST and ACTUALISED. The new status I (ITEMS_SCANNED) will be set on ORDER CHARGE payments when all of the items on an order have been scanned out. Scanned out will be defined by five different REASON CODES –SU, UL, OL, XL, MANL.
Only payments which are at status I will be able to progress to ACTUALISED. While a payment is still FORECAST, it cannot be actualised, even if the associated trip is set to COMPLETED .
A new trigger will be added to the SCH_ORD_ITEMS_REASONS table, which will run on insert. A new control table will be created called ‘SCH_ORD_ITEMS_SCAN_CTRL’. The table will include the following fields
CTRL_ID
OMS_REF
PROCESSED
CREATED_DATE
CREATED_BY
When a record is inserted into the reasons table, a new record will be added to the control table in certain instances. If an unprocessed record for the same OMS_REF is already in the table, the new record will not be added. This will ensure that this is only ever one record waiting to be processed for each order and will avoid unnecessary duplication.
A database job will search for unprocessed records in the control table and call a new procedure from within the OMS package. The new procedure will be called PROCESS_SCANNED_ITEMS and will accept an OMS_REF as an input parameter.
The new procedure will count how many non ‘X’ items are held against the order. A second count will identify how many successful scan outs are held against the order. If the total of the counts match, meaning the order has the same number of items as successful scan outs, the status of the ‘ORD CHARGE’ payment held against the order will be updated to ‘I’.
If the delivery trip of the order is set to COMPLETED and the payments are set to a status of I, the payments will be actualised, set to A. However, if the payments are still Forecast when the delivery trip is set to COMPLETED, the payments will not be actualised.
The only event that actualises payments is the status of the delivery trip being set to COMPLETED. If the payments are not actualised at this event because all items have not been scanned, the payments would never be actualised.
To overcome this issue, a new daily batch job will run which will identify any payments set the status is I and the delivery trip status is completed and actualise the payment.
A new procedure will be created in the ACC package called ‘DAILY_ACTUALISE’.
The new procedure will select all ‘I’ status payments and link the payments back to the delivery trip, to find the status.
Any payments which are identified will be actualised.
A new button will be added to the Invoice screen which will allow users to run the both new procedures. Firstly the process to update forecast payments(F) to Items_Scanned (I) will run , then the process to update I payments to ACTUALISED (A) will run. It would be expected that a user will run this process from the Invoice Screen before generating the weekly invoices.
The new button will also be added to the bottom of the screen, with the other command buttons.
A system parameter will control if the button is displayed on the screen.
A new reason code MANL will be created to allow users to manually scan out an item , allowing order payments to be actualised.
A new CSV extract will be created to detail which orders have been assigned a MANL reason code. The extract will display order and item information as displayed in the following table:
| CUSTOMER | SCH_ORD |
| OMS_REF | SCH_ORD |
| SCHED_NAME | SCH_ORD |
| BOOKING_REF | SCH_ORD |
| EXTERNAL_REF | SCH_ORD |
| COLLECTION WINDOW | SCH_ORD |
| DELIVERY WINDOW | SCH_ORD |
| COLLECTION_TRIP | SCH_TRIP |
| DELIVERY TRIP | SCH_TRIP |
| ITEM_IDENTIFIER | SCH_ORD_ITEMS |
| PRODUCT | SCH_ORD_ITEMS |
| AKA_CODE | SCH_ORD_ITEMS |
| PLAN QTY | SCH_ORD_ITEMS |
| DESPATCH QTY | SCH_ORD_ITEMS |
| DELIVERY QTY | SCH_ORD_ITEMS |
| REASON CODE | SCH_ORD_ITEMS_REASONS (= ‘MANL’) |
| REASON COMMENT | SCH_ORD_ITEMS_REASONS |
| CREATED_DATE | SCH_ORD_ITEMS_REASONS |
The extract will be parameter based, allowing the users to enter schedules from and two OR revenue date from and to. The revenue date will be selected from the ACC_PAYMENT table based on the ‘ORD CHARGE’ record for the order.
New records will be added to the REP_REPORT and REP_REPORT_PARAM tables to allow the CSV extract to be run from the exports screen. The extract will be written in the standard CSV format of 2 procedures, one to select the data and the second to write the file to the required location. Both procedures will be added to the DP_CSV2 package.
Despatch Quantity
The orders will be rated by DESPATCHED quantity. The is the quantity that the contractual quantity will be based on. Currently despatched quantity is based on the actual_despatched totals held at order level. This will be changed , based on a system parameter to count the number of successful out bound scans for the order.
The changes will be made in the SCH_ORD trigger which sets the contractual quantity, T_SCH_ORD_PNL.
Changing the way DESPATCHED_QUANTITY is calculated witll ensure that quantities are not counted twice when orders are split more than once.
Table Updates Required
References
| EST-287162 OB-8EZLWB Change to Invoicing on Outbound Scan v1.1 | |||
Glossary
| C-TMS | Calidus TMS |
Document History
| Initial version | ||||
| Discussed & Issued | ||||
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
