begin
   for orgId in 
     (select org_id from DWSDATA.T_AGENT_ORG_ID  group by agent_id
                   ) loop
                   
   insert into ken.all_agent(agent_id,all_child_agent )
   select  orgId.org_id,t.org_id
   from CDMDATA.agent_list t  start with t.agent_id=orgId.agent_id connect by prior agent_id= parent_agent_id
   ;
   commit;
   end loop;
  end;

begin
for RTrade_Date in 
  (select distinct statistic_date from ADSDATA.XS_DATA_INDEX_DAILY  where  statistic_date>= trunc(sysdate-1)  and statistic_date< trunc(sysdate)  )  -- 所有需要统计的日期
   loop
insert into ADSDATA.XS_DATA_INDEX_DAILY
(
    statistic_date
   ,od_category
   ,statistic_value
   ,value_comment
   ,data_timestamp
    )
 select 
    trunc(max(LAST_TRADE_DATE)) as statistic_date 
    ,‘交易统计‘ as od_category
    ,count(0) statistic_value
    ,‘连续30天无交易商户数‘ as value_comment
    ,sysdate as data_timestamp
 from (
     select max(Trade_Date) as LAST_TRADE_DATE,shop_no  from XS.TRADE_DETAIL_HIS  where Trade_Date < RTrade_Date.Statistic_Date+1  group by shop_no --每个用户交易的最早日期
    )
 where LAST_TRADE_DATE+30 >RTrade_Date.Statistic_Date+1;
 commit;
   end loop;
 end;