285511

DHL MTS

Revised Invoicing

FUNCTIONAL SPECIFICATION - 10.6

01/02/2011 - 1.0
Reference: 285511 RB-8DHFSM

Client Requirement

To improve the information presented on the supplier invoices + additional revenue detail report. Change the invoice and invoice backing sheet

New revenue detail report

Revised Invoice Front Sheet

The following changes will be developed to improve the invoice front sheet;

1. DHL Supply Chain logo to be central in invoice header
2. The INVOICE DATE changes to be the date the invoice is confirmed. This means being the date the invoice status is set to APPROVED. Note that will the invoice is draft status, the heading will print DRAFT INVOICE DATE and the date printed will be the date the draft was generated. The VAT percentage and VAT calculation for the draft and for the invoice will be derived using this date (and not as now the date of the Sunday of the week ending that the invoice represents).
3. The DHL address will be printed in full – this is not a system change as the address is maintained in C-TMS static data – see location DHLMER and Cost Centre DHL
4. Change the label Account id:XXX99 to read Supplier Code: XXX99
5. The Fuel Charges line in the body of the invoice displayed as a calculation for each manifest will be removed. Instead, see point 10 below, the Fuel Surcharge will be shown as an invoice total at the end of the invoice.
6. The Payment Terms narrative to become Payment Terms: 1st…….(capital T in terms and space after full colon )
7. Add a box around the payments section (i.e. Payments To: / BANK / SORT CODE / ACCOUNT NO) and include new text ‘Please make your remittance to Exel Europe Ltd and forward to: [email protected] note that the e-mail address is retrieved from the location data in the same way as the E-Mail in the invoice header.
8. Include a box around ‘All Transactions..’ section and improve the punctuation with commas and spacing in address and colon after UK VAT No:
9. At the end of the invoice include a sub total of the manifest charges
10. Under the manifest sub total include a total fuel surcharge for the invoice
11. Then show sub total of manifest and fuel surcharge as ‘Total Net’

Then ‘Total VAT at XX.XX%’. Note that the percentage should be printed according to the invoice approval date as mentioned above

1. Then print the Total Incl VAT
2. Minimum collection charges will be displayed under the manifest charges as a new line of the invoice where applicable. The minimum collection will continue to be the difference from the total manifest charges for a planned collection trip stop to bring up to the system wide minimum collection value (currently set as £29)
3. As an example

27-JAN-11 Manifest 12345                5 CTNS                        21.50

Minimum Collection Charge                                         7.50

Invoice Backing Sheet

The invoice backing sheet will be revised to match the design example included above. The minimum collection and fuel surcharge flag columns will be removed. The column heading Media Type Planned will be replaced with Despatch Unit Type. (DHL to confirm whether in future a planned and actual delivered column will be required). Column heading Order to be changed to DLM PO No. Column heading Consignment to be changed to Consignment No.

Each row of the report will be a grouping of the DU type and rate applied within each consignment (C-TMS order). The revised layout has been designed to support multi-tiered charges; refer to RIO RB-8D9KNR. This means that a new table below ACC_PAYMENTS will be introduced to allow the tiered calculation break-down to be stored and then in turn reported on the revised Invoice Backing Sheet. The rating function will populate this new table if the customer is set for DU type charging. This new data structure will allow the backing sheet to report each PO consignment using the tiered rates, for example first CTN at rate 4.50, next 5 CTN at 3.50. This information will be presented as a separate line of the backing sheet for each rate.

Where minimum consignment charges apply, the rate column will show the minimum consignment rate for the supplier and not the DU rate from the contract.

Invoice Screen

The invoice screen will be enhanced to display the Invoice Date applied to each invoice as shown above.

Fuel Surcharge

The customer maintenance screen will be modified and a new data structure implemented to allow fuel surcharge percentages to be implemented with an effective date.

