We have a Customer Service system where we get units from customers to be repaired. Sometimes we must send the unit to a subsidiary company to be fixed, and we need to establish a ‘genealogy’ approach to determine where a given unit is being serviced, but we also need to know the full ‘trace’ of every order at any given point in time (to check what companies the unit has been sent to).
Basically speaking, we have three tables involved here; for each table I am providing the basic information.
1) Order header (ORD_HDR): we have the company, the order class, the order number, and the customer. The customer will change as the order moves between subsidiaries.
2) Order detail (ORD_DET): we have the company, the order class, the order number, the order line, and the item. Sometimes the item will also change as subsidiaries have their own item coding system.
3) Order linkage (ORD_LNK): we have the parent company, the parent class, the parent order, the parent line, the child company, the child class, the child order, and the child line. The design of this table can be modified if that helps the overall solution. The other two tables (order header and order detail) must stay as indicated above.
Below are the scripts needed to create the three tables. Also, I am providing sample data that can be inserted in each table to make testing easier. Please note that for the ‘order linkage’ table (ORD_LNK), I am providing the data I think needs to go in there, but this is a new table, and I am not sure how the data in the table should look like to fulfill the requirements listed below.
When an order is sent to a subsidiary company, I think I could insert three records:
One record has all the 'parent' columns blank, and then the company, class and order number with a blank line.
One record with the parent company, parent class, parent order and a blank line, and the company, class, order and line that will be sent to the subsidiary.
One record with the company, class, order and line of the source, and the destination company, class, order and line.
Since this is a brand new table, this is how I envision the records would need to be added, but feel free to indicate whether the data in this table needs to look different from what is explained above (and this is the data included in the sample).
CREATE TABLE ORD_HDR (
COMP_ VARCHAR2(6 CHAR) DEFAULT ' ' NOT NULL,
CLASS_ VARCHAR2(3 CHAR) DEFAULT ' ' NOT NULL,
ORDER_ VARCHAR2(20 CHAR) DEFAULT ' ' NOT NULL,
CUSTOMER VARCHAR2(30 CHAR) DEFAULT ' ' NOT NULL);
CREATE UNIQUE INDEX PK_ORD_HDR ON ORD_HDR (COMP_, CLASS_, ORDER_);
CREATE TABLE ORD_DET (
COMP_ VARCHAR2(6 CHAR) DEFAULT ' ' NOT NULL,
CLASS_ VARCHAR2(3 CHAR) DEFAULT ' ' NOT NULL,
ORDER_ VARCHAR2(20 CHAR) DEFAULT ' ' NOT NULL,
LINE VARCHAR2(4 CHAR) DEFAULT ' ' NOT NULL,
ITEM VARCHAR2(30 CHAR) DEFAULT ' ' NOT NULL);
CREATE UNIQUE INDEX PK_ORD_DET ON ORD_DET (COMP_, CLASS_, ORDER_, LINE);
CREATE TABLE ORD_LNK (
PARENT_COMP VARCHAR2(6 CHAR) DEFAULT ' ' NOT NULL,
PARENT_CLASS VARCHAR2(3 CHAR) DEFAULT ' ' NOT NULL,
PARENT_ORDER VARCHAR2(20 CHAR) DEFAULT ' ' NOT NULL,
PARENT_LINE VARCHAR2(4 CHAR) DEFAULT ' ' NOT NULL,
CHILD_COMP VARCHAR2(6 CHAR) DEFAULT ' ' NOT NULL,
CHILD_CLASS VARCHAR2(3 CHAR) DEFAULT ' ' NOT NULL,
CHILD_ORDER VARCHAR2(20 CHAR) DEFAULT ' ' NOT NULL,
CHILD_LINE VARCHAR2(4 CHAR) DEFAULT ' ' NOT NULL);
CREATE UNIQUE INDEX PK_ORD_LNK ON ORD_LNK
(PARENT_COMP, PARENT_CLASS, PARENT_ORDER, PARENT_LINE, CHILD_COMP, CHILD_CLASS, CHILD_ORDER, CHILD_LINE);
insert into ORD_HDR values ('01', 'CNT', '23456', 'CUSTOMER');
insert into ORD_DET values ('01', 'CNT', '23456', '0001', 'ITEM 1');
insert into ORD_DET values ('01', 'CNT', '23456', '0002', 'ITEM 2');
insert into ORD_DET values ('01', 'CNT', '23456', '0003', 'ITEM 3');
insert into ORD_DET values ('01', 'CNT', '23456', '0004', 'ITEM 4');
insert into ORD_HDR values ('06', 'CNT', '23456A', 'CUSTOMER A');
insert into ORD_DET values ('06', 'CNT', '23456A', '0001', 'ITEM A1');
insert into ORD_HDR values ('09', 'CNT', '23456F', 'CUSTOMER F');
insert into ORD_DET values ('09', 'CNT', '23456F', '0001', 'ITEM F1');
insert into ORD_HDR values ('08', 'CNT', '23456B', 'CUSTOMER B');
insert into ORD_DET values ('08', 'CNT', '23456B', '0001', 'ITEM B1');
insert into ORD_HDR values ('12', 'CNT', '23456D', 'CUSTOMER D');
insert into ORD_DET values ('12', 'CNT', '23456D', '0001', 'ITEM D1');
insert into ORD_HDR values ('26', 'CNT', '23456C', 'CUSTOMER C');
insert into ORD_DET values ('26', 'CNT', '23456C', '0001', 'ITEM C1');
insert into ORD_HDR values ('05', 'CNT', '23456E', 'CUSTOMER E');
insert into ORD_DET values ('05', 'CNT', '23456E', '0001', 'ITEM E1');
insert into ORD_LNK values (' ', ' ', ' ', ' ', '01', 'CNT', '23456', ' ');
insert into ORD_LNK values ('01', 'CNT', '23456', ' ', '01', 'CNT', '23456', '0002');
insert into ORD_LNK values ('01', 'CNT', '23456', '0002', '06', 'CNT', '23456A', '0001');
insert into ORD_LNK values ('06', 'CNT', '23456A', '0001', '09', 'CNT', '23456F', '0001');
insert into ORD_LNK values ('01', 'CNT', '23456', ' ', '01', 'CNT', '23456', '0003');
insert into ORD_LNK values ('01', 'CNT', '23456', '0003', '08', 'CNT', '23456B', '0001');
insert into ORD_LNK values ('08', 'CNT', '23456B', '0001', '12', 'CNT', '23456D', '0001');
insert into ORD_LNK values ('01', 'CNT', '23456', ' ', '01', 'CNT', '23456', '0004');
insert into ORD_LNK values ('01', 'CNT', '23456', '0004', '26', 'CNT', '23456C', '0001');
insert into ORD_LNK values ('26', 'CNT', '23456C', '0001', '05', 'CNT', '23456E', '0001');
insert into ORD_HDR values ('01', 'CNT', '34567', 'CUSTOMER');
insert into ORD_DET values ('01', 'CNT', '34567', '0001', 'ITEM 1');
insert into ORD_DET values ('01', 'CNT', '34567', '0002', 'ITEM 2');
insert into ORD_DET values ('01', 'CNT', '34567', '0003', 'ITEM 3');
insert into ORD_HDR values ('21', 'CNT', '34567A', 'CUSTOMER A');
insert into ORD_DET values ('21', 'CNT', '34567A', '0001', 'ITEM A1');
insert into ORD_DET values ('21', 'CNT', '34567A', '0002', 'ITEM A2');
insert into ORD_HDR values ('02', 'CNT', '34567B', 'CUSTOMER B');
insert into ORD_DET values ('02', 'CNT', '34567B', '0001', 'ITEM B1');
insert into ORD_LNK values (' ', ' ', ' ', ' ', '01', 'CNT', '34567', ' ');
insert into ORD_LNK values ('01', 'CNT', '34567', ' ', '01', 'CNT', '34567', '0002');
insert into ORD_LNK values ('01', 'CNT', '34567', '0002', '21', 'CNT', '34567A', '0001');
insert into ORD_LNK values ('01', 'CNT', '34567', ' ', '01', 'CNT', '34567', '0003');
insert into ORD_LNK values ('01', 'CNT', '34567', '0003', '02', 'CNT', '34567B', '0001');
insert into ORD_LNK values ('02', 'CNT', '34567B', '0001', '21', 'CNT', '34567A', '0002');
insert into ORD_HDR values ('01', 'CNT', '65231', 'CUSTOMER');
insert into ORD_DET values ('01', 'CNT', '65231', '0001', 'ITEM 1');
insert into ORD_DET values ('01', 'CNT', '65231', '0002', 'ITEM 2');
insert into ORD_DET values ('01', 'CNT', '65231', '0003', 'ITEM 3');
insert into ORD_DET values ('01', 'CNT', '65231', '0004', 'ITEM 4');
insert into ORD_HDR values ('03', 'CNT', '65231A', 'CUSTOMER A');
insert into ORD_DET values ('03', 'CNT', '65231A', '0001', 'ITEM A1');
insert into ORD_DET values ('03', 'CNT', '65231A', '0002', 'ITEM A2');
insert into ORD_HDR values ('08', 'CNT', '65231B', 'CUSTOMER B');
insert into ORD_DET values ('08', 'CNT', '65231B', '0001', 'ITEM B1');
insert into ORD_LNK values (' ', ' ', ' ', ' ', '01', 'CNT', '65231', ' ');
insert into ORD_LNK values ('01', 'CNT', '65231', ' ', '01', 'CNT', '65231', '0001');
insert into ORD_LNK values ('01', 'CNT', '65231', '0001', '03', 'CNT', '65231A', '0001');
insert into ORD_LNK values ('01', 'CNT', '65231', ' ', '01', 'CNT', '65231', '0003');
insert into ORD_LNK values ('01', 'CNT', '65231', '0003', '08', 'CNT', '65231B', '0001');
insert into ORD_LNK values ('01', 'CNT', '65231', ' ', '01', 'CNT', '65231', '0004');
insert into ORD_LNK values ('01', 'CNT', '65231', '0004', '03', 'CNT', '65231A', '0002');
We have to create a couple of database objects (preferably views) to fulffil the following requirements.
1) The first view will be used in a ‘grid’ like object so the users in a specific company can ‘visually’ see all the orders that they have ‘in house’, along with the order’s full tree. This object should have a ‘header’ record (highlighted in yellow) representing the ‘root order’, and below it all the lines that represent the orders that have been sent to other companies. See below images for examples of the desired output for different companies.
When reviewed in company 01.
When reviewed in company 06.
When reviewed in company 08.
When reviewed in company 21.
2) The second view will be used in SQL reports, and it needs to return the full tree (including the root order) for any given order. See examples of query code and expected resulting dataset below.
If we query for order 01-CNT-23456-0003, the resulting dataset would be:
select * from [view] where SEARCH_COMP = '01' and SEARCH_CLASS = 'CNT' and SEARCH_ORDER = '23456' and SEARCH_LINE = '0003';
01-CNT-23456
01-CNT-23456-0002
06-CNT-23456A-0001
09-CNT-23456F-0001
01-CNT-23456-0003
08-CNT-23456B-0001
12-CNT-23456D-0001
01-CNT-23456-0004
26-CNT-23456C-0001
05-CNT-23456E-0001
If we query for order 02-CNT-34567B-0001, the resulting dataset would be:
select * from [view] where SEARCH_COMP = '02' and SEARCH_CLASS = 'CNT' and SEARCH_ORDER = '34567B' and SEARCH_LINE = '0001';
01-CNT-34567
01-CNT-34567-0002
21-CNT-34567A-0001
01-CNT-34567-0003
02-CNT-34567B-0001
21-CNT-34567A-0002
If we query for order 08-CNT-65231B-0001, the resulting dataset would be:
select * from [view] where SEARCH_COMP = '08' and SEARCH_CLASS = 'CNT' and SEARCH_ORDER = '65231B' and SEARCH_LINE = '0001';
01-CNT-65231
01-CNT-65231-0001
03-CNT-65231A-0001
01-CNT-65231-0003
08-CNT-65231B-0001
01-CNT-65231-0004
03-CNT-65231A-0002
If we can use the same view for both requirements, that would be great as it would reduce the maintenance efforts. But if this is not possible, then we can get the two separate views.
I have written some code to build a view for the grid (requirement #1 above). The code is found below.
Subquery COMP_ORDER_XREF provides a list of all the companies where an order has been serviced.
Subquery LINKED_ORDERS provides the full hierarchy of all orders, but it also includes a ‘row 0’ record (for the root orders).
The final query does a cartesian product between both subqueries to provide the full list of orders for each company.
I am quite sure this code can be enhanced, but I still wanted to share what I have done so far for this requirement.
with
-- query below returns list of all 'root' orders and the companies that touched them
COMP_ORDER_XREF as (
select CHILD_COMP, ROOT_ORDER
from (select CHILD_COMP, connect_by_root(CHILD_ORDER) as ROOT_ORDER
from ORD_LNK
start with PARENT_COMP = ' ' and PARENT_CLASS = ' ' and PARENT_ORDER = ' ' and PARENT_LINE = ' '
connect by PARENT_COMP = prior CHILD_COMP and PARENT_CLASS = prior CHILD_CLASS and PARENT_ORDER = prior CHILD_ORDER and
PARENT_LINE = prior CHILD_LINE)
where CHILD_COMP <> ' '
group by CHILD_COMP, ROOT_ORDER),
-- query below returns full list of linked orders, including 'root' orders
LINKED_ORDERS as (
select *
from (
-- query below returns list of 'root' orders
select ' ' as PARENT_COMP, ' ' as PARENT_CLASS, ' ' as PARENT_ORDER, ' ' as PARENT_LINE, a.CHILD_COMP, a.CHILD_CLASS,
a.CHILD_ORDER, ' ' as CHILD_LINE, 0 as LVL, a.CHILD_COMP as ROOT_CCN, a.CHILD_CLASS as ROOT_CLASS,
a.CHILD_ORDER as ROOT_ORDER, a.CHILD_LINE as ROOT_LINE,
a.CHILD_COMP || ' - ' || a.CHILD_CLASS || ' - ' || a.CHILD_ORDER as ROOT,
a.CHILD_COMP || ' - ' || a.CHILD_CLASS || ' - ' || a.CHILD_ORDER as PATH
from ORD_LNK a
where a.PARENT_COMP = ' ' and a.PARENT_CLASS = ' ' and a.PARENT_ORDER = ' ' and a.PARENT_LINE = ' '
group by a.CHILD_COMP, a.CHILD_CLASS, a.CHILD_ORDER, a.CHILD_LINE
union
-- query below returns 'full' tree of orders
select a.PARENT_COMP, a.PARENT_CLASS, a.PARENT_ORDER, a.PARENT_LINE, a.CHILD_COMP, a.CHILD_CLASS, a.CHILD_ORDER,
a.CHILD_LINE, level as LVL, connect_by_root(a.CHILD_COMP) as ROOT_CCN,
connect_by_root(a.CHILD_CLASS) as ROOT_CLASS, connect_by_root(a.CHILD_ORDER) as ROOT_ORDER,
connect_by_root(a.CHILD_LINE) as ROOT_LINE,
connect_by_root(a.CHILD_COMP || ' - ' || a.CHILD_CLASS || ' - ' || a.CHILD_ORDER || ' - ' ||
a.CHILD_LINE) as ROOT,
concat(lpad(' ', (level + 1) * 3 - 3), a.CHILD_COMP || ' - ' || a.CHILD_CLASS || ' - ' ||
a.CHILD_ORDER || ' - ' || a.CHILD_LINE) as PATH
from ORD_LNK a
start with a.PARENT_COMP <> ' ' and a.PARENT_CLASS <> ' ' and a.PARENT_ORDER <> ' ' and a.PARENT_LINE = ' '
connect by a.PARENT_COMP = prior a.CHILD_COMP and a.PARENT_CLASS = prior a.CHILD_CLASS and
a.PARENT_ORDER = prior a.CHILD_ORDER and a.PARENT_LINE = prior a.CHILD_LINE
))
-- query below does cartesian product w/COMP_ORDER_XREF and LINKED_ORDERS so all rows are available in view for any company
select a.CHILD_COMP, a.ROOT_ORDER, b.CHILD_COMP, b.CHILD_CLASS, b.CHILD_ORDER, b.CHILD_LINE, b.LVL, b.ROOT_CCN, b.ROOT, b.PATH
from COMP_ORDER_XREF a
inner join LINKED_ORDERS b on b.ROOT_ORDER = a.ROOT_ORDER
order by a.CHILD_COMP, b.ROOT, b.LVL;
For the second requirement (a view that returns the full tree for any given order), I am not sure how to tackle that part.
Thanks in advance for helping me figure how to get this done.