一些应用

 

  update cdip_order_master_his set
                          /*cnvcAgentLevel = commerce_range(orderid,submittime),*/
                           if_watch_goods = l_if_watch_goods,
                             solution_blg = l_solution_blg,
                                 CNVCRES5 = l_service_bt,
                             other_desc_2 = l_remarks||' ' ||(select if_order_agent_change(orderid,to_date(submittime, 'yyyy-mm-dd hh24:mi:ss')) from dual), --备注 --如果总代更改，则在备注中增加
                          direct_shipment = l_direct,
                          FREIGHT_UTAKER = l_freight_utaker, --运保费承担方
                          FREIGHT_WAY = l_freight_way,     --运输方式
                          SALE_REGION = l_SALE_REGION,     --区域属性
                          agent_solution_blg = l_agent_solution_blg, --代理商业绩归属
                          bjhh = l_bjhh ----保驾护航信息
                          where spms_cdip_id = cdipid;

 

 

create or replace function if_order_agent_change( --用于判断改单的总代是否与上一次的一致
                                                 orderid    in varchar2,
                                                 submittime in date)
  return varchar2 is
  v_agent1 varchar2(4000); --当前总代
  v_agent2 varchar2(4000); --上一次通过的总代
begin
  select wm_concat(t.attestation_id)
    into v_agent1
    from spms_order_sellcust t
   where t.order_id = orderid
     and t.account_type = '1';

  select wm_concat(t.attestation_id)
    into v_agent2
    from spms_order_sellcust_his t
   where t.submit_time =
         (select max(t.submit_time)
            from spms_order_master_his t
           where t.order_id = orderid
             and t.submit_time != submittime
             and t.final_wf_status = 'Passed')
     and t.order_id = orderid
     and t.account_type = '1';
  if v_agent1 != v_agent2 then
    return '更改总代';
  else
    return '';
  end if;

end;