Query/Export Dispatch Reports

Dispatch Time Analysis (03/30/17)

Dispatch Times Super Mega Query

This beast gives you most every time calculation you would want from Plant, Office and Sale ticket history. Along with some basic ticket header info you get:

• Actual Times for each status event
• Target Times for each status event
• Minutes in Each status
• Minutes Late for Each Status Event (Negative = Early)
• Minutes over for each status (Negative = Under)

SELECT 
plant_no,ticket_no, cust_no,job_po_loc, zone, dr.driver_name,truck_no, 
batch_start_dt, batch_end_dt, leave_plant_dt, arrive_job_dt, begin_pour_dt, end_pour_dt, leave_job_dt, return_plant_dt,
tgt_batch_start_dt, tgt_batch_end_dt, tgt_leave_plant_dt, tgt_arrive_job_dt, tgt_begin_pour_dt, tgt_end_pour_dt, tgt_leave_job_dt, tgt_return_plant_dt,

floor(1440*(batch_end_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100* 
    (ticket_time/100))  as numeric (11,6) ) /1440) ) "TICKET_TO_BATCH_MIN",

floor(.5 + 1440*(batch_end_dt-batch_start_dt)) "BATCH_MIN",
floor(.5 + 1440*(leave_plant_dt-batch_end_dt)) "PLANT_WASH_MIN",
floor(.5 + 1440*(arrive_job_dt-leave_plant_dt)) "TO_JOB_MIN",
floor(.5 + 1440*(begin_pour_dt-arrive_job_dt)) "JOB_WAIT_MIN",
floor(.5 + 1440*(end_pour_dt-begin_pour_dt)) "POUR_MIN",
floor(.5 + 1440*(leave_job_dt-end_pour_dt))  "JOB_WASH_MIN",
floor(.5 + 1440*(return_plant_dt-leave_job_dt)) "TO_PLANT_MIN",
floor(1440*(return_plant_DT-cast(ticket_Date as timestamp )-cast (  (60*(ticket_time/100)+ticket_time-100* 
    (ticket_time/100))  as numeric (11,6) ) /1440 ) )  RTT,

floor(.5 + 1440*(batch_start_dt-tgt_batch_start_dt)) BATCH_START_LATE,
floor(.5 + 1440*(batch_end_dt-tgt_batch_end_dt)) BATCH_END_LATE,
floor(.5 + 1440*(leave_plant_dt-tgt_leave_plant_dt)) LEAVE_PLANT_LATE,
floor(.5 + 1440*(arrive_job_dt-tgt_arrive_job_dt)) ARRIVE_JOB_LATE,
floor(.5 + 1440*(begin_pour_dt-tgt_begin_pour_dt)) BEGIN_POUR_LATE,
floor(.5 + 1440*(end_pour_dt-tgt_end_pour_dt)) END_POUR_LATE,
floor(.5 + 1440*(leave_job_dt-tgt_leave_job_dt)) LEAVE_JOB_LATE,
floor(.5 + 1440*(return_plant_dt-tgt_return_plant_dt)) RETURN_PLANT_LATE,

floor(.5 + 1440*((batch_end_dt-batch_start_dt)-(tgt_batch_end_dt-tgt_batch_start_dt))) "BATCH_OVR",
floor(.5 + 1440*((leave_plant_dt-batch_end_dt)- (tgt_leave_plant_dt-tgt_batch_end_dt))) "PLANT_WASH_OVR",
floor(.5 + 1440*((arrive_job_dt-leave_plant_dt)-(tgt_arrive_job_dt-tgt_leave_plant_dt))) "TO_JOB_OVR",
floor(.5 + 1440*((begin_pour_dt-arrive_job_dt)-(tgt_begin_pour_dt-tgt_arrive_job_dt))) "JOB_WAIT_OVR",
floor(.5 + 1440*((end_pour_dt-begin_pour_dt)-(tgt_end_pour_dt-tgt_begin_pour_dt))) "POUR_OVR",
floor(.5 + 1440*((leave_job_dt-end_pour_dt)-(tgt_leave_job_dt-tgt_end_pour_dt)))  "JOB_WASH_OVR",
floor(.5 + 1440*((return_plant_dt-leave_job_dt)-(tgt_return_plant_dt-tgt_leave_job_dt))) "TO_PLANT_OVR",
order_load_no "LD_NO",
extract (hour from batch_start_dt) "BAT_STRT_HR",
QTY_SOLD,th.elapsed_time,
TICKET_DATE ,TICKET_TIME,order_code
FROM DIP_TICKETHISTORY 
('Y','Y','Y','Y',:BEG_DATE,:END_DATE,null,null,NULL,NULL,NULL,NULL,null,null) th
left outer join artdriv dr on dr.driver_code = th.driver
order by plant_no,ticket_no

