CREATE  GLOBAL   TEMPORARY   TABLE  Temp_Table
　 (
    ITEM_CLASS    varchar2(100),
    QUANTITY      NUMBER,   ----
    COSTS         NUMBER,
    UNITS_CODE    varchar2(100),
    INP_NO        varchar2(100),
    ITEM_NO       NUMBER ,
    ITEM_CODE     varchar2(100),
    ITEM_NAME     varchar2(100),
    GENERAL_NAME  varchar2(100),
    ITEM_SPEC     varchar2(100),
    UNITS         varchar2(100),
    AMOUNT        NUMBER,
    UNIT_PRICE    NUMBER,
    CHARGES       NUMBER,
    CLASS_ON_INP_RCPT varchar2(100),
    DRUG_SIGN     varchar2(100),
    BILLING_DATE_TIME date,
    INSURANCE_CODE varchar2(100),
    INSURANCE_PAY  NUMBER,
    PERSON_STATUS  varchar2(100),
    CHARGE_TYPE    varchar2(100),
    PAY_IN         NUMBER,  
    PAY_OUT        NUMBER  
)
ON COMMIT DELETE ROWS;

declare  temp_column varchar2;
  
CURSOR order_cursor_1 IS select distinct ITEM_CLASS from V_CONN_INP_BILL_DETAIL; --定义游标
begin
open order_cursor_1 --打开游标
LOOP
 FETCH order_cursor_1 INTO temp_column; 
 EXIT WHEN order_cursor_1%NOTFOUND;
--FETCH NEXT FROM order_cursor_1 INTO @temp_column
--WHILE @@FETCH_STATUS = 0 
  --begin
    insert into Temp_Table select B.ITEM_CLASS,B.QUANTITY,B.COSTS,B.UNITS_CODE,B.INP_NO,B.ITEM_NO,B.ITEM_CODE,B.ITEM_NAME,B.GENERAL_NAME,B.ITEM_SPEC,B.UNITS
,B.AMOUNT,B.UNIT_PRICE,B.CHARGES,B.CLASS_ON_INP_RCPT,B.DRUG_SIGN,B.BILLING_DATE_TIME,B.INSURANCE_CODE
,B.INSURANCE_PAY,P.CHARGE_TYPE,P.PERSON_STATUS,
--下面根据条件判断
(CASE P.CHARGE_TYPE 
      WHEN '01' THEN 0 
      ELSE 
      CASE 
        WHEN (p.PERSON_STATUS='06') THEN  CHARGES 
        WHEN (p.PERSON_STATUS='07') THEN  CHARGES 
        WHEN (p.PERSON_STATUS='08') THEN  CHARGES 
        WHEN (p.PERSON_STATUS='09') THEN  CHARGES 
      ELSE
        B.INSURANCE_PAY * B.CHARGES  
      END          
      END)  AS PAY_IN,
(CASE P.CHARGE_TYPE 
      WHEN '01' THEN   CHARGES   
      ELSE 
      CASE 
        WHEN (p.PERSON_STATUS='06') THEN 0
        WHEN (p.PERSON_STATUS='07') THEN 0
        WHEN (p.PERSON_STATUS='08') THEN 0
        WHEN (p.PERSON_STATUS='09') THEN 0
      ELSE
      (1 - B.INSURANCE_PAY) * B.CHARGES
      END
      END)  AS PAY_OUT

--B.INSURANCE_PAY * B.CHARGES as PAY_IN,(1 - B.INSURANCE_PAY) * B.CHARGES as PAY_OUT
  from V_CONN_INP_BILL_DETAIL B,V_CONN_INP_PATS_VISIT P where B.ITEM_CLASS = temp_column AND B.INP_NO=P.SCOPE  --SCOPE是住院号
  
  
        insert into Temp_Table select'小计',0,0,'','',0,'','','','',''
,0,0,0,'','','','',0,'',''
,SUM(CASE P.CHARGE_TYPE 
      WHEN '01' THEN 0 
      ELSE 
      CASE 
        WHEN (p.PERSON_STATUS='06') THEN CHARGES 
        WHEN (p.PERSON_STATUS='07') THEN  CHARGES 
        WHEN (p.PERSON_STATUS='08') THEN  CHARGES 
        WHEN (p.PERSON_STATUS='09') THEN  CHARGES 
      ELSE
        B.INSURANCE_PAY * B.CHARGES  
      END          
      END),
SUM(CASE P.CHARGE_TYPE 
      WHEN '01' THEN  CHARGES   
      ELSE 
      CASE 
        WHEN (p.PERSON_STATUS='06') THEN 0
        WHEN (p.PERSON_STATUS='07') THEN 0
        WHEN (p.PERSON_STATUS='08') THEN 0
        WHEN (p.PERSON_STATUS='09') THEN 0
      ELSE
      (1 - B.INSURANCE_PAY) * B.CHARGES
      END
      END)
        from V_CONN_INP_BILL_DETAIL B,V_CONN_INP_PATS_VISIT P 
         where ITEM_CLASS = temp_column  AND B.INP_NO=P.SCOPE  --这里就拼小计需要统计的列了，注意列要和上面的一样
         
   -- FETCH NEXT FROM order_cursor_1 INTO @temp_column --让游标读取下一个
  --end
  END LOOP; 
close order_cursor_1 --关闭游标  
end;


insert into Temp_Table select '合计',0,0,'','',0,'','','','',''
,0,0,0,'','','','',0,'',''
,SUM(CASE P.CHARGE_TYPE 
      WHEN '01' THEN 0 
      ELSE 
      CASE 
        WHEN (p.PERSON_STATUS='06') THEN  CHARGES 
        WHEN (p.PERSON_STATUS='07') THEN  CHARGES 
        WHEN (p.PERSON_S