-- Procedure PACS_AUTOREGISTER CREATE OR REPLACE PROCEDURE PACS_AUTOREGISTER ( cApplyNo in varchar, --医技单据号 cPhysNo in varchar, --体检号 cMedicalNo in varchar, --病人档案号 cName in varchar, --姓名 cSex in varchar, --性别 cBirthDate in char, --出生日期 cAge in varchar, --年龄 cExamClass in varchar, --检查类型 cReqDate in char, --开单日期 cReqTime in char, --开单时间 cNamePhonetic in varchar, --姓名拼音 cBirthPlace in varchar, --籍贯 cPhoneNumber in varchar, --电话号码 cReqDept in varchar, --登记科室 cReqPhysician in varchar, --登记人员 cPerformDept in varchar, --执行科室 cItemArray in varchar, --项目列表 格式为 单据号^项目名称| eg:123^B超肝|124^B超肾| cItemXmid in varchar, --同步报告项目ID iSuccess out number --成功标志 ) as nExamNo number(16); nLength number(2); cExamNo EXAMS.Exam_No%type; cServerDate char(10); cServerTime char(8); cCommonDir varchar(50); cCommonServer varchar(10); nItemNo number(2); nPair number; cPair varchar(200); nNo number; cNo varchar(200); nXmid varchar(50); cXmid varchar(20); nPrevXmid varchar(20); cItemCode varchar(10); cItemNode varchar(20); nId varchar(20); cItemName varchar(200); nPrevPair number; cSickID EXAM_PATIENT.SICK_ID%type; counter number; strTmp varchar(1000); strTmp1 varchar(1000); begin iSuccess := 0; if(cExamClass='体检放射' or cExamClass='体检超声') then --取Common_server,注意一定要设置好该检查类型对应的Common_Server begin select common_server into cCommonServer from SPACS.Dict_Upload_Server where Exam_Class = cExamClass; exception when others then return; end; select substr(SPACS.st_sysdate,1,10) , substr(SPACS.st_sysdate,12,8) into cServerDate,cServerTime from dual; --取Exam_no Select Current_Value ,Serial_Length into nExamNo, nLength From SPACS.Dict_Serial Where Serial_Name ='EXAM_NO'; Update SPACS.Dict_Serial set Current_value = Current_Value+1 Where Serial_Name ='EXAM_NO'; nExamNo := nExamNo+1; cExamNo := lpad(to_char(nExamNo),nLength,'0'); --Dbms_Output.put_line(cExamNo); --生成Common_dir cCommonDir := 'common\'||cExamClass||'\'||Replace(cServerDate,'-','')||'\'||cPhysNo||'_'||cExamNo; --Exams select count(*) into counter from SPACS.V_Exams where Pat_Local_ID=cPhysNo and instr('|'||cItemArray,'|'||Apply_No||'^')>0; if counter = 0 then Insert Into SPACS.V_Exams( Exam_No, Pat_Local_ID, Sick_ID, Name, Sex, Birth_Date, Age, Outpatient_No, Patient_Source, Exam_Class, Req_Dept, Req_Physician, Req_Date, Req_Time, Scheduled_Date, Scheduled_Time, Exam_Date, Exam_Time, Register, Perform_Dept, Exam_Status, Charge_Flag, Common_Dir, Common_Server, Apply_No, Register_Date, Register_Time) Values( cExamNo,'0'||cPhysNo,nvl('T'||SUBSTR(cMedicalNo,1,10),'T'||SUBSTR(cPhysNo,1,10)),cName,cSex,cBirthDate, cAge,cPhysNo, '体检',cExamClass, cReqDept,cReqPhysician ,cReqDate,cReqTime, cReqDate,cReqTime,cReqDate,cReqTime, '体检登记', cPerformDept ,'10','1', cCommonDir ,cCommonServer,cApplyNo,cServerDate,cServerTime); end if; --Exam_Patient select count(*) into counter from SPACS.exam_patient where sick_id = nvl('T'||SUBSTR(cMedicalNo,1,10),'T'||SUBSTR(cPhysNo,1,10)); -- Exception -- when NO_DATA_FOUND THEN if counter = 0 then Insert Into Exam_Patient( Sick_ID, Name, Name_Phonetic, Sex, Birth_Date, Birth_Place, Phone_Number) Values( nvl('T'||SUBSTR(cMedicalNo,1,10),'T'||SUBSTR(cPhysNo,1,10)),cName,cNamePhonetic,cSex,cBirthDate, cBirthPlace,cPhoneNumber); end if; nItemNo := 1; nPair := 1; nPrevPair := 0; loop nPair := instr(cItemArray,'|',1,nItemNo); if nPair = 0 then nPair := length(cItemArray); end if; cPair := substr(cItemArray,nPrevPair+1,nPair-nPrevPair-1); nNo := instr(cPair,'^'); cNo := substr(cPair,1,nNo-1); cItemName := substr(cPair,nNo+1); --插入Exam_Item Insert Into SPACS.Exam_Item(Exam_No, Item_Code,Item_No, Item_Name, Apply_No,Charge_Flag,Order_No) select cExamNo,cItemCode,nItemNo,cItemName,cNo,'1',cNo from dual; --where not exists(select 1 from Exam_Item where Apply_No=cNo); --插入Exam_Organ Insert Into SPACS.Exam_Organ(Exam_No, Organ_No, Organ_Name) select cExamNo,nItemNo-1,cItemName from dual; nItemNo := nItemNo+1; nPrevPair := nPair; exit when nPair >= length(cItemArray) or nPair =0; end loop; elsif (cExamClass='KCT' or cExamClass='KMR' or cExamClass='KRF' or cExamClass='体检内镜') then select count(*) into counter from SPACS.Exam_apply where apply_no=cApplyNo; if counter = 0 then INSERT INTO spacs.exam_apply (apply_no, sick_id, NAME, sex, birth_date,age,outpatient_no,patient_source,exam_class, req_dept,req_physician,req_date, req_time,perform_dept,charge_flag,phone_number,mailing_address) Values( cApplyNo, nvl('T'||SUBSTR(cMedicalNo,1,10),'T'||SUBSTR(cPhysNo,1,10)),cName,cSex,cBirthDate, cAge,cPhysNo, '体检',cExamClass, '191',cReqPhysician ,cReqDate,cReqTime, cPerformDept ,'1',cPhoneNumber,cBirthPlace); -- raise_application_error(-20001,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240)); nItemNo := 1; nPair := 1; nPrevPair := 0; nPrevXmid :=0; nXmid :=1; strTmp := cItemXmid; if substr(strTmp, length(strTmp), 1) <> '^' then strTmp := strTmp || '^'; end if; strTmp1 := cItemArray; if substr(strTmp1, length(strTmp1), 1) <> '|' then strTmp1 := strTmp1 || '|'; end if; while instr(strTmp1, '|') > 0 loop cPair := substr(strTmp1, 1, instr(strTmp1, '|') - 1); cItemName := substr(cPair, instr(cPair, '^') + 1, length(strTmp1)); cItemNode := substr(strTmp, 1, instr(strTmp, '^') - 1); begin select jcxmid into cItemCode from SD_HOSPITAL.SY_PE_YWZD_SQJCGL@tjk where SQXMID = cItemNode; exception when others then cItemCode:=null; end; INSERT INTO spacs.EXAM_APPLY_ITEM(APPLY_NO,ITEM_NO,ITEM_CODE,ITEM_NAME,CHARGE_FLAG) Values( cApplyNo, nItemNo, cItemCode, cItemName, '1'); strTmp := substr(strTmp, instr(strTmp, '^') + 1, length(strTmp)); strTmp1 := substr(strTmp1, instr(strTmp1, '|') + 1, length(strTmp1)); end loop; /* loop if strTmp = '' then end if; nPair := instr(cItemArray,'|',1,nItemNo); if nPair = 0 then nPair := length(cItemArray); end if; nXmid := instr(cItemXmid,'^',1,nItemNo); if nXmid =0 then nXmid := length(cItemXmid); end if; cPair := substr(cItemArray,nPrevPair+1,nPair-nPrevPair-1); nNo := instr(cPair,'^'); cNo := substr(cPair,1,nNo-1); cItemName := substr(cPair,nNo+1); cXmid := substr(cItemXmid,nPrevXmid+1,nXmid-nPrevXmid-1); nId := instr(cXmid,'^'); cItemNode := substr(cXmid,nId+1); begin select jcxmid into cItemCode from SD_HOSPITAL.SY_PE_YWZD_SQJCGL@tjk where SQXMID = cItemNode; exception when others then cItemCode:=null; end; INSERT INTO spacs.EXAM_APPLY_ITEM(APPLY_NO,ITEM_NO,ITEM_CODE,ITEM_NAME,CHARGE_FLAG) Values( cApplyNo, nItemNo, cItemCode, cItemName,'1'); nItemNo := nItemNo+1; nPrevPair := nPair; nPrevXmid := nXmid; exit when nPair >= length(cItemArray) or nPair =0; end loop; */ /* elsif iSuccess := 1;*/ end if ; end if; iSuccess := 1; exception when others then -- raise_application_error(-20001,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240)); iSuccess := 0; rollback; end PACS_AUTOREGISTER; / -- End of DDL script for PACS_AUTOREGISTER