CREATE OR REPLACE

PROCEDURE      "PROC_INS_DAY_DOCTOR_LL"(v_date in VARCHAR) is

--PROC_INS_DAY_DOCTOR_LL 是存储函数名

    v_id                                                DAY_DOCTOR_STATS.DDOS_ID%TYPE;--主键

    v_hospital_id                                       DAY_DOCTOR_STATS.HOSPITAL_ID%TYPE;--医院ID

    v_doctor_id                                         DAY_DOCTOR_STATS.DOCTOR_ID%TYPE;--医生id

    v_total_money                                              DAY_DOCTOR_STATS.TOTAL_MONEY%TYPE;--总金额

    v_anti_money                                              DAY_DOCTOR_STATS.ANTI_MONEY%TYPE;--抗菌药金额

    v_create_date                                         DAY_DOCTOR_STATS.CREATE_DATE%TYPE;--创建时间

    CURSOR CUR_HOSPITAL_DOCT IS--声明游标  医生数据(住院)

    SELECT DISTINCT c.DOCTOR_ID

    FROM FIN_IPR_INMAININFO a,FIN_IPB_MEDICINELIST b,DOCTOR c

    WHERE a.INPATIENT_NO= b.INPATIENT_NO

    and b.RECIPE_DOCCODE=c.EMPL_CODE

    AND to_char(a.OUT_DATE,'yyyy-mm-dd')=v_date

    and rownum<2;

BEGIN

     OPEN CUR_HOSPITAL_DOCT; --打开游标

  --循环游标

  LOOP

    FETCH CUR_HOSPITAL_DOCT  INTO v_doctor_id; --取值

    EXIT WHEN CUR_HOSPITAL_DOCT%NOTFOUND; --当没有记录时退出循环  

        --把住院用药信息数据导入到一个临时表中

        Insert into FIN_IPB_MEDICINELIST_NEW (HOSPITAL_CODE,RECIPE_NO,SEQUENCE_NO,INPATIENT_NO,TRANS_TYPE,CARD_NO,NAME,INHOS_DEPTCODE,RECIPE_DOCCODE,DRUG_CODE,DRUG_NAME,SPECS,DOSE_MODEL_CODE,UNIT_PRICE,QTY,FREQUENCY_NAME,FREQUENCY_CODE,USAGE_CODE,USE_NAME,DOSE_ONCE,DOSE_UNIT,BASE_DOSE,DAYS,PACK_QTY,PRICE_UNIT,OWN_MONEY,MEDICARE_MONEY,FEE_DATE,MO_ORDER,BROUGHT_FLAG,MEDICAL_TEAM_CODE,IS_HERBS,MANUFACTURER,ID)

select * from FIN_IPB_MEDICINELIST WHERE INPATIENT_NO in(select INPATIENT_NO from FIN_IPR_INMAININFO where to_char(OUT_DATE,'yyyy-mm-dd')=v_date);

    --主键

    select substr(sys_guid(),1,8)||'-'||substr(sys_guid(),9,4)||'-'||substr(sys_guid(),13,4)||'-'||substr(sys_guid(),17,4)||'-'||substr(sys_guid(),20,12)

      into v_id from dual;

    --HOSPITAL_ID

    --医院编码

    select '' into v_hospital_id from dual;

    --医生编码

    SELECT EMPL_CODE INTO v_empl_code FROM  DOCTOR WHERE DOCTOR_ID=v_doctor_id;

    --TOTAL_MONEY

    --总金额

    SELECT nvl(sum(OWN_MONEY),0) into v_total_money

    FROM FIN_IPB_MEDICINELIST_NEW

    WHERE DRUG_CODE IN (SELECT DRUG_CODE FROM PHA_COM_BASEINFO WHERE  manufacturer is not null )

    AND RECIPE_DOCCODE =v_empl_code;

    --ANTI_MONEY

    --抗菌药金额

    select nvl(sum(OWN_MONEY),0) into v_anti_money

    from FIN_IPB_MEDICINELIST_NEW

    where  RECIPE_DOCCODE =v_empl_code

    AND DRUG_CODE IN (SELECT DRUG_CODE FROM PHA_COM_BASEINFO WHERE  IS_KJY='');

    --CREATE_DATE

    --创建时间

        select TO_DATE(v_date,'yyyy-mm-dd') into v_create_date from dual;

    --删除当天的数据

    delete from DAY_DOCTOR_STATS

     where CREATE_DATE=v_create_date and DOCTOR_ID=v_doctor_id;

    --清除住院用药表数据

    DELETE FROM FIN_IPB_MEDICINELIST_NEW;

    --生成一条新数据（其实就是向一张表插入数据）

    insert into DAY_DOCTOR_STATS

    (DDOS_ID,HOSPITAL_ID,DOCTOR_ID,TOTAL_MONEY,ANTI_MONEY,CREATE_DATE)

    values(v_id,v_hospital_id,v_doctor_id,v_total_money,v_anti_money,v_create_date);

  commit;

  END LOOP;

  CLOSE CUR_HOSPITAL_DOCT;--关闭这个游标

END;