create or replace procedure SP_JY_CAJYJG
(   PYBH000 in  varchar2,
    PPYRQ00 in  varchar2,
    PLX0000 in  char  --(0普通标本，1细菌标本)
)as
--Person         Date        Comment
--Nixj           2019.10.22  create for LIS9-20191022-001
--chenhn         2022.01.13  增加医E签签名数据  for     LIS-20220117-001
  PXML000   clob;
  EXTIOFO   clob; 
    VBRID00   JY_YW0000.BRID00%type;
    VZYGHID   JY_YW0000.ZYGHID%type;
    VZYHGHH   JY_YW0000.ZYHGHH%type;
    VBRXM00   JY_YW0000.BRXM00%type;
    VYEXM00   JY_YW0000.YEXM00%type;
    VXBMC00   varchar2(10);
    VCSRQ00   JY_YW0000.CSRQ00%type;
    VNL0000   varchar2(10);
    VZDID00   JY_YW0000.ZDID00%type;
    VZDMC00   varchar2(50);
    VBRLX00   varchar2(10);
    VSQRQ00   JY_YW0000.SQRQ00%type;
    VSQSJ00   JY_YW0000.SQSJ00%type;
    VSQKSMC   BM_BMBM00.BMMC00%type;
    VSQYSXM   BM_YGBM00.ZWXM00%type;
    VCYRQ00   JY_YW0000.BBCYRQ%type;
    VBBCYSJ   JY_YW0000.BBCYSJ%type;
    VCJRXM0   BM_YGBM00.ZWXM00%type;
    VBBSDRQ   JY_YW0000.BBSDRQ%type;
    VBBSDSJ   JY_YW0000.BBSDSJ%type;
    VSDRXM0   BM_YGBM00.ZWXM00%type;
    VPYSJ00   JY_YW0000.PYSJ00%type;
    VCBGRQ0   JY_YW0000.CBGRQ0%type;
    VCBGSJ0   JY_YW0000.CBGSJ0%type;
    VBGRXM0   BM_YGBM00.ZWXM00%type;
    VBGJYR0   JY_YW0000.BGJYR0%type;
    VJYRXM0   BM_YGBM00.ZWXM00%type;
    VBBMC00   varchar2(30);
    VBBSM00   JY_YW0000.BBSM00%type;
    flag      number(10);
    cursor CUR_JYJG is select JGID00,BGXMID,BGXMMC,BZBM00,ZWMC00,YWMC00,CKXX00,CKSX00,CKFW00,JYJG00,JGDW00,YQDH00,YQMC00,JYFF00
                         from VW_JY_JYJG00_TEMP where PYRQ00=PPYRQ00 and YBH000=PYBH000;
    cursor CUR_XJJG is select BGJGID,XJID00,(select ZWMC00 from JY_XJZD00 where XJID00=A.XJID00) XJSJMC,KSSID0,(select ZWMC00 FROM JY_KSSZD0 where KSSID0=a.KSSID0) KSSJMC,YMJG00,YMSM00,YMJGKB,YJND00,YJHZJ0,YMSL00,YMJGNE,SFCJJZ,JLS000,SFDCNY,XJNYJC,SFXWR0
                         from JY_XJBGJG a where PYRQ00=PPYRQ00 and YBH000=PYBH000;
