Information Systems:InfoNet Data Analyser - Revision history

Norwinu at 20:17, 22 June 2016

2016-06-22T20:17:30Z

New page

=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.

[[Category: Infonet]]

172.30.20.117: Created page with "=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 inst..."

2015-10-21T00:14:39Z

Created page with "=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 inst..."

New page