procedure GetALLPaymentApply(p_vehicleno in employforeignvehicle.vehicleno%type) is
p_pickupmode varchar2(200) ;
P_yuqitianshu number ;
begin
-- select count(o.pickupmode) into from
select o.pickupmode into p_pickupmod from orders o,ordertrack od where od.licenseplateno=p_vehicleno and od.licenseplateno=o.orderid;
-- p_pickupmode:=o.pickupmode;
if(p_pickupmode ='直送') then if(p_pickupmode ='签单') then select
--dd.vehicleno 车牌号,
tt.licenseplateno 车牌号,
o.dzt 缴单状态,
o.orderid 承运单号,
o.clearingform 付款方式,
decode(o.dzt,
'未交单',
round(sysdate - tt.departuretime - 0.03125, 0),
'已交单',
round((select ot.tracktime
from ordertrack ot
where ot.orderid = o.orderid
and ot.eventtype = '客户签收') -
tt.departuretime - 0.03125,
0)) 逾期天数,
decode(o.dzt,
'未交单',
round(sysdate - tt.departuretime - 0.03125, 0),
'已交单',
round((select ot.tracktime
from ordertrack ot
where ot.orderid = o.orderid
and ot.eventtype = '客户签收') -
tt.departuretime - 0.03125,
0)) * 50 罚款金额
from orders o,
longdistancetask tdt,
transporttrip tt
-- dispatchvehicle dd
--EmployForeignVehicle ev
where o.pickupmode = '直送'
and o.clearingform = '签单'
and tdt.orderid = o.orderid
and tt.transporttripno = tdt.tripno
and decode(o.dzt,
'未交单',
round(sysdate - tt.departuretime - 0.03125, 0),
'已交单',
round((select ot.tracktime
from ordertrack ot
where ot.orderid = o.orderid
and ot.eventtype = '客户签收') - tt.departuretime -
0.03125,
0)) > 0
group by o.orderid, tt.licenseplateno,o.dzt,o.clearingform,o.dzt,tt.departuretime; end if;
end GetALLPaymentApply;
p_pickupmode varchar2(200) ;
P_yuqitianshu number ;
begin
-- select count(o.pickupmode) into from
select o.pickupmode into p_pickupmod from orders o,ordertrack od where od.licenseplateno=p_vehicleno and od.licenseplateno=o.orderid;
-- p_pickupmode:=o.pickupmode;
if(p_pickupmode ='直送') then if(p_pickupmode ='签单') then select
--dd.vehicleno 车牌号,
tt.licenseplateno 车牌号,
o.dzt 缴单状态,
o.orderid 承运单号,
o.clearingform 付款方式,
decode(o.dzt,
'未交单',
round(sysdate - tt.departuretime - 0.03125, 0),
'已交单',
round((select ot.tracktime
from ordertrack ot
where ot.orderid = o.orderid
and ot.eventtype = '客户签收') -
tt.departuretime - 0.03125,
0)) 逾期天数,
decode(o.dzt,
'未交单',
round(sysdate - tt.departuretime - 0.03125, 0),
'已交单',
round((select ot.tracktime
from ordertrack ot
where ot.orderid = o.orderid
and ot.eventtype = '客户签收') -
tt.departuretime - 0.03125,
0)) * 50 罚款金额
from orders o,
longdistancetask tdt,
transporttrip tt
-- dispatchvehicle dd
--EmployForeignVehicle ev
where o.pickupmode = '直送'
and o.clearingform = '签单'
and tdt.orderid = o.orderid
and tt.transporttripno = tdt.tripno
and decode(o.dzt,
'未交单',
round(sysdate - tt.departuretime - 0.03125, 0),
'已交单',
round((select ot.tracktime
from ordertrack ot
where ot.orderid = o.orderid
and ot.eventtype = '客户签收') - tt.departuretime -
0.03125,
0)) > 0
group by o.orderid, tt.licenseplateno,o.dzt,o.clearingform,o.dzt,tt.departuretime; end if;
end GetALLPaymentApply;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货