order/shipment query for the experts

[blom0344]

Our warehouse database contains an order_header table and an order_line table (1 to n).
Joins are on order/shipment number and type (S51 = order , S01 = shipment)
An ordernumber is unique in the header , for instance B123456. As soon as a shipment is established one or more (partial) shipmentnumbers are created in header:
B123456-1
B123456-2 etc.
Each shipmentnumber has 1 or more lines in the line table. The lines contain productcode and quantity. The products and quantities from the order-lines are the subject of the output of the query.
Query should produce the sum of the quantities per productcode for orders of a certain status. However if there exists a corresponding shipment of a certain status the order it relates to should not be taken into account when aggregating the quantities.

Problem 1: Orders and shipments are stored in the same tables, only the field ‘type’ and the prefix after the ordernumber indicates that it concerns a shipment.
Problem 2: There is no field in header table that contains the ordernumber (7 positions) for BOTH orders AND shipments, making an autojoin with header impossible.
(B123456 relates to B123456-1 , but how to join these ?)

So far I can only think of a solution by creating a view that contains the shipments with an extra field (a substring of first 7 positions of shipmentnumber) to make a join to the orderheader, but since the database (ORACLE 7.3.4) is bought with the application , this is a bit tricky.

Can anyone think of a single SQL , PL/SQL that can do the trick?

T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[rac2]

Well, isn't a view just a query saved by name for our convenience?

If you can write

CREATE someView AS
SELECT id, someotherstuff FROM anotherTable
WHERE somethingsaretrue

and

SELECT t.*, v.* 
FROM someTable t
JOIN someView v ON t.id = v.id

then you could also write

SELECT t.*, v.* 
FROM someTable t
JOIN (
SELECT id, someotherstuff FROM anotherTable
WHERE somethingsaretrue
) v ON t.id = v.id

Or even

SELECT t.*, v.* 
FROM someTable t
JOIN (
SELECT

substring(shipmentnumber,1,7) AS id

, someotherstuff FROM

someTable

WHERE somethingsaretrue
) v ON t.id = v.id

 

[angiole]

I'm pretty sure I understand, but to confirm, would you provide a better picture of the tables and (necessary) columns of the 2, or is it 3 tables used in this query. AA 8~)

 

[blom0344]

Hello Angiole,

We are talking about two tables here , one for header, one for lines. Both tables contain data about the orders (as delivered from our ERP system) and about shipments (as created by the warehouse system)

ORDERHEADER:

Type Ord/Shipno. Status

S51 B123456 90
S01 B123456-1 12
S01 B123456-2 12


S51 indicates this is an order (not shipment). If order is processed (status at certain level) one or more shipments are created in the header. The lifecycle of these shipments runs from 'Created' to 'Despatched'.
If the status of shipments reaches 'Active' the quantities belonging to the original order should not be taken in the aggregation.

ORDER_LINES:

Ord/Shipno. Lineno. Product Quantity

B123456 1 XXX 50
B123456 2 YYY 10
B123456 3 ZZZ 30
B123456 4 TTT 5
B123456-1 1 XXX 50
B123456-1 2 YYY 10
B123456-2 1 ZZZ 30
B123456-2 2 TTT 5

Almost always the shipment data is a replication of the order data in terms of quantities, except for mutations during the process, where for instance quantity for ZZZ is reduced to 15 instead of 30.

For comparison with the ERP data, I can not take the shipment quantities , but have to get at the original orderdata.

To make matters more complicated: If the shipment of an order reaches the status active the order data in the ERP system is scrapped from the corresponding file. (But not in the warehouse database)

So, to get a good comparison I can only aggregate data from the warehouse for certain orders where corresponding shipments have not reached status 'Active'.

The actual situation is even more complicated, but only in terms of more conditions added (several types, several status) that would make it more confusing

T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[angiole]

So given the above data, what do you want to see as the output ?

p.s., both values for product ZZZ are 30, how did you get to 15? AA 8~)

 

[blom0344]

The output is the sum of the quantities aggregated by product (order is not output,but quantities belonging to orders with shipments of certain status should not be taken into account.)

The ZZZ from 30 to 15 is just to illustrate that during the whole warehouse process there can be mutations altering quantities. It just served to illustrate that I can not use the quantities from the shipments to compare with the ERP system. (Otherwise it would be a simple thing, just getting the shipments with the right status to compare with ERP quantities) T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[angiole]

You're going to have to give me some time for this one ... AA 8~)

 

[angiole]

OK, I may be totally off-base without a proper data set to look at.

I think you need to select specific order lines from specific order headers, so:

---------------------------------------------

Specific Header then is

Select substr(ord_shp_no,1,6) ord_nbr,
max(case when type = 'S51'
then status else null) max_hstat,
max(case when type = 'S01'
then status else null) max_dstat
From ORDERHEADER
Where apply_conditions = true
Group by
substr(ord_shp_no,1,6)
Having apply_conditions = true

---------------------------------------------

The summary of ORDERLINES becomes for those specific headers is

Select substr(ol.ord_shp_no,1,6) ord_nbr,
ol.product,
sum(ol.qty) qty
From
ORDERLINES ol,
(Select substr(ord_shp_no,1,6) ord_nbr,
max(case when type = 'S51'
then status else null) max_hstat,
max(case when type = 'S01'
then status else null) max_dstat
From ORDERHEADER
Where apply_conditions = true
Group by
substr(ord_shp_no,1,6)
Having apply_conditions = true) HQ
Where more_conditions = true
and ol.ord_nbr = HQ.ord_nbr
Group by
substr(ol.ord_shp_no,1,6),
ol.product
Having more_conditions = true

------------------------------------------


Like I said, I think this is a long shot.

If you would like to prepare an access database (it's the only one we could mail) and send it to me, we would much better iron out the wrinkles of the query.

I'm at angelo_angiolella@hrdc-drhc.gc.ca

Tomorrow's my last day before vacation ...

Bye all





AA 8~)

 

[angiole]

Correction:
angelo.angiolella@hrdc-drhc.gc.ca
AA 8~)