--order 502497

create or replace procedure update_distrib_point is
  idprintque          printqueue.printqueue_id%type;
  customerlinkid1     customer_link.customer_link_id%type;
  customerlinkid2     customer_link.customer_link_id%type;
  distributionpointid distributionpoint.distributionpoint_id%type;
BEGIN
  DECLARE
    CURSOR cc IS
      select pq.printqueue_id,
             pq.customer_link_id as pqcust,
             dptl.customer_link_id as dptlcust
        from printqueue pq
        left outer join(weareremployment_link wel
        join(distributionpoint_link dpl
        left outer join distributionpoint dp on dpl.distributionpoint_id =
                                                dp.distributionpoint_id
        left outer join department_link dptl on dpl.department_link_id =
                                                dptl.department_link_id) on dpl.distributionpoint_link_id = wel.distributionpoint_link_id) on pq.weareremployment_link_id = wel.weareremployment_link_id
       where pq.distributionpoint_link_id is null
         and pq.invoiceheader_id is null
       order by pq.printqueue_id;
  begin
    open cc;
    loop
      FETCH cc
        INTO idprintque, customerlinkid1, customerlinkid2;
      if (customerlinkid2 is not null) then
        select min(dp.distributionpoint_id)
          into distributionpointid
          from distributionpoint dp, department dt, customer_link cl
         where cl.customer_link_id = customerlinkid2
           and dp.department_id = dt.department_id
           and dt.customer_id = cl.customer_id;
      else
        if (customerlinkid1 is not null) then
          select min(dp.distributionpoint_id)
            into distributionpointid
            from distributionpoint dp, department dt, customer_link cl
           where cl.customer_link_id = customerlinkid1
             and dp.department_id = dt.department_id
             and dt.customer_id = cl.customer_id;
        else
          distributionpointid := null;
        end if;
      end if;
      if (distributionpointid is not null) then
        update printqueue pq
           set pq.distributionpoint_link_id = distributionpointid
         where pq.printqueue_id = idprintque;
      end if;
      commit;
      EXIT WHEN cc%NOTFOUND;
    end loop;
   -- commit;
    close cc;
  end;
END update_distrib_point;
/

execute update_distrib_point;

drop procedure update_distrib_point;