declare 
  cssdCode varchar(100);
  cssdName varchar(100);
begin
  
      select orgUnitCoding into cssdCode from SupplyRoomConfig where supplyRoomType='1';
      select orgUnitName into cssdName from SupplyRoomConfig where supplyRoomType='1';

      if cssdCode is null then
      begin
         dbms_output.put_line('未配置一级供应室,请先配置一级供应室');
      end;
      else
      begin
         dbms_output.put_line('一级供应室编码='||cssdCode||',名称='||cssdName);

	  delete from CssdHandleTousses;
	  
	  --器械包定义未配置处理科室的器械包
          insert into CssdHandleTousses (id,
                 orgUnitCode
                ,orgUnitName
                ,tousseDefinitionId
                ,tousseName
          )
          select HIBERNATE_SEQUENCE.Nextval, cssdCode,cssdName,td.id,td.name from TousseDefinition td where td.forDisplay='1'
          and (td.handlerDepartCode is null or len(td.handlerDepartCode) = 0)
	  and td.id not in (select tousseDefinitionId from CssdHandleTousses);

          --器械包定义已配置处理科室的器械包
          insert into CssdHandleTousses (orgUnitCode,orgUnitName,tousseDefinitionId,tousseName)
          select td.handlerDepartCode,td.handlerDepartName,td.id,td.name from TousseDefinition td where td.forDisplay='1'
          and td.handlerDepartCode is not null and len(td.handlerDepartCode) > 0
          and td.id not in (select tousseDefinitionId from CssdHandleTousses)

	  delete from CssdServiceDepts;
          insert into CssdServiceDepts(id,clinicOrgUnitCode
                ,clinicOrgUnitId
                ,clinicOrgUnitName
                ,cssdOrgUnitCode
                ,cssdOrgUnitName
                ,tousseType)
          select HIBERNATE_SEQUENCE.Nextval,ou.orgUnitCoding,ou.id,ou.name,cssdCode,cssdName,'器械包' from OrgUnit ou 
          where ou.orgUnitCoding in (select orgUnitCoding from SupplyRoomConfig where supplyRoomType in ('3','4')) 
          and ou.orgUnitCoding not in (select clinicOrgUnitCode from CssdServiceDepts where tousseType='器械包');



          insert into CssdServiceDepts(id,clinicOrgUnitCode
                ,clinicOrgUnitId
                ,clinicOrgUnitName
                ,cssdOrgUnitCode
                ,cssdOrgUnitName
                ,tousseType)
          select HIBERNATE_SEQUENCE.Nextval,ou.orgUnitCoding,ou.id,ou.name,cssdCode,cssdName,'一次性物品' from OrgUnit ou 
          where ou.orgUnitCoding in (select orgUnitCoding from SupplyRoomConfig where supplyRoomType in ('3','4')) 
          and ou.orgUnitCoding not in (select clinicOrgUnitCode from CssdServiceDepts where tousseType='一次性物品');
          dbms_output.put_line('执行成功');
          commit;


      end;

      end if;

end;