sp_damedicineexecute.sql

来自「一整套的源代码」· SQL 代码 · 共 99 行

create or replace procedure sp_damedicineexecute
(
  l_inpatientid in integer,
  l_stockoutorderid in integer,
  l_reckoningid in integer,
  l_createby in integer
)
is
  cursor c is select * from damedicine where inpatientid = l_inpatientid and isactive = 0;
  r damedicine%rowtype;
  r_psm pstockmedicine%rowtype;
  r_dam damedicine%rowtype;
  l_medicineid integer;
  l_departmentid integer;
  l_pstockoutorderid integer;
begin
  for r in c loop
    select * into r_dam from damedicine where damedicineid = r.damedicineid;

    if round(trunc(sysdate, 'hh24') - trunc(r_dam.executed, 'hh24'), 1) < 0.5 then
      raise_application_error(-20001, '12小时内不能重得执行医嘱');
    end if;

    select * into r_psm from pstockmedicine where pstockmedicineid = r_dam.pstockmedicineid;
    select medicineid into l_medicineid from medicinestock where medicinestockid = r_psm.medicinestockid;
    select departmentid into l_departmentid from employee where employeeid = l_createby;

    insert into rmedicine
    (
      rmedicineid,
      isactive,
      price,
      total,
      amount,
      reckoningid,
      pstockmedicineid,
      medicineid
    )
    values
    (
      null,
      0,
      r_psm.price,
      r_psm.price * r_dam.amount * r_dam.daytimes,
      r_dam.amount * r_dam.daytimes,
      l_reckoningid,
      r_dam.pstockmedicineid,
      l_medicineid
    );
    
    select pstockoutorder_id.nextval into l_pstockoutorderid from dual;

    insert into pstockoutorder
    (
      pstockoutorderid,
      total,
      taxrate,
      amount,
      isactive,
      isvalid,
      isapprove,
      approvedate,
      created,
      updated,
      checkoutdate,
      pstockmedicineid,
      createby,
      updateby,
      approveby,
      price,
      departmentid
    )
    values
    (
      l_pstockoutorderid,
      0,
      null,
      r_dam.amount * r_dam.daytimes,
      0,
      0,
      0,
      null,
      sysdate,
      sysdate,
      sysdate,
      r_psm.pstockmedicineid,
      l_createby,
      l_createby,
      null,
      r_psm.price,
      l_departmentid
    );
    update damedicine set executed = sysdate where damedicineid = r_dam.damedicineid;
    update damedicine set stopdate = sysdate, stopby = l_createby, isactive = 1 where damedicineid = r_dam.damedicineid and istemporary = 0;
  end loop;
end;
/