begin
    select BRID00,ZYGHID,ZYHGHH,BRXM00,YEXM00,XBMC00,CSRQ00,NL0000,ZDID00,ZDMC00,BRLX00,
    SQRQ00,SQSJ00,SQKSMC,SQYSXM,BBCYRQ,BBCYSJ,CJRXM0,BBSDRQ,BBSDSJ,SDRXM0,
    PYSJ00,CBGRQ0,CBGSJ0,BGRXM0,JYRXM0,BBMC00,BBSM00,BGJYR0
    into
    VBRID00,VZYGHID,VZYHGHH,VBRXM00,VYEXM00,VXBMC00,VCSRQ00,VNL0000,VZDID00,VZDMC00,VBRLX00,
    VSQRQ00,VSQSJ00,VSQKSMC,VSQYSXM,VCYRQ00,VBBCYSJ,VCJRXM0,VBBSDRQ,VBBSDSJ,VSDRXM0,
    VPYSJ00,VCBGRQ0,VCBGSJ0,VBGRXM0,VJYRXM0,VBBMC00,VBBSM00,VBGJYR0
    from VW_JY_YW0000  where PYRQ00=PPYRQ00 and YBH000=PYBH000 and rownum=1;
    PXML000:='PID|'||VBRID00||'|'||VZYGHID||'|'||VZYHGHH||'|'||VBRXM00||'|'||VYEXM00||'|'||VXBMC00||'|'||VCSRQ00||'|'||VNL0000||'|'||VZDID00||'|'||VZDMC00||'|'||VBRLX00||chr(13);
    PXML000:=PXML000||'OBR|'||PYBH000||'|'||PPYRQ00||'|'||VSQRQ00||'|'||VSQSJ00||'|'||VSQKSMC||'|'||VSQYSXM||'|'||VCYRQ00||'|'||VBBCYSJ||'|'||VCJRXM0||'|'||VBBSDRQ||'|'||VBBSDSJ||'|'||VSDRXM0||'|'||VPYSJ00||'|'||VCBGRQ0||'|'||VCBGSJ0||'|'||VBGRXM0||'|'||VJYRXM0||'|'||VBBMC00||'|'||VBBSM00||chr(13);
    flag:=1;
    if(PLX0000=0) then
        for V_JYJG in CUR_JYJG loop
            PXML000:=PXML000||'OBX|'||flag||'|'||V_JYJG.JGID00||'|'||V_JYJG.BGXMID||'|'||V_JYJG.BGXMMC||'|'||V_JYJG.BZBM00||'|'||V_JYJG.ZWMC00||'|'||V_JYJG.YWMC00||'|'||V_JYJG.CKXX00||'|'||V_JYJG.CKSX00||'|'||V_JYJG.CKFW00||'|'||V_JYJG.JYJG00||'|'||V_JYJG.JGDW00||'|'||V_JYJG.YQDH00||'|'||V_JYJG.YQMC00||'|'||V_JYJG.JYFF00||chr(13);
            flag:=flag+1;
        end loop;
    end if;
    if(PLX0000=1) then
        for V_XJJG in CUR_XJJG loop
            PXML000:=PXML000||'OBX|'||flag||'|'||V_XJJG.BGJGID||'|'||V_XJJG.XJID00||'|'||V_XJJG.XJSJMC||'|'||V_XJJG.KSSID0||'|'||V_XJJG.KSSJMC||'|'||V_XJJG.YMJG00||'|'||V_XJJG.YMSM00||'|'||V_XJJG.YMJGKB||'|'||V_XJJG.YJND00||'|'||V_XJJG.YJHZJ0||'|'||V_XJJG.YMSL00||'|'||V_XJJG.YMJGNE||'|'||V_XJJG.SFCJJZ||'|'||V_XJJG.JLS000||'|'||V_XJJG.SFDCNY||'|'||V_XJJG.XJNYJC||'|'||V_XJJG.SFXWR0||chr(13);
            flag:=flag+1;
        end loop;
    end if;
    
    EXTIOFO := EXTIOFO||'<?xml version="1.0" encoding="UTF-16"?>'||CHR(13);
    EXTIOFO := EXTIOFO||'<root>'||CHR(13);
    EXTIOFO := EXTIOFO||'<sign_sys>LIS</sign_sys>'||CHR(13);    
    EXTIOFO := EXTIOFO||'<title>检验数据</title>'||CHR(13);     
    EXTIOFO := EXTIOFO||'<patient_id>'||VBRID00||'</patient_id>'||CHR(13);  
    EXTIOFO := EXTIOFO||'<patient_name>'||VBRXM00||'</patient_name>'||CHR(13);
    EXTIOFO := EXTIOFO||'<emr_id></emr_id>'||CHR(13);       
    EXTIOFO := EXTIOFO||'<emr_catalog></emr_catalog>'||CHR(13);         
    EXTIOFO := EXTIOFO||'<his_id></his_id>'||CHR(13);       
    EXTIOFO := EXTIOFO||'<event_no>'||VZYHGHH||'</event_no>'||CHR(13);  
    EXTIOFO := EXTIOFO||'<visit_number></visit_number>'||CHR(13);       
    EXTIOFO := EXTIOFO||'<dept_name>'||VSQKSMC||'</dept_name>'||CHR(13);            
    EXTIOFO := EXTIOFO||'<prescribe_number>'||VZYGHID||'</prescribe_number>'||CHR(13);      
    EXTIOFO := EXTIOFO||'<prescribe_sub_number></prescribe_sub_number>'||CHR(13);               
    EXTIOFO := EXTIOFO||'<prescribe_operation></prescribe_operation>'||CHR(13); 
    EXTIOFO := EXTIOFO||'<radiotherapy_id></radiotherapy_id>'||CHR(13); 
    EXTIOFO := EXTIOFO||'<process_name></process_name>'||CHR(13);   
    EXTIOFO := EXTIOFO||'<process_time></process_time>'||CHR(13);           
    EXTIOFO := EXTIOFO||'<extend_value_1>备用扩展1</extend_value_1>'||CHR(13);      
    EXTIOFO := EXTIOFO||'<extend_value_2>备用扩展2</extend_value_2>'||CHR(13);      
    EXTIOFO := EXTIOFO||'<extend_value_3>备用扩展3</extend_value_3>'||CHR(13);          
    EXTIOFO := EXTIOFO||'<extend_value_4>备用扩展4</extend_value_4>'||CHR(13);  
    EXTIOFO := EXTIOFO||'<extend_value_5>备用扩展5</extend_value_5>'||CHR(13);      
    EXTIOFO := EXTIOFO||'<extend_value_6>备用扩展6</extend_value_6>'||CHR(13);      
    EXTIOFO := EXTIOFO||'<extend_value_7>备用扩展7</extend_value_7>'||CHR(13);      
    EXTIOFO := EXTIOFO||'<extend_value_8>备用扩展8</extend_value_8>'||CHR(13);      
    EXTIOFO := EXTIOFO||'<extend_value_9>备用扩展9</extend_value_9>'||CHR(13);  
    EXTIOFO := EXTIOFO||'</root>';      
  delete from JY_JGSJQM where YBH000=PYBH000 and PYRQ00=PPYRQ00;    
    insert into JY_JGSJQM(YBH000,PYRQ00,SHR000,SJQM00,QMSTR0,QZKZXX,QMZ000,QMZSSJ) values(PYBH000,PPYRQ00,VBGJYR0,PXML000,'',EXTIOFO,'','');
                
end;