Invoicing will apply the fuel surcharge percentage in force at the date of the Sunday ending the operational week for which each invoice is generated. This single rate for the invoice will apply for all order charges, minimum consignment and minimum collection charges included.

Oracle Financials Interface

The interface mechanism that generates a flat text file of the invoices generated will be modified slightly to take into consideration the revised method of calculating fuel surcharge and VAT.

Additional Revenue Detail Report

A new extract will be developed to allow the required output specified in the attached above to be exported in CSV format.

The export will be run with parameters From/To Schedule; Supplier one or all; manifest no one or all.

The export will group very like the invoice backing sheet so grouping by DU type and rate applied within each consignment number.

Note the provided design does not include PO number which will be included as an additional column.

Note that minimum collection is not designed into the output so these charges will not be reported.

It is assumed from the design that the schedule of each order will be reported; this schedule is based on the in-store date of the invoice. (As a note to authors; because the invoicing is designed accumulate order charges based on collection date / collection manifest it might be better to provide the finance extracts based on collection date rather then schedule in store date).

Scope

This change will be applied to system version 10.6.0 on DUNTST and once approved DUNPRD.

Set-up

Pre-requisites

The revised multi-tiered rating development reference RB-8D9KNR. The revised rating development will generate rating audit transactions for each calculated rate tier on a new database table described below. These transactions will be referenced and reported in the revised backing sheet specified in this document.

Menu Structure

Unchanged.

Data

The following script will be utilised to create the new database table ORG_CUST_FUEL. This will allow effective dates to be maintained for fuel surcharge percentages on a customer by customer basis.

The following script will be utilised to create the new database table ACC_PAYMENT_LINES. This will store the tiered audit trail calculation used to build up the order change for each consignment.

An additional column will be added to the ACC_INVOICE table to store the total invoice calculated fuel surcharge amount and the tax point date for the invoice (will be printed as invoice date on the revised invoice front sheet).

An additional column will be added to the ACC_PAYMENT table to store a flag denoting whether the transaction is a minimum collection surcharge.

Functional Description

Fuel Surcharge Maintenance

A new table will be created to allow fuel surcharge to be stored with an effective date. When applying any surcharge the effective date will be used to determine which rate that should be used.

The triggers which generate the fuel surcharge payments are when an order is saved or changed. In this instance the system date will be compared with the effective date to determine the rate. The detailed fuel surcharge transactions will be considered memorandum entries until the draft invoice is created and then approved.

When an order charge is applied to an invoice, a new procedure Update Fuel Payment will be called, which will compare the effective date with the operational week ending date (the Sunday date ending the week) to determine the correct fuel surcharge. The payments will then be updated based on this fuel surcharge. This will ensure that the fuel surcharge applied on each invoice is based on the operational week being invoiced.

The fuel surcharge will no longer be displayed in detail on the invoices for each collection manifest but instead as an invoice total. The fuel surcharge on the Invoice will be calculated as the net amount (order charges and minimum collections) multiplied by the fuel surcharge for the operational week ending date and this value will be stored in a new field FUEL_AMOUNT which will be added to the ACC_INVOICE table .

In the INV package, the procedure UPDATE_TOTALS will be amended to exclude fuel surcharge records from the NET_AMOUNT and populate the new FUEL_AMOUNT field.

VAT

A new date field will be added to the invoice table, to hold the TAX_POINT_DATE. This will be populated with the date the draft invoice is created and then updated to the date the invoice is approved.

When the DRAFT invoice is created, by selecting the GENERATE command button, the new field TAX_POINT_DATE will be populated with the system date (today).

When setting the Invoice to APPROVED status, the new tax point field will be updated, again to the system date (today).

The Invoice Screen will be amended to display the new field in the footer.

The VAT rate applied will be based on the TAX_POINT_DATE. When the VAT is calculated as part of the UPDATE_TOTALS procedure, a call is made to the procedure ACC.GET_VAT_RATE, passing in the payment id and the revenue date. The call to the procedure will be amended to pass the payment id and NVL(acc_invoice.TAX_POINT_DATE, acc_payment.REVENUE_DATE).

