Dispatch Diagnostic Query Collection: Difference between revisions

From KeystoneIntranet
Jump to navigation Jump to search
Intra>User
m (1 revision imported)
 
m (1 revision imported)

Revision as of 12:58, 14 July 2023

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
Retrieved from "http://in.compucrete.com/index.php?title=Dispatch_Diagnostic_Query_Collection&oldid=162"