CREATE OR REPLACE PROCEDURE SP_YK_RKXXSC
(
  PWBDJH0  in     varchar2             ,          --供应商码上付上的单据号(F.K = MSJFRZ.BILL_CODE)
  PGHDWNM  in     number               ,          --供货单位内码
  PCZY000  in     number               ,          --操作员
  PCZYKS0  in     number               ,          --操作员科室
  PRKDH00  out    varchar2             ,          --入库单号
  Pcommit  in     varchar2 default 'N' ,          --是否提交
  Pzxcgbz  out    number               ,          --执行成功标志 1成功 0失败
  Ptsxx00  out    varchar2             ,          --执行结果提示信息，成功的情况下也可能有警告信息
  Pxxxx00  out    varchar2             ,          --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标
  PYRKDH0  in     varchar2   default ''           --入库确认、入库单查询补刷箱码PYRKDH0不为空已生成
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- linshu      2025.01.17  create  药库入库信息自动生成(阿里码上付接口) for YK9-20250117-001
-- huangjy     2025.03.18  增加PYRKDH0 已手动生成入库单号 for YK-20250317-001
-- huangjy     2025.03.26  增加BCRKID 字段区分不同批次相同药品 for YK-20250325-002
-- huangjy     2025.03.26  修正入录日期长度 for YK-20250326-001
-- linshu      2025.08.11  修正供应商值：From_USER_NAME改为Product_ENT_NAME; for YK9-20250618-001
-- linshu      2025.09.11  修正生产厂家没有取值问题及yk_rkmxx没生成时，数量生成问题; for    YK9-20250905-002
-- linshu      2025.10.24  支持多单合并生成数据; for    YK9-20250905-001
-- linshu      2025.12.02  支持下载下来的码未匹配药典表中的批准文号也能生成数据; for YK9-20251128-001
--             2026.3.23   入库明细药品顺序按接口下载顺序，批准文号去空格，入库数量按生产批号不同取值
as
  Vsysdate    date;                                --日期变量
  --Vcounter    number(14,4);                      --计数器变量
  Ecustom     exception;                           --错误变量
  LS_YPRKPC   YK_YPRKMX.YPRKPC%type;               --药品入库批次
  LS_RKLS00   YK_YPRKMX.RKSL00%type;               --药品入库数量
  LS_RKLXBH   BM_YKRKLX.RKLXBH%type;               --药品入库类型编号
  LS_RKLXMC   BM_YKRKLX.RKLXMC%type;               --药品入库类型名称
  iCNT        number(8);
  iRKD        number(2);
  ls_sccjbh   YK_YPRKMX.Sccjbh%type;
  V_ERROR     varchar2(1000);
begin
  Pzxcgbz:=0;
  Vsysdate:=sysdate;
  V_ERROR := 'a';
  select count(1) into iRKD from YK_YPRKD0 where RKDH00= PYRKDH0;
  select lengthb(to_char(PWBDJH0)) into V_ERROR from dual;
  --V_ERROR := PWBDJH0;
  --Pxxxx00 :=nvl(to_char(PWBDJH0),'null')||','||nvl(to_char(PCZY000),'null')||','||nvl(to_char(PCZYKS0),'null');
  --SP_TransLog(Vsysdate,'SP_YK_RKXXSC',Pczy000,Pczyks0,Pxxxx00);
  V_ERROR := 'b';
  select count(1) into iRKD from YK_YPRKD0 where RKDH00= PYRKDH0;
  select lengthb(to_char(PWBDJH0)) into V_ERROR from dual;
  V_ERROR := 'c';
  if iRKD=0 then
    --生成YK_YPRKD0
    SP_YK_YPRKD0_EDIT(0,PRKDH00,PCZYKS0,null,PGHDWNM,'01',null,0,0,
                               null,to_char(Vsysdate,'yyyymmdd'),to_char(Vsysdate,'hh24:mi:ss'),
                               null,Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00);
  else
    PRKDH00:=PYRKDH0;
    select count(1) into iCNT from YK_RKMXX0 where BILL_CODE in ( to_char(PWBDJH0) );
    select RKSL00 into LS_RKLS00 from YK_YPRKMX where RKDH00=PRKDH00 and rownum=1;
    if LS_RKLS00<iCNT then
       Ptsxx00 := '箱内追溯码数量大于入库单的数量!';
       raise Ecustom;
    end if;
  end if;
  V_ERROR := 'd';
  --update YK_YPRKD0 set WBRKDH = PWBDJH0 where RKDH00 = PRKDH00;
  select A.RKLXBH,A.RKLXMC into LS_RKLXBH,LS_RKLXMC from BM_YKRKLX A,YK_YPRKD0 B where A.RKLXBH = B.RKLXBH and B.RKDH00 = PRKDH00;
  V_ERROR := 'e';
  for c in ( SELECT distinct
              SUBSTR(
                REGEXP_SUBSTR(your_column, '[^,]+', 1, LEVEL),
                1,
                LENGTH(REGEXP_SUBSTR(your_column, '[^,]+', 1, LEVEL))
              ) AS value
            FROM
              (SELECT to_char(PWBDJH0) AS your_column FROM dual)
            CONNECT BY
              REGEXP_SUBSTR(your_column, '[^,]+', 1, LEVEL) IS NOT null )
  loop
    --生成入库明细
    for YPRKMX in ( select A.BCRKID,B.YPNM00,B.YPMC00,B.YPGG00,B.KCDW00,B.K2J000,B.GJJ000,B.LSJ000,B.PFJ000,A.PRODUCE_BATCH_NO,
                          substr(REPLACE(A.Valid_End_Date, '-', ''),1,8) YPSXRQ,A.Product_ENT_NAME,B.SFZBYP,B.ZBXH00,B.YPJCL0,A.APPROVAL_NO,
                          substr(REPLACE(A.PRODUCE_DATE,'-', ''),1,8) SCRQ00,B.JLDW00,B.K2ZJJL,substr(REPLACE(A.STORE_OUT_DATE,'-', ''),1,8) FPRQ00
                          from YK_MSJFRZ A,BM_YD0000 B where ( A.YPNM00 = B.YPNM00 or trim(A.APPROVAL_NO) = B.PZWH00 ) and A.BILL_CODE = c.value and B.TZRQ00 is null order by a.bcrkid )
                          loop
      LS_YPRKPC := null;
      ls_sccjbh := null;
      --lxl 根据ypnm00匹配字典上的生产厂家，匹配不到，根据下载的生产厂家取sccjbh
      if YPRKMX.YPNM00 <> 0 then
         select MAX(yd.sccjbh) into ls_sccjbh from bm_yd0000 yd  where yd.ypnm00 = YPRKMX.YPNM00;
      end if;

      if ls_sccjbh is null and YPRKMX.Product_ENT_NAME is not null then
         select MAX(SCCJBH) into ls_sccjbh from BM_YPSCCJ  where SCCJZW= YPRKMX.Product_ENT_NAME and ZFBZ00='0';
      end if;

      if iRKD=0 then
       /* select count(1) into LS_RKLS00 from YK_RKMXX0 where BILL_CODE = c.value and YPNM00 = YPRKMX.Ypnm00; --and BCRKID = YPRKMX.BCRKID;
        if LS_RKLS00 = 0 then*/
           select sum(PKG_AMOUNT) into LS_RKLS00 from Yk_Msjfrz where BILL_CODE = c.value and PRODUCE_BATCH_NO=YPRKMX.PRODUCE_BATCH_NO and (YPNM00 = YPRKMX.Ypnm00 or APPROVAL_NO = YPRKMX.APPROVAL_NO ) ;
        --end if;
        SP_YK_YPRKMX_EDIT(
           Popflag => 0,                              --操作标志 0：增加  1：修改  2：删除
           Prkdh00 => PRKDH00,                        --入库单号
           Pyprkpc => LS_YPRKPC,
           Pfph000 => null,
           Pfpflh0 => null,
           Pypnm00 => YPRKMX.YPNM00,
           Pypmc00 => YPRKMX.YPMC00,
           Pypgg00 => YPRKMX.YPGG00,

           Prkdw00 => YPRKMX.KCDW00,
           Pzhl000 => YPRKMX.K2J000,
           Prksl00 => nvl(LS_RKLS00,0),
           Pfpgjdj => YPRKMX.GJJ000,
           Pkl0000 => 1,
           Plsdj00 => YPRKMX.LSJ000,
           Ppfdj00 => YPRKMX.PFJ000,
           Pscph00 => YPRKMX.PRODUCE_BATCH_NO,

           Pypsxrq => YPRKMX.YPSXRQ,
           Psccjbh => ls_sccjbh,
           Psccjzw => null,
           Pcfwzbh => null,
           Pcfwzmc => null,
           Pbz0000 => null,
           Pjhrq00 => null,
           Psfyhgz => null,

           Pwgqk00 => '0',
           Psfzbyp => YPRKMX.SFZBYP,
           Pzbxh00 => YPRKMX.ZBXH00,
           Pypjcl0 => YPRKMX.YPJCL0,
           Pzbqnm0 => null,
           Ppzwh00 => YPRKMX.APPROVAL_NO,
           Pzkje00 => null,
           Pfprq00 => YPRKMX.FPRQ00,                          --发票日期

           Pypzcz0 => null,
           Pscrq00 => YPRKMX.SCRQ00,
           Pcdid00 => null,
           Pcdmc00 => null,
           Pczy000 => Pczy000,
           Pczyks0 => Pczyks0,
           Pcommit => 'N',
           Pzxcgbz => Pzxcgbz,

           Ptsxx00 => Ptsxx00,
           Pxxxx00 => Pxxxx00,
           Psfxytj => 'N',
           Pxxlsdj => 0,
           Pypthpc => 0,
           Pcxbz00 => 'Z',
           Pcxrkpc => 0,
           Pjlrksl => 0,
           Pjlrkdw => YPRKMX.JLDW00,

           Pjlzhl0 =>YPRKMX.K2ZJJL
           );

        update YK_MSJFRZ A set A.YPRKPC = LS_YPRKPC where A.YPNM00 = YPRKMX.YPNM00 and A.BILL_CODE = c.value and BCRKID = YPRKMX.BCRKID;
        if Pzxcgbz = 0 then
          Ptsxx00 := '药品入库明细操作错误!';
          raise Ecustom;
        end if;
      else
        --已经输入入库明细
        select nvl(max(YPRKPC),0) into LS_YPRKPC from YK_YPRKMX
        where YPNM00=YPRKMX.YPNM00 and RKDH00=PRKDH00 and rownum=1;

        update YK_MSJFRZ A set A.YPRKPC =LS_YPRKPC
        where A.YPNM00 = YPRKMX.YPNM00 and A.BILL_CODE = to_char(PWBDJH0) and BCRKID = YPRKMX.BCRKID;

        update YK_YPRKMX a set
          a.SCRQ00=YPRKMX.SCRQ00,
          a.YPSXRQ=YPRKMX.YPSXRQ,
          a.PZWH00=YPRKMX.APPROVAL_NO,
          a.SCPH00=YPRKMX.PRODUCE_BATCH_NO
        where a.YPRKPC=LS_YPRKPC and a.YPNM00=YPRKMX.YPNM00;
      end if;
      --生成入库追溯码信息
  /*    for VRKZSM0 in ( select code from YK_RKMXX0 where BILL_CODE = PWBDJH0 and YPNM00 = YPRKMX.YPNM00 and BCRKID = YPRKMX.BCRKID) loop
        SP_YK_RKZSM0_EDIT( 0,'08',PRKDH00,LS_YPRKPC,VRKZSM0.CODE,LS_RKLXBH,LS_RKLXMC,PCZY000,
                           PCZYKS0,'N',Pzxcgbz,Ptsxx00 );
        if Pzxcgbz = 0 then
          --Ptsxx00 := '药品入库追溯码生成错误!';
          raise Ecustom;
        end if;
      end loop;*/
    end loop;
  end loop;

  Pzxcgbz:=1;
  if pcommit='Y' then
     commit;
  end if;
exception
  when No_Data_Found then
    rollback;
    raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm||Ptsxx00,1,240));
  when Ecustom       then
    rollback;
    raise_application_error(-20010,substrb(Ptsxx00||'！*',1,240));
  when others        then
    rollback;
    raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240));
end;