Pmorey at 14:24, 19 April 2024

2024-04-19T14:24:49Z

@@ Line 182: / Line 182: @@
 where o.delivery_date=:order_date
 group by o.cust_no,o.order_code,o.session_no,o.trans_no
 </pre>

WikiAdmin: 1 revision imported

2023-07-14T12:58:49Z

1 revision imported

← Older revision	Revision as of 12:58, 14 July 2023
(No difference)

Intra>User: 1 revision imported

2017-01-10T00:01:36Z

1 revision imported

New page

== List all Loads for an Order Number ==
<PRE>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</PRE>

== Diagnosing Loads for Schedule Screen ==

This query is similar to the selection from dip_schedule:
<PRE>
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
</PRE>

Compare the results to a simple load query:
<PRE>
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
</PRE>

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

Detail is driven off of the dispatch schedule procedure:
<PRE>select * from dip_schedule ('10/18','10/19')</PRE>

Header Detail comes from Dispatch Plant Summary Query:
<PRE>
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 </PRE>

The following example expands detail for the undelivered items that would go into the sum by plant query:
<PRE>
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'
</PRE>

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

<PRE>
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
</PRE>

REVISED:
<PRE>
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
</PRE>

== Load Schedule DateTime alert ==
Related to bug in 1.8.41.0 where new orders for future date schedule to today.

<PRE>
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
</PRE>

== Finding mismatched Delv Qty and Ticketed Qty ==
Problem in 1.8.40.0 where Load Qty not updated to match corresponding ticket.

<PRE>
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
</PRE>

Fix Query: (1 load at a time based on above diag qry)
<PRE>update ditprjld l
set load_size=10 where order_session_no=587 and order_trans_no=80 and load_no=1
</PRE>

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.

<PRE>
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)
</PRE>

== Dispatch Order Summary Analysis ==
<pre>
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
</pre>

Dispatch Diagnostic Query Collection - Revision history

Pmorey at 14:24, 19 April 2024

WikiAdmin: 1 revision imported

Intra>User: 1 revision imported