If there is a TAX_POINT_DATE this will be used, if the payment is not on an invoice yet, the REVENUE_DATE will be used.

The new TAX_POINT_DATE date filed will be printed on the front sheet as the INVOICE DATE and the terms days will be used to calculate a due date accordingly. For all customers this is currently configured as the 1^st day of the next month.

Charge Details

A new database table will be created, sub-ordinate to the payments table which will list each tiered charge element as a record. The table will store the du type, quantity and rate which has been applied for each tier configured on the customer DU rate. The table will be joined to acc_payment by the payment_id, this being a unique system generated sequence number.

If the details of an order are changed, affecting the payment value (revenue), the payment record will be deleted and replaced. The deletion process will also remove the related sub-ordinate records from the tiered payments and recreate.

As charges (ACC_PAYMENTS) are calculated, the parent payment will not have been committed to the database. This means the function will read and reserve the next payment id value from the database sequence and use this to write the unique index for the new sub-ordinate table transactions and the payment record itself. The CREATE_PAYMENT function will be ‘over loaded’ to allow the payment id to be passed into the function. If the parameter is populated, then the code in create payment to get a payment id will not be run, if this parameter is null, the code will run.

The new table ACC_PAYMENT_LINES will be the basis of the amended backing sheet. A new field will be added to the acc_payment table called MIN_CONSIGNMENT. If a minimum consignment charge has been applied, this flag will be set to Y. The field will be displayed on the backing sheet to indicate that the rate was overwritten with the minimum consignment.

Invoice Report

Amendments1, 3,4,6,7,8,9,10,11 and 12 to the presentation of the Invoice sheet front page, as listed in the estimate will be developed. Points 2 and 5 are discussed in detail in the Fuel Charges and VAT sections above.

Oracle Financials

The OF_INT package will be amended to set the Invoice total of the oracle interface record to the NET_TOTAL plus the FUEL_AMOUNT. The change will be made in the F_CREATE_OF_FILE procedure. See the AS-Sales Record Interface NFCASL1 layout for more information.

Detailed Finance Extract

A new Finance Extract will be developed to output a CSV format file in the format on the attached example file in the solution section above. The extract will have 2 additional columns of data to that in the design example, the planned collection date as the first column and PO number column after manifest number column.

The runtime parameters will be – From / To Collection Date (planned); From / To schedule Date (in-store from SAP); Supplier all or one ; Manifest No; all or one, PO number all or one. At least one parameter must be used.  More than one can be used and the selection will be combined matches so ..and..and..

For ‘Quicks’, and to align with the invoicing, the from and to collection date will be the best method to run the extract as this should line up with invoicing week on week.

Minimum collection charges will be included – this means reporting to loc and to loc name, po number, consignment no, DU Type, qty and rate as blank values for these rows; the rate description will be printed as Minimum Collection and the Revenue and Fuel Charge associated to this revenue will be included; minimum consignment would be N  

The extract will not include the split consignments containing zero planned qty and therefore no revenue. The data will be sorted by collection date, then supplier, then manifest number, then PO number. The parameters provided will be printed in the header with the date and time created.

(Looking forward, the business should have some view on how both the invoicing and backing sheet and finance extracts will shift away from planned to actual.  This will not be trivial and needs some considered thought and planning.  The business should consider this now and define what the transition looks like, what is the enabler operationally and then in turn OBS can analyse the work effort beyond that defined in the current RIOs).

Table Updates Required

See section 2.3 above.

References

Ref No	Document Title & ID	Version	Date
	EST-285511 RB-8DHFSM Revised Invoicing & Revenue Detail Extract v1.0	1.0

Document History

Version	Date	Status	Reason	Initials
0.1		Draft	Initial version	SW
1.0	1/2/2011	Issue	Reviewed and Issued	DJM

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager