折磨人的存储过程

PROCEDURE drs_return_update_check_up(P_BARCODE IN VARCHAR,
                                     P_POLNO   IN VARCHAR,
                                     P_CARD_NO IN VARCHAR,
                                     P_BIRTH_DATE IN VARCHAR,
                                     P_CUSTOMER_NAME IN VARCHAR,
                                     P_ID_TYPE IN VARCHAR,
                                     P_ID_NO   IN VARCHAR,
                                     P_GENDER  IN VARCHAR,
                                     P_UPLOADNO IN VARCHAR,
                                     p_error_flag        OUT VARCHAR,
                                     p_error_msg         OUT VARCHAR) IS

  v_status varchar2(2);
-- v_customer_no_pol customer.CUSTOMER_NO%type;
-- v_customer_no_card customer.CUSTOMER_NO%type;
  v_customer_no varchar2(50);
  --v_barcode checkup_report_main.bar_code_no%type;
  v_id_type id_type_tbl.id_type%type;
  v_gender sex_tbl.sex%type;
  v_is_found varchar2(1);
  v_bar_code checkup_report_main.bar_code_no%type;
  v_cur_num number;
  v_card_no varchar2(500);
  v_length number;
  v_cur_card_no varchar2(500);
  v_description varchar2(500);
  v_char varchar2(1);
  v_region_code region_code_tbl.REGION_CODE%type;
  v_birth_date varchar2(20);
  v_relate_exist varchar2(20);
  v_customer_count number;
  v_card_bar_no varchar2(20);
  --test_date date;

  --通过五项信息去customer,person表中匹配客户
  cursor c_customer_match is
  select a.customer_no
  from person a,
       customer c
  where a.GENDER_CODE = v_gender
-- and a.CERTIFICATE_TYPE_CODE = v_id_type
  and a.CERTIFICATE_NO = P_ID_NO
  and a.CUSTOMER_NO = c.CUSTOMER_NO
  and c.CUSTOMER_NAME = P_CUSTOMER_NAME;
  --and a.BIRTH_DATE = to_date(v_birth_date,'YYYY-MM-DD');
 
  cursor c_customer_count is
  select count(*)
  from person a,
       customer c
  where a.GENDER_CODE = v_gender
-- and a.CERTIFICATE_TYPE_CODE = v_id_type
  and a.CERTIFICATE_NO = P_ID_NO
  and a.CUSTOMER_NO = c.CUSTOMER_NO
  and c.CUSTOMER_NAME = P_CUSTOMER_NAME;
 
  --通过保单号去pol_list匹配
  cursor c_polno_match is
  select 'X'
  from pol_list
  where insno = v_customer_no
  and polno = P_POLNO;

  --通过五项信息去health_check_card_customer匹配客户
  cursor c_card_match_1 is
  select 'X'
  from health_check_card_info a,
       health_check_card_customer b
  where
  --b.GENDER_CODE = v_gender
  --and b.CERTIFICATE_TYPE_CODE = v_id_type
  --and b.CERTIFICATE_NO = P_ID_NO
   b.CUSTOMER_ID = a.CUSTOMER_ID
  and a.health_check_card_no = v_card_no
  and b.CUSTOMER_ID = a.CUSTOMER_ID
  and b.customer_no=v_customer_no;
  --and b.CUSTOMER_NAME = P_CUSTOMER_NAME
  --and b.BIRTH_DATE = to_date(v_birth_date,'YYYY-MM-DD');

  --通过卡号去health_check_corp_card_info匹配客户
  cursor c_card_match_2 is
  select 'X'
  from health_check_corp_card_info
  where card_no = v_card_no
  and policy_no = nvl(p_polno,policy_no);

  --通过卡号去pol_list匹配客户
  cursor c_card_match_3 is
  select 'X'
  from pol_list
  where INSNO = v_customer_no
  and polno = nvl(P_POLNO,polno)
  and MEMBER_CODE = v_card_no;

  cursor c_get_card_no is
  select checkup_card_no
  from checkup_report_card
  where bar_code_no = P_BARCODE;
 
  cursor test_card_bar_no is
         select 'X' from checkup_report_card
                where  bar_code_no = P_BARCODE
                or     checkup_card_no = v_cur_card_no;

  cursor test_card_bar_no2 is
         select 'X' from checkup_report_card
                where  bar_code_no = P_BARCODE
                or     checkup_card_no = v_card_no;
    cursor test_card_bar_no3 is
         select 'X' from checkup_report_card
                where  bar_code_no = P_BARCODE;                               
 
  cursor testBarcode is
      select 'X' from checkup_report_des_input
         where bar_code_no=P_BARCODE;

  --五项信息不全且卡号不为空的情况下，去health_check_card_customer匹配
  /*cursor c_is_card_match is
  select d.CUSTOMER_NO
  from health_check_card_info a,
       health_check_card_customer b,
       person c,
       customer d
  where b.GENDER_CODE = c.GENDER_CODE
  and b.CERTIFICATE_TYPE_CODE = c.CERTIFICATE_TYPE_CODE
  and b.CERTIFICATE_NO = c.CERTIFICATE_NO
  and b.CUSTOMER_ID = a.CUSTOMER_ID
  and a.health_check_card_no = v_card_no
  and b.customer_no = d.CUSTOMER_NO
  and b.CUSTOMER_NAME = d.CUSTOMER_NAME
  and b.customer_no = c.CUSTOMER_NO
  and b.BIRTH_DATE = c.BIRTH_DATE;*/

  cursor c_get_region_1 is
  select a.branch_code
  from health_check_card_batch_info a,
       health_check_card_info b
  where b.health_check_card_no = v_card_no
  and a.CARD_BATCH_NO= b.card_batch_no;

  cursor c_get_region_2 is
  select b.region_code
  from health_check_corp_card_info a,
       pol_main b
  where a.card_no = v_card_no
  and a.policy_no= b.polno;

  cursor c_get_region_3 is
  select a.region_code
  from pol_list a,
       pol_main b
  where a.polno= b.polno
  and a.member_code= v_card_no;

  cursor checkup_report_relate_exist is
  select 'X' from
         checkup_report_relate c
  where c.upload_no=P_UPLOADNO
        and c.bar_code_no = P_BARCODE;
  BEGIN
    --初始化错误代码
    p_error_flag := '00';

    v_id_type := P_ID_TYPE;

    if P_GENDER = '1' then
       v_gender := 'M';
    elsif P_GENDER = '2' then
       v_gender := 'F';
    elsif P_GENDER = '男' then
       v_gender := 'M';
    elsif P_GENDER = '女' then
       v_gender := 'F';
    else
       v_gender :='';
    end if;

    --这里把DES传过来的日期类型做一个转换，如果抛错则捕获
    if P_BIRTH_DATE is not null then
       begin
       --test_date := to_date(P_BIRTH_DATE,'yyyymmdd');
       v_birth_date := substr(P_BIRTH_DATE,0,8);
       EXCEPTION
         WHEN OTHERS THEN
         v_birth_date :='';
      end;
    end if;
/*
    select bar_code_no
    into v_bar_code
    from checkup_report_des_input
    where task_id = P_TASK_ID;*/

    --卡号可能包含'/'符号，需要截取为多个卡号
     open test_card_bar_no3;
         fetch test_card_bar_no3 into v_card_bar_no;
        close test_card_bar_no3;
    if p_card_no is not null then
       v_card_no := p_card_no;
       loop
         exit when instr(v_card_no,'/')=0 or instr(v_card_no,'/') is null;
         v_cur_num := instr(v_card_no,'/');
         v_length := length(v_card_no);
         v_cur_card_no := substr(v_card_no,0,v_cur_num-1);
         v_card_no := substr(v_card_no,v_cur_num+1,v_length);       
         if v_cur_card_no is not null then    
          open test_card_bar_no;
         fetch test_card_bar_no into v_card_bar_no; 
         if  test_card_bar_no%notfound then
            insert into checkup_report_card(seq_no,
                                            bar_code_no,
                                            checkup_card_no)
                                     values(seq_checkup_report_card_seq_no.nextval,
                                            P_BARCODE,
                                            substr(v_cur_card_no,0,20));
                        
          else
          insert into CHECKUP_REPORT_RELATE(
           ID,
           UPLOAD_NO,
           BAR_CODE_NO,
           RELATE_RESULT,
           FAIL_DESC,
           CREATED_BY,
           CREATED_DATE,
           UPDATED_BY,
           UPDATED_DATE
    )values(
           (seq_checkup_report_relate_id.nextval),
           P_UPLOADNO,
           P_BARCODE,
           '失败',
           '卡号和条形码已存在不作处理',
           'hcs',
           sysdate,
           'hcs',
           sysdate
    );                                             
          end if;
          close test_card_bar_no;                                                                                        
         end if;
        
       end loop;
      
       if v_card_no is not null then
       open test_card_bar_no2;
         fetch test_card_bar_no2 into v_card_bar_no;
          if  test_card_bar_no2%notfound then
          insert into checkup_report_card(seq_no,
                                          bar_code_no,
                                          checkup_card_no)
                                   values(seq_checkup_report_card_seq_no.nextval,
                                          P_BARCODE,
                                          substr(v_card_no,0,20));
         else
          insert into CHECKUP_REPORT_RELATE(
           ID,
           UPLOAD_NO,
           BAR_CODE_NO,
           RELATE_RESULT,
           FAIL_DESC,
           CREATED_BY,
           CREATED_DATE,
           UPDATED_BY,
           UPDATED_DATE
    )values(
           (seq_checkup_report_relate_id.nextval),
           P_UPLOADNO,
           P_BARCODE,
           '失败',
           '卡号和条形码已存在不作处理',
           'hcs',
           sysdate,
           'hcs',
           sysdate
    );                                         
       end if;                                                                                   
       close     test_card_bar_no2;                                                                               
       end if;
     
    end if;

    --如果包含波浪号，则转待处理
    /*
    if instr(P_POLNO,'~')>0 or instr(P_CARD_NO,'~')>0 or instr(P_BIRTH_DATE,'~')>0
       or instr(P_CUSTOMER_NAME,'~')>0 or instr(P_ID_TYPE,'~')>0 or instr(P_ID_NO,'~')>0
       or instr(P_GENDER,'~')>0 then
       v_status := '04';
       v_description := '包含波浪号，转待处理';*/
    --如果五项信息不全，则转未关联客户(如果是健康险通卡还需进一步处理。这个已实现见注释)
   /* elsif P_BIRTH_DATE is null or P_CUSTOMER_NAME is null   or P_ID_NO is null  then*/

       /* 如果五项信息不全，可以通过卡号反匹配出客户号，这块暂时注释掉，需要的时候再加上
       if P_CARD_NO is not null then
          open c_get_card_no;
            loop
              fetch c_get_card_no into v_card_no;
              exit when c_get_card_no%notfound or v_is_found = 'N';
              v_char := 'N';
              open c_is_card_match;
              fetch c_is_card_match into v_customer_no;
              if c_is_card_match%found then
                  if p_polno is not null then
                    open c_polno_match;
                    fetch c_polno_match into v_char;
                    if c_polno_match%found then
                        v_char := 'Y';
                        open c_get_region_1;
                        fetch c_get_region_1 into v_region_code;
                        close c_get_region_1;
                    end if;
                    close c_polno_match;
                  else
                    v_char := 'Y';
                    open c_get_region_1;
                    fetch c_get_region_1 into v_region_code;
                    close c_get_region_1;
                 end if;
              end if;
              close c_is_card_match;

              if v_char = 'Y' then
                 v_is_found := 'Y';
                 v_status := '02';
                 v_description := '根据体检卡号'||v_card_no||'成功匹配客户';
              else
                 v_is_found := 'N';
                 v_status := '03';
                 v_description := '根据体检卡号'||v_card_no||'未匹配客户';
              end if;

            end loop;
            close c_get_card_no;
       else
       */
       /*
         v_status := '03';
         v_description := '客户信息不全，转未关联客户';*/
       /*
       end if;
       */

    --五项信息全且没有波浪号，则进行判断
   -- else
             
       if  v_card_bar_no is null then
       v_status := '03';
       v_description := '客户信息未匹配客户';      
       open c_customer_match;
       loop
       fetch c_customer_match into v_customer_no;  
      
       exit when  c_customer_match%notfound or v_status='02';    
     
          --如果卡号不为空，则检验卡号信息（保单号可能为空可能不空）
          if P_CARD_NO is not null then
            --循环取所有的卡号，只要发现其中有一个卡号无法匹配，则认为未关联
            open c_get_card_no;
            loop
              fetch c_get_card_no into v_card_no;
              exit when c_get_card_no%notfound or v_is_found = 'N';

              v_char := 'N';

              --校验规则1（加上如果保单号不为空的情况），体检卡
              open c_card_match_1;
              fetch c_card_match_1 into v_char;
              if c_card_match_1%found then
                 if p_polno is not null then
                    open c_polno_match;
                    fetch c_polno_match into v_char;
                    if c_polno_match%found then
                        v_char := 'Y';
                        open c_get_region_1;
                        fetch c_get_region_1 into v_region_code;
                        close c_get_region_1;
                    end if;
                    close c_polno_match;
                 else
                    v_char := 'Y';
                    open c_get_region_1;
                    fetch c_get_region_1 into v_region_code;
                    close c_get_region_1;
                 end if;
              end if;
              close c_card_match_1;

              --校验规则2，企业健康通卡
              open c_card_match_2;
              fetch c_card_match_2 into v_char;
              if c_card_match_2%found then
                 v_char := 'Y';
                 open c_get_region_2;
                 fetch c_get_region_2 into v_region_code;
                 close c_get_region_2;
              end if;
              close c_card_match_2;

              --校验规则3，集团大单
              open c_card_match_3;
              fetch c_card_match_3 into v_char;
              if c_card_match_3%found then
                  v_char := 'Y';
                  open c_get_region_3;
                  fetch c_get_region_3 into v_region_code;
                  close c_get_region_3;
              end if;
              close c_card_match_3;

              if v_char = 'Y' then
                 v_is_found := 'Y';
                 v_status := '02';
                 v_description := '根据体检卡号'||v_card_no||'成功匹配客户';
              else
                 v_is_found := 'N';
                  --员工编号、保单号不为空
                 if P_POLNO is not null and  v_cur_card_no is not null then
                      v_status := '04';
                      v_description := '根据员工号或体检卡号、保单号'||v_card_no||','||P_POLNO||'未匹配客户';
                 else
                      v_status := '03';
                      v_description := '根据体检卡号'||v_card_no||'未匹配客户';
                 end if;
              end if;

            end loop;
            close c_get_card_no;
          --保单号不为空，卡号为空
          elsif P_POLNO is not null then
            open c_polno_match;
            fetch c_polno_match into v_char;
            if c_polno_match%found then
               v_status := '02';
               v_description := '根据保单号'||P_POLNO||'成功匹配客户';
               select region_code
               into v_region_code
               from pol_main
               where polno=P_POLNO;
            else
               v_status := '03';
               v_description := '根据保单号'||P_POLNO||'未匹配客户';
            end if;
            close c_polno_match;        
          else
               v_status := '03';
               v_description := '保单号，卡号均为空';
          end if;
               
    --更新数据
    /*
    update checkup_report_des_input
    set policy_no = P_POLNO,
        person_name = substr(P_CUSTOMER_NAME,0,60),
        gender_code = substr(v_gender,0,1),
        birth_date = to_date(v_birth_date,'YYYYMMDD'),
        id_type_code = substr(v_id_type,0,2),
        id_no = substr(P_ID_NO,0,20),
        input_birth_date = substr(p_birth_date,0,8),
        Input_id_type = substr(P_ID_TYPE,0,50),
        input_gender = substr(P_GENDER,0,10),
        input_card_no = P_CARD_NO,
        updated_date = sysdate,
        readin_date = sysdate
    where task_id = P_TASK_ID;*/
   
    --将数据放入des
    open testBarcode;
         fetch testBarcode into v_bar_code;
         if testBarcode%found then
            update checkup_report_des_input
                   set policy_no=P_POLNO,
                       person_name=P_CUSTOMER_NAME,
                       GENDER_CODE=v_gender,
                       BIRTH_DATE= to_date(v_birth_date,'YYYY-MM-DD'),
                       ID_TYPE_CODE=P_ID_TYPE,
                       ID_NO=P_ID_NO,
                       INPUT_GENDER=P_GENDER,
                       INPUT_BIRTH_DATE=P_BIRTH_DATE,
                       INPUT_ID_TYPE=P_ID_TYPE,
                       INPUT_CARD_NO=P_CARD_NO,
                       READIN_DATE=sysdate                      
             where bar_code_no=P_BARCODE;
         else
     insert into checkup_report_des_input
    (
        bar_code_no,
        policy_no,
        person_name,
        GENDER_CODE,
        BIRTH_DATE,
        ID_TYPE_CODE,
        ID_NO,
        INPUT_GENDER,
        INPUT_BIRTH_DATE,
        INPUT_ID_TYPE,
        INPUT_CARD_NO,
        READIN_DATE,
        CREATED_BY,
        CREATED_DATE,
        UPDATED_BY,
        UPDATED_DATE
    )values
    (
        P_BARCODE,
        P_POLNO,
        P_CUSTOMER_NAME,
        v_gender,
        to_date(v_birth_date,'YYYY-MM-DD'),
        substr(v_id_type,0,2),
        P_ID_NO,
        P_GENDER,
        P_BIRTH_DATE,
        P_ID_TYPE,
        P_CARD_NO,
        sysdate,
        'hcs',
        sysdate,
        'hcs',
        sysdate
    );  
         end if;
    close testBarcode;
   

    update checkup_report_main
    set status = v_status,
        customer_no = decode(v_status,'02',v_customer_no,customer_no),
        description = v_description,
        region_code = decode(v_status,'02',v_region_code,region_code)
    where bar_code_no = P_BARCODE;

    --只有02已匹配才写main表的卡号和保单号字段
    if v_status = '02' then
       update checkup_report_main
       Set policy_no= nvl(p_polno,(select polno from pol_list where member_code=v_card_no and rownum=1)),
           checkup_card_no = v_card_no
       where bar_code_no = P_BARCODE;
    end if;

    --写关联情况表
    open checkup_report_relate_exist;
    fetch checkup_report_relate_exist into v_relate_exist;
    if checkup_report_relate_exist%found then
       update CHECKUP_REPORT_RELATE
              set RELATE_RESULT =  decode(v_status,'02','成功','失败'),
                  FAIL_DESC=  decode(v_status,'02','',v_description)
              where BAR_CODE_NO = P_BARCODE;
      
    else
    insert into CHECKUP_REPORT_RELATE(
           ID,
           UPLOAD_NO,
           BAR_CODE_NO,
           RELATE_RESULT,
           FAIL_DESC,
           CREATED_BY,
           CREATED_DATE,
           UPDATED_BY,
           UPDATED_DATE
    )values(
           (seq_checkup_report_relate_id.nextval),
           P_UPLOADNO,
           P_BARCODE,
           decode(v_status,'02','成功','失败'),
           decode(v_status,'02','',v_description),
           'hcs',
           sysdate,
           'hcs',
           sysdate
    );
    end if;
    close checkup_report_relate_exist;
    --写更新记录表
    insert into checkup_report_modify_history(seq_no,
                                              bar_code_no,
                                              old_status_code,
                                              new_status_code,
                                              modify_reason,
                                              operator_id,
                                              operate_date)
                                       values(seq_checkup_rpt_mdi_his_seq_no.nextval,
                                              P_BARCODE,
                                              '06',
                                              v_status,
                                              '上载csv文件关联客户调HCS更新数据',
                                              'EHISJOB',
                                              sysdate);
    end loop;   
       close c_customer_match;
  open c_customer_count;
       fetch c_customer_count into v_customer_count;
       if v_customer_count=0 then
      
         --将数据放入des
    open testBarcode;
         fetch testBarcode into v_bar_code;
         if testBarcode%found then
            update checkup_report_des_input
                   set policy_no=P_POLNO,
                       person_name=P_CUSTOMER_NAME,
                       GENDER_CODE=v_gender,
                       BIRTH_DATE= to_date(v_birth_date,'YYYY-MM-DD'),
                       ID_TYPE_CODE=P_ID_TYPE,
                       ID_NO=P_ID_NO,
                       INPUT_GENDER=P_GENDER,
                       INPUT_BIRTH_DATE=P_BIRTH_DATE,
                       INPUT_ID_TYPE=P_ID_TYPE,
                       INPUT_CARD_NO=P_CARD_NO,
                       READIN_DATE=sysdate                      
             where bar_code_no=P_BARCODE;
         else
     insert into checkup_report_des_input
    (
        bar_code_no,
        policy_no,
        person_name,
        GENDER_CODE,
        BIRTH_DATE,
        ID_TYPE_CODE,
        ID_NO,
        INPUT_GENDER,
        INPUT_BIRTH_DATE,
        INPUT_ID_TYPE,
        INPUT_CARD_NO,
        READIN_DATE,
        CREATED_BY,
        CREATED_DATE,
        UPDATED_BY,
        UPDATED_DATE
    )values
    (
        P_BARCODE,
        P_POLNO,
        P_CUSTOMER_NAME,
        v_gender,
        to_date(v_birth_date,'YYYY-MM-DD'),
        substr(v_id_type,0,2),
        P_ID_NO,
        P_GENDER,
        P_BIRTH_DATE,
        P_ID_TYPE,
        P_CARD_NO,
        sysdate,
        'hcs',
        sysdate,
        'hcs',
        sysdate
    );  
         end if;
    close testBarcode;
   

    update checkup_report_main
    set status = v_status,
        customer_no = decode(v_status,'02',v_customer_no,customer_no),
        description = v_description,
        region_code = decode(v_status,'02',v_region_code,region_code)
    where bar_code_no = P_BARCODE;

    --只有02已匹配才写main表的卡号和保单号字段
    if v_status = '02' then
       update checkup_report_main
       Set policy_no= nvl(p_polno,(select polno from pol_list where member_code=v_card_no and rownum=1)),
           checkup_card_no = v_card_no
       where bar_code_no = P_BARCODE;
    end if;

    --写关联情况表
    open checkup_report_relate_exist;
    fetch checkup_report_relate_exist into v_relate_exist;
    if checkup_report_relate_exist%found then
       update CHECKUP_REPORT_RELATE
              set RELATE_RESULT =  decode(v_status,'02','成功','失败'),
                  FAIL_DESC=  decode(v_status,'02','',v_description)
              where BAR_CODE_NO = P_BARCODE;
      
    else
    insert into CHECKUP_REPORT_RELATE(
           ID,
           UPLOAD_NO,
           BAR_CODE_NO,
           RELATE_RESULT,
           FAIL_DESC,
           CREATED_BY,
           CREATED_DATE,
           UPDATED_BY,
           UPDATED_DATE
    )values(
           (seq_checkup_report_relate_id.nextval),
           P_UPLOADNO,
           P_BARCODE,
           decode(v_status,'02','成功','失败'),
           decode(v_status,'02','',v_description),
           'hcs',
           sysdate,
           'hcs',
           sysdate
    );
    end if;
    close checkup_report_relate_exist;
    --写更新记录表
    insert into checkup_report_modify_history(seq_no,
                                              bar_code_no,
                                              old_status_code,
                                              new_status_code,
                                              modify_reason,
                                              operator_id,
                                              operate_date)
                                       values(seq_checkup_rpt_mdi_his_seq_no.nextval,
                                              P_BARCODE,
                                              '06',
                                              v_status,
                                              '上载csv文件关联客户调HCS更新数据',
                                              'EHISJOB',
                                              sysdate);
       end if;
  close c_customer_count;
  end if;

  EXCEPTION
    --例外处理，返回出错代码和信息
    WHEN OTHERS THEN
      ROLLBACK;
      p_error_flag := '01'; --失败
      p_error_msg  := 'drs_return_update_check_up' || substr(SQLERRM, 1, 200);
      
    
  END drs_return_update_check_up;
END hhcs_package_outbound;