CREATE OR REPLACE PROCEDURE sp_ypjg_cgdcx( ai_requestid number,
ai_forgid number,
out_cur out his_zyjs.ref_cur) AS
/*
功能：采购单查询(包括上下级的查询)。
名称：sp_ypjg_cgdcx
参数: ai_requestid 申请单ID，ai_forgid 传入的机构ID
创建：XCK
创建时间：2012-05-24
*/
v_type varchar2(2);
v_orglevel varchar2(2);
BEGIN
select t.type,t.orglevel into v_type,v_orglevel from sys_organize_info t where t.id = ai_forgid;
if v_type = '03' and v_orglevel = '04' then
OPEN out_cur for
select t1.requcode,--申请单号
t2.orderid,--序号
t3.medicode,--项目编码
t3.mediname||'['||t3.spec||']' as mediname,--项目名称
t2.unit,--单位
t2.requnum as sqsl,--申请数量
t2.spsl,--审批数量
pkg_bshis_common.F_GET_ADD('dictoper',t1.inputor,'opername') as sqr,--申请人
t1.requdate as sqrq--申请日期
from request_total t1,request t2,dictmedi_public 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.requestid = ai_requestid
and t1.forgid = ai_forgid;
elsif v_type = '03' and v_orglevel = '05' then
OPEN out_cur for
select t1.requcode,
t2.orderid,
t3.medicode,
t3.mediname||'['||t3.spec||']' as mediname,
t2.unit,
t2.requnum as sqsl,
t2.spsl,
pkg_bshis_common.F_GET_ADD('dictoper',t1.inputor,'opername') as sqr,
t1.requdate as sqrq
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.requestid = ai_requestid
and t1.forgid = ai_forgid;
end if;
End;
sql语句
<sqlMap>
<resultMap class="java.util.HashMap" id="out_ResultMap">
<result column="requcode" property="requcode" jdbcType="VARCHAR" />
<result column="orderid" property="orderid" jdbcType="INTEGER" />
<result column="medicode" property="medicode" jdbcType="VARCHAR" />
<result column="mediname" property="mediname" jdbcType="VARCHAR" />
<result column="unit" property="unit" jdbcType="VARCHAR" />
<result column="sqsl" property="sqsl" jdbcType="DECIMAL" />
<result column="spsl" property="spsl" jdbcType="DECIMAL" />
<result column="sqr" property="sqr" jdbcType="VARCHAR" />
<result column="sqrq" property="sqrq" jdbcType="VARCHAR" />
</resultMap>
<parameterMap id="busPrintProcedureParam" class="java.util.HashMap">
<parameter property="REQUESTID" jdbcType="NUMBER" mode="IN"/>
<parameter property="FORGID" jdbcType="NUMBER" mode="IN"/>
<parameter jdbcType="ORACLECURSOR" mode="OUT"
property="DATA" javaType="java.sql.ResultSet" resultMap="out_ResultMap"/>
</parameterMap>
<procedure id="busPrintProcedure" parameterMap="busPrintProcedureParam">
{call sp_ypjg_cgdcx(?,?,?)}
</procedure>
</sqlMap>