sp_damedicineexecute2.sql

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

create or replace procedure sp_damedicineexecute2
(
  l_inpatientid 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
  select * into r_dam from damedicine where damedicineid = r.damedicineid;
  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;
/