CREATE OR REPLACE PROCEDURE sp_ypjg_addcgd(ai_operid number,
as_frcode varchar2,
ai_forgid number) AS
/*
功能:下级单位申请的采购计划生成上级的采购汇总单。
名称:sp_ypjg_cgtj
参数: ai_operid 操作员
as_frcode 县区编码
ai_forgid 管理机构ID即当前操作员所在机构的ID
创建:XCK
创建时间:2012-05-24
*/
-- Create table
/*create global temporary table TMP_SQHZD
(
PRECID NUMBER,
SQSL NUMBER,
SPSL NUMBER,
UNIT VARCHAR2(10)
)
on commit delete rows;*/
v_requoffiid number;
v_requestid number;
v_requcode varchar2(10);
v_k number;
v_precid number;
v_mediid number;
v_sqsl number;
v_spsl number;
v_unit varchar2(10);
v_storesplit number;
v_currdate date;
v_cnt number;
CURSOR cur_sqxx Is
select t.precid,t.sqsl,t.spsl,t.unit
from tmp_sqhzd t;
BEGIN
v_currdate := sysdate;
--汇总药品于临时表
insert into tmp_sqhzd(PRECID,Sqsl,spsl,Unit)
select t3.precid,sum(t2.requnum) as sqsl,sum(t2.spsl),t2.unit
from request_total t1,request t2,dictmedi t3
where t1.requestid = t2.requestid
and t2.mediid = t3.mediid
and t1.forgid = t2.forgid
and t1.frcode = t2.frcode
and t2.forgid = t3.forgid
and t2.frcode = t3.frcode
and t1.stage = 1
and t1.retu = 0
and t1.forgid in(select gx_forgid from ypjg_gxjg where forgid = ai_forgid)
group by t3.precid,t2.unit;
select count(*) into v_cnt
from tmp_sqhzd;
if v_cnt = 0 then
raise_application_error(-20001,'没有待汇总的采购信息.');
return;
end if;
--取当前操作员科室
select officeid into v_requoffiid
from dictoffice
where officeid in(select officeid from dictoper where operid = ai_operid and forgid = ai_forgid)
and forgid = ai_forgid;
--写主记录表
select s_request_total.nextval into v_requestid from dual;
select count(*) into v_cnt from request_total where forgid = ai_forgid;
if v_cnt = 0 then
v_requcode := '00000001';
else
select trim(to_char(to_number(max(requcode))+1,'00000000'))
into v_requcode
from request_total where forgid = ai_forgid and substr(requcode,1,1)<>'F';
end if;
insert into request_total(REQUESTID,
REQUCODE,
REQUTRANID,
REQUDATE,
REQUOFFICE,
BASEID,
REMARK,
INPUTOR,
STAGE,
RETU,
RETUOPERID,
RETUDATE,
FORGID,
FRCODE)
values(v_requestid,
v_requcode,
18,--采购申请
v_currdate,
v_requoffiid,
v_requoffiid,
'生成采购汇总',
ai_operid,
1,
0,
null,
null,
ai_forgid,
as_frcode);
--循环写明细记录
v_k := 1;
OPEN cur_sqxx;
LOOP
FETCH cur_sqxx into v_precid,v_sqsl,v_spsl,v_unit;
EXIT WHEN cur_sqxx%notfound;
select mediid,store_split into v_mediid,v_storesplit from dictmedi_public
where forgid = ai_forgid and frcode = as_frcode and precid = v_precid;
--写明细表
insert into request( REQUESTID,--1
ORDERID,--2
MEDIID,--3
REQUNUM,--4
SPSL,--5
UNIT,--6
ACTPRI,--7
RETPRI,--8
PCID,--9
PROID,--10
CARRID,--11
YXQ,--12
PH,--13
YPDJID,--14
ZBID,--15
ZBDJ,--16
MIN_NUM,--17
FORGID,--18
FRCODE,--19
ISDEL,--20
ISDY,--21
REMARK)
values( v_requestid,--1
v_k,--2
v_mediid,--3
v_sqsl,--4
v_spsl,--5
v_unit,--6
0,--7
0,--8
null,--9
null,--10
null,--11
null,--12
null,null,null,null,
v_spsl*v_storesplit,--17
ai_forgid,--18
as_frcode,--19
null,null,null);
v_k := v_k + 1;
END LOOP;
--更新受理标识
update request_total
set retu = 1,
retuoperid=ai_operid,
retudate = v_currdate,
hz_requestid = v_requestid
where forgid in(select gx_forgid from ypjg_gxjg where forgid = ai_forgid);
End;
<parameterMap id="busGenProcedureFormParam" class="java.util.HashMap">
<parameter property="OPERID" jdbcType="NUMBER" mode="IN"/>
<parameter property="FRCODE" jdbcType="varchar" mode="IN"/>
<parameter property="FORGID" jdbcType="NUMBER" mode="IN"/>
</parameterMap>
<procedure id="busGenProcedureForm" parameterMap="busGenProcedureFormParam">
{call sp_ypjg_addcgd(?,?,?)}
</procedure>