Information Systems:InfoNet Data Analyser
Data Analyser
We called it this because we planned on using ASW’s Analyser files. Then we found it was better to use the transaction files that Analyser is based on instead. However, the name stuck. So keep in mind this is not ASW Analyser.
Sales Order Fill Rates
There are two versions of fill rate analysis; one uses all orders and all picking for a particular day, and the other starts with all orders on a day and follows them right through to picking. (One difference between these is that orders received after afternoon cut off are typically picked the next day.)
Early every morning, as part of the End of Day, WEBPRDP/BLDDAYLINE is run to accumulate all order and picking data write to files WEBPRDD/DAYLINEP and DAYLIN2P. This program can be run for any day by calling it manually, with the date in the parameters, ie CALL BLDDAYLINE ‘20140801’.
This process only includes customer orders that are picked by the warehouse. Orders that will be picked at a future date (future orders, promos, and special orders) are handled differently than orders that will drop for picking right away. They will not be included in any totals based on the order date, but on the picked date.
As a regular order can be picked up to three days after it is ordered (ordered Friday evening, picked on a statutory holiday Monday), and orders under the customer’s daily order minimum can be held for up to four days, BLDDAYLINF is called, which will rebuild the previous 4 days.
Included Order Types
Orders that are available for picking when ordered –
DO – Detail Order EO – Electronic Order OR – Price Override SO – Sales Order (Manual) WO – Web Order
Returns –
RT – Return from Store (non-web) WR – Web Return from Store ** these are credits, and will subtract from all totals instead of adding
Future dated orders (added to all totals based on date picked)
EP – Electronic Promo Order FO – Future Order (Manual) PR – Promotional Order (Manual) SP – Special Order (Cat 900 only) WF – Web Future Order WP – Web Promo Order
In the text below, I will call these current orders (includes returns), and future orders.
- Sometimes electronic orders (EO), and very rarely Web Orders (WO) can act like future orders; when a customer is below their daily order minimum their orders will be held until they are over it. These orders will be held for up to 4 days, then cancelled.
Summary Data File (DAYLINEP)
Two sets of totals are calculated; one based on order date and the other on picked date. Except for future dated orders; which are included in both set of totals based on picked date. The values are broken down by warehouse / buyer / item account group.
DLDATE Date (YYYYMMDD) DLYYMM Year/Month (YYYYMM) DLFISC Fiscal Period DLYEAR Year DLDAYNUM Day number (from SROCLD) DLDAY Day of Week (Monday, Tuesday, etc) DLWHSE Warehouse DLRESP Buyer DLAGRP Item account group DLONHAND Onhand at cost
Sales Summary by Order Date
DLSALESO Sales Amount/Order Date DLSHPLNO Lines Shipped/Order Date DLSQTYO Quantity Shipped/Order Date
Sales Summary by Pick Date
DLSALESP Sales Amount/Pick Date DLSHPLNP Lines Shipped/Pick Date DLSQTYP Quantity Shipped/Pick Date
Order Summary
DLORDLN Lines Ordered (include cancelled lines and future orders)
DLORDCHG Quantity Changes
DLORDCAN1 Lines cancelled because invalid
DLORDCAN2 Lines cancelled because item is discontinued
DLORDCAN3 Lines cancelled because out of stock
DLORDCAN4 Lines cancelled by CPR (restricted or limited qty)
DLORDCAN5 Lines cancelled because special order or promo item
DLFUTURA Future Dated Lines Accepted
DLFUTURD Future Dated Lines Picked
DLACCLN Lines Accepted
DLORDERS Accepted Order Amount
DLNOTPCK Order lines that have been dropped for picking, but have not
yet been picked. If this does not clear after a few days, it
could mean that a current order line was accepted, but by the
time it was ready to drop for picking the stock was not available,
or that a customer is on credit hold.
Fill Rates by Order Date
DLFFILLO Fully Filled/Order Date DLPFILLO Partially Filled/Order Date DLZFILLO Zero Filled/Order Date DLCFILLO Lines that were accepted then cancelled
Fill Rates by Pick Date
DLFFILLP Fully Filled/Pick Date DLPFILLP Partially Filled/Pick Date DLZFILLP Zero Filled/Pick Date DLCFILLP Lines that were accepted then cancelled
Summary of all Order Lines by Order Source
DLWEB Web DLTRIRX TRI-RX DLTRIP TRI-POS DLARI ARI DLKROLL Kroll DLMANUAL Manual DLSIMP Simplicity DLTREX T-Rex DLPOSIT Positec DLEDISO EDI-SO
Summary of accepted Order Lines by Order Source
DLAWEB Web/Accepted DLATRIRX TRI-RX/Accepted DLATRIP TRI-POS/Accepted DLAARI ARI/Accepted DLAKROLL Kroll/Accepted DLAMANUAL Manual/Accepted DLASIMP Simplicity/Accepted DLATREX T-Rex/Accepted DLAPOSIT Positec/Accepted DLAEDISO EDI-SO/Accepted
Detail Data File (DAYLIN2P)
This file gives counts by reason code for order lines being cancelled or zero picked. The difference between the two, is that ‘zero picked’ is what we have some control over; out of stock, expired, can’t fine. ‘Cancelled’ is things that we can’t effect, like errors on the stores part (duplicate PO, bad item numbers).
D2DATE Date (YYYYMMDD)
D2YYMM Year/Month (YYYYMM)
D2FISC Fiscal Period
D2YEAR Year
D2DAYNUM Day number (from SROCLD)
D2DAY Day of Week (Monday, Tuesday, etc)
D2WHSE Warehouse
D2RESP Buyer
D2AGRP Item account group
D2TYPE Type – ‘CA’ for cancelled or ‘ZP’ for zero picked
D2SUBT Sub Type – ‘ORD’ for lines not accepted at ordering, or
‘PCK’ for cancelled or zero picked after order line was accepted
D2LSRN Lost sales reason code
D2COUNT Count of order lines
RPG program BLDDAYLINE
Files used –
SROISDPL – Invoice Lines SROSRO – Warehouse balances SROORSHE – Sales order header SROORSPL – Sales order line IOPHDRP – IOP order header IOPLINP – IOP order line
Totals are summarized by date / warehouse / buyer / item account group.
Total onhand
Read warehouse file and calculate total on hand value at average cost.
SQL – select srsrom, itresp, itagrp, sum(srsthq * srapco) from srosro, xxitemp where srprdc = itprdc and srsthq <> 0 group by srsrom, itresp, itagrp order by srsrom, itresp, itagrp
Result moved to DLONHAND.
Sales Summary by order date
Link invoice lines to the sales order header, and summarize amount and line count by debit/credit flag and order type. The invoice file includes the item account group, so only include records for inventory items (item account group starts with ‘I’), by order date (from the sales order header).
SQL – select idsrom, idresp, idpagr, idtypp, idordt, sum(idamou), count(*), sum(idqty) from sroisdpl, sroorshe where idorno = ohorno and ohoush = 'Y' and idpagr < 'I999' and ohodat = 20140702 group by idsrom, idresp, idpagr, idtypp, idordt order by idsrom, idresp, idpagr, idtypp, idordt
When idtypp (debit/credit flag) is ‘2’, reverse accumulators.
Current orders are added to DLSALESO, DLSHPLNO, AND DLSQTYO.
Future orders are not added here, as they are added based on date picked.
Sales summary by pick date
Read invoice file, and summarize amount and line count by debit/credit flag and order type. TThe invoice file includes the item account group, so only include records for inventory items (item account group starts with ‘I’), by invoice date (which is the date picked).
SQL – select idsrom, idresp, idpagr, idtypp, idordt, sum(idamou), count(*), sum(idqty) from sroisdpl where ididat = 20140702 and idpagr < 'I999' group by idsrom, idresp, idpagr, idtypp, idordt order by idsrom, idresp, idpagr, idtypp, idordt
When idtypp (debit/credit flag) is ‘2’, reverse accumulators.
Current and future orders are added to DLSALESP, DLSHPLNP, AND DLSQTYP.
Future orders are also added to sales by order date (DLSALESO, DLSHPLNO, DKSQTYO), future orders picked (DLFUTURD), and accepted orders (DLORDERS, DLACCLN).
Get accepted order lines
Link the sales order headers to sales order lines, and summarize the order value and line count by order type and handler.
SQL – select ohsrom, itresp, itagrp, ohordt, ohhand, sum(oloqts*olsalp), count(*) from sroorshe, sroorspl, xxitemp where ohorno = olorno and olprdc = itprdc and olprdc <> '02000040' and ohodat = 20140702 and ohoush = 'Y' group by ohsrom, itresp, itagrp, ohordt, ohhand order by ohsrom, itresp, itagrp, ohordt, ohhand
All orders add to DLORDLN.
All current orders add to DLORDERS and DLACCLN.
All future orders add to DLFUTURA.
WO, WP, WR, and WF add to DLWEB and DLAWEB.
EO and EP are accumulated by source –
EDI-ARI adds to DLAARI and DLARI. EDI-KROLL adds to DLAKROLL and DLKROLL. EDI-POSITE adds to DLAPOSIT and DLPOSIT. EDI-SIMPLI adds to DLASIMP and DLSIMP. EDI-TREX adds to DLATREX and DLTREX. EDI-TRIPOS adds to DLATRIP and DLTRIP. EDI-TRIRX adds to DLATRIRX and DLREIRX. Anything else (there shouldn’t be) adds to DLAEDISO and DLEDISO.
DO, FO, OR, PR, RA, RT, SO, and SP add to DLAMANUAL and DLMANUAL.
Get cancelled and changed order lines
Link the IOP header and line files together, and count number of lines per various error flags.
SQL – select itresp, itagrp, ihsrce, idlspo, idcpqt, idlsre, idstat, idpafi, idnofi, count(*) from iophdrp left outer join ioplinp on iophdrp.ihunin = ioplinp.ihunin left outer join xxitemp on iditem = itprdc where ihcryr = 14 and ihcrce = 20 and (idpafi = 'Y' or idaswo = 0) and ihcrmo = 07 and ihsrce <> 'MISCBIL' and ihcrdy = 02 group by itresp, itagrp, ihsrce, idlspo, idcpqt, idlsre, idstat, idpafi, idnofi order by itresp, itagrp, ihsrce, idlspo, idcpqt, idlsre, idstat, idpafi, idnofi
This will return counts of order lines that have either had the quantity changed, or have not posted to ASW.
If IDPAFI (partially filled) is ‘Y’, add lines to DLORDCHG.
Otherwise, add lines to DLORDLN, and by source (IDSRCE) add lines to the applicable summary of order lines field (ie DLARI, DLSIMP).
If IDPAFI (partially filled) is not ‘Y’, and IDCPQT (quantity changed by CPR) is ‘Y’ add lines to DLORDCAN4 (cancelled by CPR).
If IDPAFI (partially filled) is not ‘Y’, IDCPQT (quantity changed by CPR) is not ‘Y’, IDLSPO (posted to lost sales) is ‘Y’, and IDLSRE (lost sales reason code) is ‘S’ add lines to DLORDCAN5 (special order or promo item).
If IDPAFI (partially filled) is not ‘Y’, IDCPQT (quantity changed by CPR) is not ‘Y’, IDLSPO (posted to lost sales) is ‘Y’, and IDLSRE (lost sales reason code) is ‘A’ or ‘B’ add lines to DLORDCAN3 (out of stock).
If IDPAFI (partially filled) is not ‘Y’, IDCPQT (quantity changed by CPR) is not ‘Y’, IDLSPO (posted to lost sales) is ‘Y’, and IDLSRE (lost sales reason code) is ‘P’ or ‘R’ add lines to DLORDCAN4 (cancelled by CPR).
If IDPAFI (partially filled) is not ‘Y’, IDCPQT (quantity changed by CPR) is not ‘Y’, IDLSPO (posted to lost sales) is ‘Y’, and IDLSRE (lost sales reason code) is not ‘A’, ‘B’, ‘P’, or ‘R’ add lines to DLORDCAN2 (discontinued).
If IDPAFI (partially filled) is not ‘Y’, IDCPQT (quantity changed by CPR) is not ‘Y’, IDLSPO (posted to lost sales) is not ‘Y’, and IDSTAT (status code) is ‘50’ add lines to DLORDCAN5 (special order or promo item).
If IDPAFI (partially filled) is not ‘Y’, but record does not fit into any of the above categories, add lines to DLORDCAN4 (error).
Get fully filled by order date
Get count of lines where the sales order quantity equals the invoice quantity.
SQL – select idsrom, idresp, idpagr, idtypp, idordt, count(*) from sroisdpl, sroorspl, sroorshe where idorno = olorno and idolin = olline and idorno = ohorno and idqty = oloqts and ohodat = 20140702 and idpagr < 'I999' group by idsrom, idresp, idpagr, idtypp, idordt order by idsrom, idresp, idpagr, idtypp, idordt
When idtypp is ‘2’, subtract instead of adding.
Current orders are added to DLFFILLO. Future orders will be added based on picked date.
Get partially filled by order date
Get count of lines where the sales order quantity does not equal the invoice quantity.
SQL – select idsrom, idresp, idpagr, idtypp, idordt, count(*) from sroisdpl, sroorspl, sroorshe where idorno = olorno and idolin = olline and idorno = ohorno and idqty <> oloqts and ohodat = 20140702 and idpagr < 'I999' group by idsrom, idresp, idpagr, idtypp, idordt order by idsrom, idresp, idpagr, idtypp, idordt
When idtypp is ‘2’, subtract instead of adding.
Current orders are added to DLPFILLO. Future orders will be added based on picked date.
Get zero filled and cancelled by order date
Get lines from the lost sales file linked to the sales order line, and based on the sales order date.
SQL – select lssrom, itresp, lspagr, lsordt, lsplno, lslsrn, olcqts, olstat, olords from srolstsl, sroorspl, xxitemp where lsorno = olorno and lsline = olline and lsprdc = itprdc and olrddt = 20140702
Include current orders only; future orders will be added based on picked date.
Returns (LSORDT equals ‘RT’ or ‘WR’) subtract instead of add.
If OLCQTS (confirmed quantity from sales order line) is not zero, this line was a partial pick, which has already been counted, so ignore it here.
If LSLSRN (lost sales reason code) is ‘A’, ‘B’, ‘1’, ‘2’, ‘3’, or ‘5’ add to DLZFILLO (zero filled by order date. Otherwise add to DLCFILLO (cancelled after being accepted as order).
Get fully filled by picked date
Get count of lines where the sales order quantity equals the invoice quantity.
SQL – select idsrom, idresp, idpagr, idtypp, idordt, count(*) from sroisdpl, sroorspl where idorno = olorno and idolin = olline and idpagr < 'I999' and idqty = oloqts and ididat = 20140702 group by idsrom, idresp, idpagr, idtypp, idordt order by idsrom, idresp, idpagr, idtypp, idordt
When idtypp is ‘2’, subtract instead of adding.
Current and future orders are added to DLFFILLP. Future orders are also added to DLFFILLO.
Get partially filled by picked date
Get count of lines where the sales order quantity does not equal the invoice quantity.
SQL – select idsrom, idresp, idpagr, idtypp, idordt, count(*) from sroisdpl, sroorspl where idorno = olorno and idolin = olline and idpagr < 'I999' and idqty <> oloqts and ididat = 20140702 group by idsrom, idresp, idpagr, idtypp, idordt order by idsrom, idresp, idpagr, idtypp, idordt
When idtypp is ‘2’, subtract instead of adding.
Current and future orders are added to DLPFILLP. Future orders are also added to DLPFILLO.
Get zero filled and cancelled by picked date
Get lines from the lost sales file linked to the sales order line, and based on the lost sales date.
SQL – select lssrom, itresp, lspagr, lsordt, lsplno, lslsrn, olcqts, olstat, olords from srolstsl, sroorspl, xxitemp where lsorno = olorno and lsline = olline and lsprdc = itprdc and lslsdt = 20140702
Current and future orders are included.
Returns (LSORDT equals ‘RT’ or ‘WR’) subtract instead of add.
If OLCQTS (confirmed quantity from sales order line) is not zero, this line was a partial pick, which has already been counted, so ignore it here.
If LSLSRN (lost sales reason code) is ‘A’, ‘B’, ‘1’, ‘2’, ‘3’, or ‘5’ add to DLZFILLP (zero filled by order date. Otherwise add to DLCFILLP (cancelled after being accepted as order).
Get Current Orders Not Picked by Order Date
From the ASW sales order files, read active order lines that have not yet been picked but were ordered on the requested date.
SQL – select ohsrom, itresp, itagrp, ohordt, count(*) from sroorshe, sroorspl, xxitemp where ohorno = olorno and olprdc = itprdc and ohstat = ' ' and olstat = ' ' and olords <= 30 and ohodat = 20170702 group by ohsrom, itresp, itagrp, ohordt order by ohsrom, itresp, itagrp, ohordt
Returns (OHORDT equals ‘RT’ or ‘WR’) subtract instead of add.
Add lines for current orders to DLNOTPCK.
Get Future Order Not Picked by Expected Dispatch Date
From the ASW sales order files, read active order lines that have not yet been picked but had a dispatch date of the requested date.
SQL – select ohsrom, itresp, itagrp, ohordt, count(*) from sroorshe, sroorspl, xxitemp where ohorno = olorno and olprdc = itprdc and ohstat = ' ' and olstat = ' ' and olords <= 30 and oldelt = 20140702 group by ohsrom, itresp, itagrp, ohordt order by ohsrom, itresp, itagrp, ohordt
Add lines for future orders to DLNOTPCK.