Dispatch Diagnostic Query Collection

List all Loads for an Order Number

select l.* from ditprjld l
inner join artordhd o on l.order_session_no = o.session_no and l.order_trans_no=o.trans_no
where o.order_code=:ord_code

Diagnosing Loads for Schedule Screen

This query is similar to the selection from dip_schedule:

SELECT OH.CUST_NO,OH.ORDER_CODE, PJ.PLANT_NO,OH.PLANT_NO, PJ.STATUS_COL, PJ.SCHED_DATETIME,
             PJ.LOAD_SIZE, PJ.RETURN_DT,
             PJ.TGT_DEPART_DT,
             UC.DESCRIPTION,
             CU.NAME,
             PL.PLANT_SCREEN_ID, PL.PLANT_TYPE,OH.*
   FROM ARTORDHD OH
    INNER JOIN DIVPLANT_CONCRETE PL ON (PL.PLANT_NO = OH.PLANT_NO)
    INNER JOIN ARTCUST CU ON (CU.CUST_NO = OH.CUST_NO)
    INNER JOIN DITPRJLD PJ ON (PJ.ORDER_SESSION_NO = OH.SESSION_NO) AND
            (PJ.ORDER_TRANS_NO = OH.TRANS_NO) AND
            (PJ.SCHED_DATETIME BETWEEN :ORD_DT AND 1+:ORD_DT)
    LEFT OUTER JOIN DITUSECL UC ON (UC.CLASS_NO = OH.USAGE_TYPE)
     WHERE (OH.ACTIVE_FLAG = 'Y') AND 
           (OH.ORDER_STATUS <> 'C') 
      ORDER BY OH.SESSION_NO, OH.TRANS_NO

Compare the results to a simple load query:

select l.* from ditprjld l
inner join artordhd o on l.order_session_no = o.session_no and l.order_trans_no=o.trans_no
where l.SCHED_DATETIME BETWEEN :ORD_DT AND 1+:ORD_DT

Schedule Screen: Totals in Screen Header mismatch detail on Schedule Screen

Detail is driven off of the dispatch schedule procedure:

select * from dip_schedule ('10/18','10/19')

Header Detail comes from Dispatch Plant Summary Query:

select pj.PLANT_NO,cast('U' AS CHAR(1)) RECTYPE, 
 sum(pj.load_size) QTY, 
 count  LDS 
from ditprjld pj 
inner join artordhd oh on (oh.session_no = pj.order_session_no) and 
                          (oh.trans_no = pj.order_trans_no) and 
                 (oh.active_flag = 'Y') and 
                 (oh.order_status <> 'C') 
where (pj.sched_datetime between :beg_datetime and :end_Datetime) and 
 ((pj.status_col is null) and (pj.return_dt is null)) 
GROUP BY pj.PLANT_NO 
UNION ALL 
select pj.plant_no, cast('D' AS CHAR(1)) RECTYPE,  
 sum(pj.load_size) QTY, 
 count  LDS 
from ditprjld pj 
inner join artordhd oh on (oh.session_no = pj.order_session_no) and 
                          (oh.trans_no = pj.order_trans_no) and 
                 (oh.active_flag = 'Y') and 
                 (oh.order_status <> 'C') 
where (pj.sched_datetime between :beg_datetime and :end_Datetime) and 
 ((pj.status_col is not null) or (pj.return_dt is not null)) 
GROUP BY pj.PLANT_NO 

The following example expands detail for the undelivered items that would go into the sum by plant query:

select oh.order_code,oh.cust_no,pj.PLANT_NO,oh.plant_no,
pj.load_size QTY, pj.*
from ditprjld pj 
inner join artordhd oh on (oh.session_no = pj.order_session_no) and 
                          (oh.trans_no = pj.order_trans_no) and 
                 (oh.active_flag = 'Y') and 
                 (oh.order_status <> 'C') 
where (pj.sched_datetime between :beg_datetime and :end_Datetime) and 
 ((pj.status_col is null) and (pj.return_dt is null)) 
  and pj.plant_no='2'

Load vs Ticket Analysis for an Order

