CREATE OR REPLACE 
PROCEDURE sp_hisjk_zhygchj
(
  pMZZYBZ in  varchar2,
  pZYHKH0 in varchar2,
  pKDKSBH in varchar2,
  pKDYS00 in varchar2,
  pZXKSBH in varchar2,
  pKSRQ00 in date,
  pZZRQ00 in date,
  pBRID00 in varchar2,
  pZXYS00 in varchar2,
  pZXDJH0 in varchar2,
  pZLXM00 in varchar2,
  pZLXMSL in float,
  PGCLB00 in varchar2, --0:提取检验单，1:检验单扣费和材料费用,2:补开材料费
  pRETRUN out varchar2,
  pMessage out varchar2
)
as
As_apply_no varchar2(100);
As_io_flag varchar2(2);
as_nullah_number varchar2(36);
As_sick_id varchar2(36);
As_dept_code varchar2(12);
as_doctor varchar2(12);
As_status varchar2(2);
As_settle_status varchar2(2);
As_bgdh varchar2(36);
As_bglx varchar2(36);
as_apply_dept_code varchar2(36);
as_apply_doctor varchar(12);
rs_return varchar2(2);
ad_start_time date;
ad_end_time date;
as_item_code varchar2(36);
adc_item_quantity number(5,0);
as_item_class varchar2(2);
adc_item_price number(5,2);
rs_mes_error varchar2(200);
as_class_flag varchar2(1);
begin
As_io_flag:=pMZZYBZ;
as_nullah_number:=pZYHKH0;
As_sick_id:=pBRID00;
As_dept_code:=pZXKSBH;
as_doctor:=pZXYS00;
As_apply_no:=pZXDJH0;
As_status:='1';
As_settle_status:='1';
As_bgdh:='';
As_bglx:='';
as_apply_dept_code:=pKDKSBH;
as_apply_doctor:=pKDYS00;
ad_start_time:=pKSRQ00;
ad_end_time:=pZZRQ00;
as_item_code:=pZLXM00;
adc_item_quantity:=pZLXMSL;
if PGCLB00='0' then
   as_class_flag:='0';
   sp_yjjk_getwzxxm_sd(As_io_flag,as_apply_dept_code,as_nullah_number,ad_start_time,ad_end_time,as_class_flag,rs_return,rs_mes_error);
   if rs_return='-1' then
      pRETRUN:='-1';
      pMessage:=rs_mes_error;
    else
      pRETRUN:='0';
      pMessage:='';
    end if ;  
elsif PGCLB00='1' then
    zhiydba.usp_yjjk_yjqr@NPEYHIS(As_io_flag,as_nullah_number,As_sick_id,As_dept_code,as_doctor,As_apply_no,As_status,
                           As_settle_status,As_bgdh,As_bglx,rs_return,rs_mes_error);
    if rs_return='-1' then
      pRETRUN:='-1';
      pMessage:=rs_mes_error;
    else
      pRETRUN:='0';
      pMessage:='';
    end if ;
elsif PGCLB00='2' then
  as_item_class:='1';
  adc_item_price:=0;
  zhiydba.usp_yjjk_clfsf@NPEYHIS(As_io_flag,as_nullah_number,As_sick_id,As_dept_code,as_doctor,as_item_code,adc_item_price,
                         adc_item_quantity,as_item_class,as_apply_dept_code,as_apply_doctor,rs_return,rs_mes_error); 
  
   if rs_return='-1' then
      pRETRUN:='-1';
      pMessage:=rs_mes_error;
    else
      pRETRUN:='0';
      pMessage:='';
    end if ;
elsif PGCLB00='3' then
  pRETRUN:='0';
  pMessage:='';
end if;
exception 
when others then  
  pRETRUN:='-1';
  pMessage:='执行过程出错!'; 
  --raise_application_error(-20021,nvl(sqlERRM,'出错原因不明*')); 
end;
/



