CREATE OR REPLACE PROCEDURE SP_SST_YPYJFY_QUERY_YKT (
    P_ZDBH00      in varchar2,  --入参：终端编号
    P_YPYJSZM     in varchar2,  --药品或者医技的首字母
    P_XMLXBH      in varchar2,  --0为药品，1为医技,2制剂--> 1为药品，2为医技 3耗材 4制剂
    P_YYJGDM      in varchar2,  --入参：医院机构代码(总院、分院)
    P_QSYM00      in varchar2,  --起始页
    P_MYJLS0      in varchar2,  --每页记录数
    P_ZJLS00      out varchar2, --总记录数
    P_ERRMSG      out varchar2 --出错信息
    --p_CURSOR      out pkg_fee_qry.MY_CURSOR --结果集游标
)
as
-- MODIFICATION HISTORY
-- Person      Date          Comments
-- qks         2013.04.19    create;
-- qks         2015.12.21    增加本院制剂：P_XMLXBH=2,P_XMLXBH=0剔除耗材
--zhangyc      2017.01.08    v1.24
-- dsm         2017.04.07    泉二P_MYJLS0原来要从0开始传才正确,实际应该是1开始,加LS_MYJLS0修正
--zhangyc      2018.01.31    去掉p_CURSOR，改为插入中间表SST_YPYJFY_QUERY （V2.04）
  LS_ROWNUM  number(10); --记录数
  LS_PYSM00 Varchar2(50);
  LS_MYJLS0  Varchar2(10);  --P_MYJLS0原来要从0开始传才正确,现在加这个
begin
    select  SF_YJ_GETPYSM(trim(P_YPYJSZM)) into LS_PYSM00  from  dual;
    LS_PYSM00:=LS_PYSM00||'%';
     LS_MYJLS0:=to_char(to_number(P_MYJLS0)-1);
     delete from SST_YPYJFY_QUERY  where ZDBH00=P_ZDBH00;
     if P_XMLXBH = '1' then
         select count(1) into LS_ROWNUM from BM_YD0000 a where pysm00 like LS_PYSM00
            and a.tzrq00 is null and a.QYRQ00 is not null and a.lsj000>0 and a.lbbh00<>9;
         P_ZJLS00 := to_char(ls_rownum);
     --open p_CURSOR for
     insert into SST_YPYJFY_QUERY(ZDBH00,XMLB00,PLXH00,XMMC00,XMGG00,XMDW00,XMDJ00)
     select P_ZDBH00,XMLB00,XH0000,NAME00 as XMMC00,STANDARD as XMGG00,XMDW00, PRICE0 as XMDJ00 from
     (
            select rownum XH0000,
            (select  LBMC00 from BM_YPLB00 WHERE aa.LBBH00=LBBH00) XMLB00,--药品类别名称
            aa.YPMC00 name00,     --药品或者医技名称
            aa.YPGG00 standard,     --药品或者医技的规范
            aa.kcdw00 XMDW00,      --药品或者医技单位
            trim(to_char(aa.LSJ000,'99999990.99'))||'元/'||trim(aa.KCDW00) price0 --价格
       from
      (select a.* from bm_yd0000 a where a.pysm00 like LS_PYSM00
          and a.tzrq00 is null and a.QYRQ00 is not null
          and a.lsj000>0 and a.lbbh00<>9 order by ypbm00) aa
      ) bb
      where bb.XH0000>((to_number(P_QSYM00)-1)*to_number(P_MYJLS0))
        and bb.XH0000<=(to_number(P_QSYM00)* to_number(P_MYJLS0));

     elsif P_XMLXBH = '4' then
         select count(1) into ls_rownum from bm_yd0000 a where pysm00 like LS_PYSM00
            and tzrq00 is null and QYRQ00 is not null and lsj000>0 and ZJBZ00='1';
         P_ZJLS00 := to_char(ls_rownum);

      --open p_CURSOR for
      insert into SST_YPYJFY_QUERY(ZDBH00,XMLB00,PLXH00,XMMC00,XMGG00,XMDJ00)
      select P_ZDBH00,XMLB00,XH0000,NAME00 as XMMC00,STANDARD as XMGG00, PRICE0 as XMDJ00  from
      (
        select    rownum XH0000,
            (select  LBMC00 from BM_YPLB00 WHERE aa.LBBH00=LBBH00) XMLB00, --药品类别名称
            aa.YPMC00 name00,     --药品或者医技名称
            aa.YPGG00 standard,     --药品或者医技的规范
            aa.kcdw00 XMDW00,      --药品或者医技单位
            trim(to_char(aa.LSJ000,'99999990.99'))||'元/'||trim(aa.KCDW00) price0 --价格
       from
      (select a.* from bm_yd0000 a where a.pysm00 like LS_PYSM00
      and a.tzrq00 is null and a.QYRQ00 is not null
      and a.lsj000>0 and a.ZJBZ00='1' order by a.ypbm00) aa
     ) bb
      where bb.XH0000>((to_number(P_QSYM00)-1)*to_number(P_MYJLS0))
        and bb.XH0000<=(to_number(P_QSYM00)* to_number(P_MYJLS0));
   elsif P_XMLXBH  in ( '2','3') then
         select count(1) into ls_rownum from bm_yysfxm a where a.pysm00 like LS_PYSM00 and a.bmgzid='1' and a.FLAG00='1' and a.SFJE00>0 and a.TZRQ00 IS NULL and a.JSXM00<>'2';
         P_ZJLS00 := to_char(ls_rownum);
    --open p_CURSOR for
    insert into SST_YPYJFY_QUERY(ZDBH00,XMLB00,PLXH00,XMMC00,XMGG00,XMDW00,XMDJ00)
     select P_ZDBH00,XMLB00,PLXH00,NAME00 as XMMC00,STANDARD as XMGG00,XMDW00, PRICE0 as XMDJ00  from
     (
     select rownum PLXH00,
           null XMLB00, --项目类别名称
           aa.XMMC00 name00,     --药品或者医技名称
            trim(aa.DW0000) standard,   --药品或者医技的规范
            aa.DW0000 XMDW00,      --药品或者医技单位
            trim(to_char(aa.SFJE00,'99999990.99'))||'元' price0 --价格
       from
      (select a.* from bm_yysfxm a where a.pysm00 like LS_PYSM00 and a.bmgzid='1' and a.FLAG00='1' and a.SFJE00>0 and a.TZRQ00 IS NULL and a.JSXM00<>'2'
          order by bh0000) aa
      ) bb
      where bb.PLXH00>((to_number(P_QSYM00)-1)*to_number(P_MYJLS0))
        and bb.PLXH00<=(to_number(P_QSYM00)* to_number(P_MYJLS0));


   end if;
exception
  when no_data_found then
      P_ERRMSG:= substr(SQLERRM||'药品或医技项目查询失败！',1,255);
      --ROLLBACK;
   when others then
      P_ERRMSG:= substr(SQLERRM||'药品或医技项目查询失败！',1,255);
      --ROLLBACK;
end;