Analyze loads vs. Tickets to compare:

• Trucks
• Quantities
• Multiple Tickets tied to a Load (Pay attention to Void flag .. void will still point to original load)
• Verify Voids

select OH.CUST_NO,OH.ORDER_CODE,OH.DELIVERY_DATE,l.order_session_no,l.order_trans_no, l.load_no,th.ticket_no,l.truck_no,th.truck_no,TD.PRODUCT_CODE,TD.QTY_SOLD,L.LOAD_SIZE,VOID_FLAG,L.LOAD_NO,TH.ORDER_LOAD_NO,oh.*
from arTPTKHD TH
INNER JOIN ARTPTKDT TD ON TD.SESSION_NO=TH.SESSION_NO AND TD.TRANS_NO=TH.TRANS_NO AND TD.LINE_NO=1
INNER JOIN ARTORDHD OH ON TH.ORDER_SESSION_NO=OH.SESSION_NO AND TH.ORDER_TRANS_NO=OH.TRANS_NO INNER JOIN DITPRJLD L ON L.ORDER_SESSION_NO=OH.SESSION_NO AND L.ORDER_TRANS_NO=OH.TRANS_NO
 AND   TH.ORDER_LOAD_NO=L.LOAD_NO
where order_code =:Order_Code

REVISED:

select TH.VOID_FLAG,OH.CUST_NO,OH.ORDER_CODE,OH.DELIVERY_DATE,l.order_session_no,l.order_trans_no, l.load_no,th.ticket_no,l.truck_no,th.truck_no,TD.PRODUCT_CODE,TD.QTY_SOLD TKT_QTY,L.LOAD_SIZE ORD_LD_SZ,VOID_FLAG,L.LOAD_NO,TH.ORDER_LOAD_NO,th.session_no tksno,th.trans_no tktno,l.order_session_no ordsno,l.order_trans_no ordtno
from arTPTKHD TH
INNER JOIN ARTPTKDT TD ON TD.SESSION_NO=TH.SESSION_NO AND TD.TRANS_NO=TH.TRANS_NO AND TD.LINE_NO=1
INNER JOIN ARTORDHD OH ON TH.ORDER_SESSION_NO=OH.SESSION_NO AND TH.ORDER_TRANS_NO=OH.TRANS_NO INNER JOIN DITPRJLD L ON L.ORDER_SESSION_NO=OH.SESSION_NO AND L.ORDER_TRANS_NO=OH.TRANS_NO
 AND   TH.ORDER_LOAD_NO=L.LOAD_NO
where order_code =:Order_Code
ORDER BY TH.VOID_FLAG,L.LOAD_NO

Load Schedule DateTime alert

Related to bug in 1.8.41.0 where new orders for future date schedule to today.

select o.cust_no,o.order_code,o.session_no,  o.delivery_date,min(l.sched_datetime),    o.trans_no,sum(load_size),
(select sum(d.qty_ordered) from artorddt d where o.session_no=d.session_no and o.trans_no=d.trans_no and line_no=1) ordrd
 from artordhd o
inner join ditprjld l on l.order_session_no=o.session_no and l.order_trans_no=o.trans_no
where o.delivery_date>=:order_dt   
group by o.cust_no,o.order_code,o.session_no,o.trans_no,o.delivery_date
having  min(l.sched_datetime)<o.delivery_date 

Finding mismatched Delv Qty and Ticketed Qty

Problem in 1.8.40.0 where Load Qty not updated to match corresponding ticket.

select OH.CUST_NO,OH.ORDER_CODE,OH.DELIVERY_DATE,l.order_session_no,l.order_trans_no, l.load_no,TD.PRODUCT_CODE,TD.QTY_SOLD,L.LOAD_SIZE,VOID_FLAG,L.LOAD_NO,TH.ORDER_LOAD_NO
from arTPTKHD TH
INNER JOIN ARTPTKDT TD ON TD.SESSION_NO=TH.SESSION_NO AND TD.TRANS_NO=TH.TRANS_NO AND TD.LINE_NO=1
INNER JOIN ARTORDHD OH ON TH.ORDER_SESSION_NO=OH.SESSION_NO AND TH.ORDER_TRANS_NO=OH.TRANS_NO INNER JOIN DITPRJLD L ON L.ORDER_SESSION_NO=OH.SESSION_NO AND L.ORDER_TRANS_NO=OH.TRANS_NO
 AND   TH.ORDER_LOAD_NO=L.LOAD_NO
WHERE QTY_SOLD<>LOAD_SIZE

Fix Query: (1 load at a time based on above diag qry)

update ditprjld l
set load_size=10 where order_session_no=587 and order_trans_no=80 and load_no=1

NOTES:

• This update qry breaks the qty ordered. To fix Qty Ordered reset the spacing or order time in order maint.
• Not tested on voids.

Identify Ordered QTY mismatch -- Difference Between Order and Loads

• Order Maintenance will look correct.
• Loads List will be incorrect.
• Schedule Screen shows wrong amount.

select o.cust_no,o.order_code,o.session_no,o.trans_no,sum(load_size),
(select sum(d.qty_ordered) from artorddt d where o.session_no=d.session_no and o.trans_no=d.trans_no and line_no=1) ordrd
 from artordhd o
inner join ditprjld l on l.order_session_no=o.session_no and l.order_trans_no=o.trans_no
where tgt_depart_dt between :ord_dt and 1+:ord_dt
group by o.cust_no,o.order_code,o.session_no,o.trans_no
having
sum(load_size)<>
(select sum(d.qty_ordered) from artorddt d where o.session_no=d.session_no and o.trans_no=d.trans_no and line_no=1) 

Dispatch Order Summary Analysis

select o.cust_no,o.order_code,o.session_no,o.trans_no,
(select sum(d.qty_ordered) from artorddt d where o.session_no=d.session_no and o.trans_no=d.trans_no and line_no=1) Ordered,
sum(load_size) LoadTot, 
(select sum(d.qty_delivered) from artorddt d where o.session_no=d.session_no and o.trans_no=d.trans_no and line_no=1) qty_delvd,
(select sum(TD.qty_sold) from artptkdt TD join artptkhd TH on (TD.session_no = TH.session_no and TD.trans_no = TH.trans_no)  
     where (TH.cust_no = o.cust_no and TH.order_code = o.order_code and TD.line_no = 1 and TH.void_flag= 'N')) Qty_Ticketed, 
(select sum(TD.qty_sold) from artptkdt TD join artptkhd TH on (TD.session_no = TH.session_no and TD.trans_no = TH.trans_no)  
     where (TH.cust_no = o.cust_no and TH.order_code = o.order_code and TD.line_no = 1 and TH.void_flag= 'Y')) Voided_Qty,
count(*) Loads,
(select count(*) from artptkdt TD join artptkhd TH on (TD.session_no = TH.session_no and TD.trans_no = TH.trans_no)  
     where (TH.cust_no = o.cust_no and TH.order_code = o.order_code and TD.line_no = 1 and TH.void_flag= 'N')) Ticketed_Loads, 
 (select count(*) from artptkdt TD join artptkhd TH on (TD.session_no = TH.session_no and TD.trans_no = TH.trans_no)  
     where (TH.cust_no = o.cust_no and TH.order_code = o.order_code and TD.line_no = 1 and TH.void_flag= 'Y')) Voided_Loads,
min(l.sched_datetime) First_Load,max(l.sched_datetime) Last_Load
 from artordhd o
inner join ditprjld l on l.order_session_no=o.session_no and l.order_trans_no=o.trans_no
where o.delivery_date=:order_date
group by o.cust_no,o.order_code,o.session_no,o.trans_no

Find batch ingredients that do not exist as Keystone products

NOTE: You will need to replace the '-' with whatever product separator the customer uses!

select distinct (d.ingred_id)
from ditbwhd h
inner join ditbwdt d on (d.reckey = h.reckey)
where (h.load_dt between :beg_date and :end_date) and (d.val_product_code is null) and
 (not exists (select 1 from artprod where (product_code starting with d.ingred_id||'-')))