CREATE OR REPLACE 
PROCEDURE sp_yjjk_getwzxxm_sd(as_type       in varchar2, --病人类别   0门诊 1住院 3体检
                                                as_dept_code  in varchar2, --住院科室
                                                as_card_no    in varchar2, --卡号/住院号
                                                ad_start_time in date, --起始时间
                                                ad_end_time   in date, --结束时间
                                                as_class_flag in varchar2, --单据类型 0:lis  1:ris
                                                rs_return     out varchar2,
                                                rs_mes_error  out varchar2) is
  ls_type        char(1);
  ls_card_No     sick_basic_info.ic_card_id%type;
  ls_sick_id     sick_basic_info.sick_id%type;
  ls_dept_code   department_dict.dept_code%type;
  ldt_start_time date;
  ldt_end_time   date;
  ls_class_flag  varchar2(2);
  ls_apply_class varchar2(2);
begin
  ls_type        := as_type;
  ls_dept_code   := as_dept_code;
  ls_card_No     := as_card_no;
  ldt_start_time := ad_start_time;
  ldt_end_time   := ad_end_time;
  ls_class_flag  := as_class_flag;
  --容错
  if ls_type is null or ls_type = '' then
    rs_return    := -1;
    rs_mes_error := '传入的病人类别为空!';
    return;
  end if;

  if ls_type = '0' then
    if ls_card_No is null or ls_card_No = '' then
      rs_return    := -1;
      rs_mes_error := '传入的门诊卡号为空!';
      return;
    end if;
  else
    if (ls_card_No is null or ls_card_No = '') and
       (ls_dept_code is null or ls_dept_code = '') then
      rs_return    := -1;
      rs_mes_error := '传入的住院号和申请科室都为空!';
      return;
    end if;
  end if;
  if ls_class_flag is null or ls_class_flag = '' then
    rs_return    := -1;
    rs_mes_error := '传入的单据类型为空!';
    return;
  end if;

  if ldt_start_time > ldt_end_time then
    rs_return    := -1;
    rs_mes_error := '传入的起始时间小于终止时间!';
    return;
  end if;
  ldt_end_time := ldt_end_time + 1;

  if ls_class_flag = '0' then
    ls_apply_class := 'D'; --检验
  else
    ls_apply_class := 'E'; --检查
  end if;
  --通过卡号或者住院号取病人信息
  if ls_type = '0' then
    begin
      select a.sick_id
        into ls_sick_id
        from ic_used_status a
       where a.ic_card_id = ls_card_No
         and a.now_status = '1';
    exception
      when OTHERS then
        rs_return    := -1;
        rs_mes_error := '传入的卡号找不到对应的病人信息!';
        return;
    end;
  else
    if not (ls_card_No is null or ls_card_No = '') then
      begin
        select a.sick_id
          into ls_sick_id
          from sick_visit_info a
         where a.RESIDENCE_NO = ls_card_No;
      exception
        when OTHERS then
          rs_return    := -1;
          rs_mes_error := '传入的住院号找不到对应的病人信息!';
          return;
      end;
    end if;
  end if;
  begin
    --门诊未执行医嘱
    if ls_type = '0' then
      --作废已删除的检验单
      delete from HIS_APPLY_TEMP
       where sqzt00 in ('0', '3', '7')
         and yjdjh0 in (select a.apply_no
                          from V_APPLY_SHEET_UNION a,
                               sick_basic_info     b,
                               rate_type_dict      c,
                               bill_kind_dict      d,
                               DEPT_BILL_CONFIG    e,
                               clinic_item_dict    g
                         where a.sick_id = b.sick_id
                           and b.rate_type = c.rate_type_code
                           and a.APPLY_CLASS_CODE = d.bill_code
                           and d.bill_code = e.bill_code
                           and a.item_code = g.item_code
                           and substr(g.item_class, 1, 1) in ('D')
                           and e.dept_code IN  ('0301')
                           and a.sick_id = ls_sick_id
                           and a.visit_number = 0
                           and a.apply_time >= ldt_start_time
                           and a.apply_time < ldt_end_time
                           and a.result_status = '9');
    
      insert into HIS_APPLY_TEMP
        (YJDJH0, /*单据号*/
         BRID00, /*病人ID*/
         ZYHGHH, /*住院号或挂号号*/
         MZZYBZ, /*'0'为门诊'1'为住院'2'为体检*/
         BRXM00, /*病人姓名*/
         XB0000, /*性别*/
         CSRQ00, /*出生日期*/
         FBBH00, /*费别编号(0自费,1医保,2其它)*/
         ICKH00, /*IC卡号*/
         BRBLH0, /*病人病历号*/
         ZLXMID, /*-诊疗项目ID号*/
         SL0000, /*诊疗项目数量*/
         KDYS00, /*开单医生编号*/
         KDYSXM, /*开单医生姓名*/
         KDKSBH, /*开单科室编号*/
         KDKSMC, /*开单科室名称*/
         KDRQ00, /*开单日期*/
         KDSJ00, /*开单时间*/
         JZBZ00, /*急诊标志'Y'是'N'否*/
         ZDID00, /*诊断ICD10*/
         ZDMC00, /*诊断名称*/
         SFDJH0, /*收费单据号*/
         KDBQ00, /*开单病区编号*/
         KDBQMC, /*开单病区名称*/
         CH0000, /*床号*/
         SFBZ00, /*收费标志(0=未计价、1＝收费 、2=作废)*/
         ZJE000, /*总金额*/
         BRZT00, /*病人状态('0':在院，'1':非在院)*/
         SQZT00, /*申请单状态*/
         BBID00,/*标本ID*/
         BBMC00 /*标本名称*/
         )
        select a.apply_no,
               a.sick_id,
               a.nullah_number,
               '0' io_flag,
               a.sick_name,
               decode(b.sex, '0', '男', '1', '女', '其他') sex,
               to_char(b.birthdate, 'yyyymmdd') birthdate,
               decode(c.STANDING_CODE, '01', '0', '1') rate_type,
               b.ic_card_id,
               nvl(b.CASE_NO, b.sick_id) case_no,
               a.item_code,
               nvl(a.APPLY_TIMES, 0) apply_times,
               (select d.employe_no
                  from staff_dict d
                 where a.APPLY_DOCTOR = d.name_employe_no) employe_no,
               a.APPLY_DOCTOR,
               a.APPLY_DEPT,
               (select e.dept_name
                  from department_dict e
                 where a.APPLY_DEPT = e.dept_code) apply_dept_name,
               to_char(a.APPLY_TIME, 'yyyymmdd') apply_date,
               to_char(a.APPLY_TIME, 'hh24:mi:ss') apply_time,
               decode(a.PRIORTY_FLAG, '1', 'Y', 'N') PRIORTY_FLAG,
               (select f.diagnosis_code
                  from sick_diagnosis_record f
                 where a.SICK_ID = f.sick_id
                   and a.NULLAH_NUMBER = f.nullah_number
                   and rownum = 1) diagnosis_code,
               (select substrB(f.diagnosis_name,1,60)
                  from sick_diagnosis_record f
                 where a.SICK_ID = f.sick_id
                   and a.NULLAH_NUMBER = f.nullah_number
                   and rownum = 1) diagnosis_name,
               to_number(a.APPLY_NO) fee_no,
               '' APPLY_DEPT,
               '' apply_dept_name,
               '' bed_no,
               decode(a.result_status, '0', '0', '1', '0', '1') status,
               a.COST,
               decode(a.VISIT_NUMBER, '0', '1', '0') brzt,
               '0',
               (select t.usage
                  from V_LIS_DISP_PRESCRIBE_DETAIL t
                 where a.PRESCRIBE_NO = t.prescription_number
                   and a.PRESCRIBE_SUB_NUMBER = t.sequence_number) SAMPLE_CODE,
               (select k.SAMPLE_NAME
                  from V_LIS_DISP_PRESCRIBE_DETAIL t, SAMPLE_DICT k
                 where a.PRESCRIBE_NO = t.prescription_number
                   and a.PRESCRIBE_SUB_NUMBER = t.sequence_number
                   and t.usage = k.SAMPLE_CODE) SAMPLE_NAME
          from V_APPLY_SHEET_UNION a,
               sick_basic_info     b,
               rate_type_dict      c,
               bill_kind_dict      d,
               DEPT_BILL_CONFIG    e,
               clinic_item_dict    g
         where a.sick_id = b.sick_id
           and b.rate_type = c.rate_type_code
           and a.APPLY_CLASS_CODE = d.bill_code
           and d.bill_code = e.bill_code
           and a.item_code = g.item_code
           and substr(g.item_class, 1, 1) in ('D')
           and e.dept_code IN  ('0301')
           and a.sick_id = ls_sick_id
           and a.visit_number = 0
           and a.apply_time >= ldt_start_time
           and a.apply_time < ldt_end_time
           and a.result_status <> '9'
           and not exists
         (select 1 from HIS_APPLY_TEMP p where a.apply_no = p.YJDJH0);
    else
      --先作废已经删除的单子
      delete from HIS_APPLY_TEMP
       where sqzt00 in ('0','5') and  yjdjh0 in
             (select a.apply_no
                from V_APPLY_SHEET_UNION a,
                     sick_basic_info     b,
                     rate_type_dict      c,
                     sick_visit_info     d,
                     bill_kind_dict      e,
                     DEPT_BILL_CONFIG    f,
                     clinic_item_dict    g
               where a.sick_id = b.sick_id
                 and b.rate_type = c.rate_type_code
                 and b.sick_id = d.sick_Id
                 and a.visit_number = d.visit_number
                 and a.APPLY_CLASS_CODE = e.bill_code
                 and e.bill_code = f.bill_code
                 and a.item_code = g.item_code
                 and substr(g.item_class, 1, 1) in ('D')
                 and f.dept_code IN  ('0301')                 
                 and ((d.sick_id = ls_sick_id or d.RESIDENCE_NO = ls_card_no) or
                     (a.APPLY_DEPT = ls_dept_code))
                 and a.apply_time >= ldt_start_time
                 and a.apply_time < ldt_end_time
                 and a.result_status = '9');
    
      insert into HIS_APPLY_TEMP
        (YJDJH0, /*单据号*/
         BRID00, /*病人ID*/
         ZYHGHH, /*住院号或挂号号*/
         MZZYBZ, /*'0'为门诊'1'为住院'2'为体检*/
         BRXM00, /*病人姓名*/
         XB0000, /*性别*/
         CSRQ00, /*出生日期*/
         FBBH00, /*费别编号(0自费,1医保,2其它)*/
         ICKH00, /*IC卡号*/
         BRBLH0, /*病人病历号*/
         ZLXMID, /*-诊疗项目ID号*/
         SL0000, /*诊疗项目数量*/
         KDYS00, /*开单医生编号*/
         KDYSXM, /*开单医生姓名*/
         KDKSBH, /*开单科室编号*/
         KDKSMC, /*开单科室名称*/
         KDRQ00, /*开单日期*/
         KDSJ00, /*开单时间*/
         JZBZ00, /*急诊标志'Y'是'N'否*/
         ZDID00, /*诊断ICD10*/
         ZDMC00, /*诊断名称*/
         SFDJH0, /*收费单据号*/
         KDBQ00, /*开单病区编号*/
         KDBQMC, /*开单病区名称*/
         CH0000, /*床号*/
         SFBZ00, /*收费标志(0=未计价、1＝已计价)*/
         ZJE000, /*总金额*/
         BRZT00, /*病人状态('0':在院，'1':非在院)*/
         SQZT00 ,/*申请单状态*/
         BBID00,/*标本ID*/
         BBMC00 /*标本名称*/
         )
        select a.apply_no,
               a.sick_id,
               a.nullah_number,
               '1' io_flag,
               a.sick_name,
               decode(b.sex, '0', '男', '1', '女', '其他') sex,
               to_char(b.birthdate, 'yyyymmdd') birthdate,
               decode(c.STANDING_CODE, '01', '0', '1') rate_type,
               b.ic_card_id,
               nvl(b.CASE_NO, b.sick_id) case_no,
               a.item_code,
               nvl(a.APPLY_TIMES, 0) apply_times,
               (select d.employe_no
                  from staff_dict d
                 where a.APPLY_DOCTOR = d.name_employe_no) employe_no,
               a.APPLY_DOCTOR,
               a.APPLY_DEPT,
               (select e.dept_name
                  from department_dict e
                 where a.APPLY_DEPT = e.dept_code) apply_dept_name,
               to_char(a.APPLY_TIME, 'yyyymmdd') apply_date,
               to_char(a.APPLY_TIME, 'hh24:mi:ss') apply_time,
               decode(a.PRIORTY_FLAG, '1', 'Y', 'N') PRIORTY_FLAG,
               (select f.diagnosis_code
                  from sick_diagnosis_record f
                 where a.SICK_ID = f.sick_id
                   and a.NULLAH_NUMBER = f.nullah_number
                   and rownum = 1) diagnosis_code,
               (select substrb(f.diagnosis_name,1,60)
                  from sick_diagnosis_record f
                 where a.SICK_ID = f.sick_id
                   and a.NULLAH_NUMBER = f.nullah_number
                   and rownum = 1) diagnosis_name,
               to_number(a.APPLY_NO) fee_no,
               a.APPLY_DEPT,
               (select e.dept_name
                  from department_dict e
                 where a.APPLY_DEPT = e.dept_code) apply_dept_name,
               (select h.bed_no
                  from sick_visit_info h
                 where a.SICK_ID = h.sick_id
                   and a.VISIT_NUMBER = h.visit_number) bed_no,
               decode(a.result_status, '0', '0', '1', '0', '1') status,
               a.COST,
               decode(a.VISIT_NUMBER, '0', '1', '0') brzt,
               '0',
               (select t.TAKE_MEDICINE_WAYS
                  from V_LIS_PRESCRIBE_RECORD t
                 where a.PRESCRIBE_NO = t.PRESCRIBE_NUMBER
                   and a.PRESCRIBE_SUB_NUMBER = t.PRESCRIBE_SUB_NUMBER) SAMPLE_CODE,
               (select k.SAMPLE_NAME
                  from V_LIS_PRESCRIBE_RECORD t, SAMPLE_DICT k
                 where a.PRESCRIBE_NO = t.PRESCRIBE_NUMBER
                   and a.PRESCRIBE_SUB_NUMBER = t.PRESCRIBE_SUB_NUMBER
                   and t.TAKE_MEDICINE_WAYS = k.SAMPLE_CODE) SAMPLE_NAME
          from V_APPLY_SHEET_UNION a,
               sick_basic_info     b,
               rate_type_dict      c,
               sick_visit_info     d,
               bill_kind_dict      e,
               DEPT_BILL_CONFIG    f,
               clinic_item_dict    g
         where a.sick_id = b.sick_id
           and b.rate_type = c.rate_type_code
           and b.sick_id = d.sick_Id
           and a.visit_number = d.visit_number
           and a.APPLY_CLASS_CODE = e.bill_code
           and e.bill_code = f.bill_code
           and a.item_code = g.item_code
           and substr(g.item_class, 1, 1) in ('D')
           and f.dept_code in ('0301')
           and ((d.sick_id = ls_sick_id or d.RESIDENCE_NO = ls_card_no) or
               (a.APPLY_DEPT = ls_dept_code))
           and a.apply_time >= ldt_start_time
           and a.apply_time < ldt_end_time
           and a.result_status <> '9'
           and not exists
         (select 1 from HIS_APPLY_TEMP p where a.apply_no = p.YJDJH0);
    end if;
  exception
    when others then
      rs_return    := -1;
      rs_mes_error := '执行插入HIS_APPLY_TEMP出错!原因：' ||
                      nvl(SQLERRM, '原因不明！');
      return;
  end;
  rs_return := 0;
  return;
exception
  when others then
    null;
end ;
/