DHL Invoicing

DHL Invoicing

The invoicing is transactional so requires data extracted from each system.

DHL Invoicing – CTMS

Each server has a crontab entry to run the processing on the 1^st of the month. It runs at 0421.

#Entry for DHL monthly invoicing

21 4 1 * * /oraapp/util/sql/dhl_invoicing >> /tmp/dhl_invoicing

The script calls a database process called DP_DHL_INVOICING.RUN_ME

A file /oraapp/util/sql/orainv as reference, the script will run against each database name annotated as Y. Previously this used the standard oratab entries but has been amended as part of the DHL Migration Project.

The package code is in CVS and should be maintained through the standard code change procedures.

A system parameter called DHL_INV_EMAIL which contains the list of email addresses to send the output to. Multiple entries should be separated with a semi-colon.

The processing runs slightly different iterations of the queries depending on the system. The queries were split into groups based on the agreed costing models.

There is also a listing of users.

Users:

• List of users who logged in in the last month.

Group 1:

• Trips - count of non-deleted trips in the previous month
• Bookings - count of schedule bookings in the previous month
• Scheduled Orders - count of orders' Load activities in the previous month.

Group 2:

• Scheduled Orders - count of orders' Load activities in the previous month, split down by cost centre and planning group (depot).

Databases and Groups:

Database	Processes Run
aam	Users, Group 1, Group 2
bnl	Users, Group 1, Group 2
con	Users, Group 1, Group 2
dun	Users, Group 1, Capped Orders, Group 2
eur	Users, Group 1, Group 2
hcr	Users, Group 1, Group 2, Scheduled Low Volume Orders, Scheduled Standard Orders
ind	Users, Group 1, Group 2

Note:

• dun - includes a list of capped orders (order count is a minimum of 30 orders per trip, or the count of orders per trip, whichever is the larger).
• hcr - includes a split of low-volume orders (volume <= 0.20) and standard orders (any order not in that list above).

Each database will send an email with an attachment which contains tab separated data. It can be pasted directly into Excel.

DHL Invoicing – LOTS

There is a stored procedure in the MySQL database called dhl_invoicing that contains the SQL.

A bat file D:\LOTS\Invoicing\invoicing.bat runs the stored procedure and emails the file created. The file is then deleted (MySQL cannot overwrite a file).

The list of email addresses is hard coded but it is just a text file that can be edited as required.

The bat file runs from the Windows task scheduler on the first of the month at 0421