(Corrected OVR fields on 02/25/20)

If you need to ID first loads you can add this to your field list...

,
(SELECT 
'*' FROM DIP_TICKETHISTORY 
('Y','Y','Y','Y',TH.TICKET_DATE,TH.TICKET_DATE,TH.CUST_NO,TH.CUST_NO,NULL,NULL,NULL,NULL,TH.PLANT_NO,TH.PLANT_NO) thX
WHERE THX.ORDER_CODE=TH.ORDER_CODE
having min(order_load_no)=th.order_load_no) "Firsty"

DIP_TICKETHISTORY Parameters:

• INC_CUR_TIX Plant tickets
• INC_EXP_TIX Exported Plant tickets
• INC_OFF_TIX Office tickets
• INC_INV_TIX Sales
• LODATE Ticket Date Begin
• HIDATE Ticket Date End
• LOCUST Begin Customer
• HICUST End Customer
• LOCCLS Low Cust Class
• HICCLS High Cust Class
• LOPCLS Low Product Class
• HIPCLS Hight Product Class
• LOPLANT Begin Plant
• HIPLANT End Plant

Use these parameters and not a where clause whenever possible. This is much more efficient. (e.g. if you put a where clause for plant rather than use the parameter, the procedure will first process data for all plants and then filter out all the unselected plants).

Dispatch Ticket Yardage Report

select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,h.product_code,h.description,h.qty_sold,h.tax_code,
     h.void_flag,source_table STATUS  from 
DIP_TICKETHISTORY ('Y','Y','Y','Y',:BEG_DATE,:END_DATE,'','z',NULL,NULL,NULL,NULL,:"Begin Plant",:"End Plant") h
  where ticket_date between :beg_date and :end_date AND LINE_NO=1 and void_flag='N'

Old Style

select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,
     h.void_flag,h.closed_flag,'D' STATUS from artptkhd h
  inner join artptkdt d on  d.session_no=h.session_no and d.trans_no=h.trans_no  
  where ticket_date between :beg_date and :end_date AND D.LINE_NO=1 AND PLANT_NO BETWEEN :Beg_Plant and :End_Plant and void_flag='N'Union
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,'N','T' STATUS
 from arttckhd h
inner join arttckdt d on  d.session_no=h.session_no and d.trans_no=h.trans_no
where ticket_date between :beg_date and :end_date AND D.LINE_NO=1 AND PLANT_NO BETWEEN :Beg_Plant and :End_Plant and void_flag='N' UNION
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,'N','S' STATUS
 from artSLShd h
inner join artSLSdt d on  d.session_no=h.session_no and d.trans_no=h.trans_no
where ticket_date between :beg_date and :end_date AND D.LINE_NO=1 AND PLANT_NO BETWEEN :Beg_Plant and :End_Plant and void_flag='N'

Ticket Detail Report

select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,h.product_code,h.description,h.qty_sold,h.tax_code,h.void_flag,h.source_table STATUS  from 
DIP_TICKETHISTORY ('Y','Y','Y','Y',:BEG_DATE,:END_DATE,'','z',NULL,NULL,NULL,NULL,:"Begin Plant",:"End Plant") h
  where ticket_date between :beg_date and :end_date AND LINE_NO=1 and void_flag='N'

Old Style:

select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,h.closed_flag,'D' STATUS from artptkhd h
inner join artptkdt d on  d.session_no=h.session_no and d.trans_no=h.trans_no
where ticket_date between :beg_date and :end_date
Union
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,'N','T' STATUS
 from arttckhd h
inner join arttckdt d on  d.session_no=h.session_no and d.trans_no=h.trans_no
where ticket_date between :beg_date and :end_date
 UNION
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,'N','S' STATUS
 from artSLShd h
inner join artSLSdt d on  d.session_no=h.session_no and d.trans_no=h.trans_no
where ticket_date between :beg_date and :end_date

Tickets by Order Code

select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,h.closed_flag,'D' STATUS from artptkhd h
inner join artptkdt d on  d.session_no=h.session_no and d.trans_no=h.trans_no
where h.order_code=:order_code
Union
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,'N','T' STATUS
 from arttckhd h
inner join arttckdt d on  d.session_no=h.session_no and d.trans_no=h.trans_no
where h.order_code=:order_code
 UNION
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,'N','S' STATUS
 from artSLShd h
inner join artSLSdt d on  d.session_no=h.session_no and d.trans_no=h.trans_no
where h.order_code=:order_code