declare
           in_Business          VARCHAR2 (10) := 'EPC';
           in_TBF_Start  EPCTBFSITEHEADER.BILLPERIODSTARTDATE%TYPE;
           in_TBF_Stop   EPCTBFSITEHEADER.BILLPERIODENDDATE%TYPE;
           in_TBF_Usage  EPCTBFSITEHEADER.USAGETOTAL%TYPE;
           in_TBF_SiteId EPCTBFSITEHEADER.SITEID%TYPE;
           in_MeterType  VARCHAR2(24);
           in_Read_Start DCMUSAGE.STARTTIME%TYPE;
           in_Read_Stop  DCMUSAGE.STOPTIME%TYPE;
           in_Read_Usage DCMUSAGE.USAGE%TYPE;
           in_Read_UidAccount   DCMUSAGE.UIDACCOUNT%TYPE;
           
           
--
vd_TBF_Start          EPCTBFSITEHEADER.BILLPERIODSTARTDATE%TYPE := in_TBF_Start;
vd_TBF_Stop           EPCTBFSITEHEADER.BILLPERIODENDDATE%TYPE   := in_TBF_Stop;
vn_TBF_Usage          EPCTBFSITEHEADER.USAGETOTAL%TYPE          := in_TBF_Usage;
vd_Read_Start         DCMUSAGE.STARTTIME%TYPE                   := in_Read_Start;
vd_Read_Stop          DCMUSAGE.STOPTIME%TYPE                    := in_Read_Stop;
vd_Read_Stop2         DCMUSAGE.STOPTIME%TYPE;
vn_Read_Usage         DCMUSAGE.USAGE%TYPE                       := in_Read_Usage;
vn_Read_Usage2        DCMUSAGE.USAGE%TYPE;
vs_SiteID             EPCTBFSITEHEADER.SITEID%TYPE;
vs_Valid              DCMUSAGE.VALID%TYPE;
vs_Valid2             DCMUSAGE.VALID%TYPE;
vn_UidAccount         ACCOUNT.UIDACCOUNT%TYPE;
vn_TBF_CurrentRead    EPCTBFTARIFFDETAIL.CURRMETERREAD%TYPE;
vs_uiddcmusage        DCMUSAGE.UIDDCMUSAGE%TYPE;
vs_UIDDCMmessage      VARCHAR2(40) := 'UIDDCM=';
--
   CURSOR c_get_9590 is
      SELECT * from UTS_EXCEPTION_LOG 
         WHERE ssgerrorcode like '%9590%'
           and wspcode = '0040'
           and x_sr_stat_id != 'Closed' ;

   cursor c_Get_Tbf(p_Externalref uts_exception_log.externalxRef%type) is
     select * from EPCTBFTARIFFDETAIL 
        where uiduts_tbf = p_externalref ;
  
    cursor c_Get_emc_Tbf(p_Externalref uts_exception_log.externalxRef%type) is
     select * from EPCTBFTARIFFDETAIL 
        where uiduts_tbf = p_externalref ;      

                
BEGIN
   --
   DBMS_OUTPUT.PUT_LINE('CLOSE EXCEPTION, SITEID, TBF STARTTIME, TBF STOPTIME, TBF USAGE, DCM STARTTIME, DCM STOPTIME, DCM USAGE');
   
   for v_Get_9590 in c_get_9590 loop
      if v_Get_9590.RETAILERID in ('474804103','935695827') then
         in_Business := 'EMC' ;
      else
         in_Business := 'EPC' ;
      end if;
    
      vs_SiteID := v_Get_9590.siteid ;
      vn_UidAccount := NVL(in_Read_UidAccount, Pkg_Lpss_Util.fn_Get_UidAccount(vs_SiteID));
   
      pkg_lpss_Tbf.pr_Get_DCM_Usage(vn_UidAccount, v_Get_9590.Stoptime, vd_Read_Start, vd_Read_Stop, vn_Read_Usage, vs_Valid, vs_uiddcmusage );
      if vs_uiddcmusage is null then 
         dbms_output.put_line('Problem with DCM ...Check DCM record: '||vs_SiteID);
          
     else
   
     
     if in_business = 'EPC'  then
      for v_get_tbf in c_get_tbf(v_Get_9590.externalxref) loop
        vn_TBF_Usage := v_get_tbf.usage;
      end loop;  
     else
       for v_get_emc_tbf in c_get_emc_tbf(v_Get_9590.externalxref) loop
        vn_TBF_Usage := v_get_emc_tbf.usage;
      end loop;  
      
     end if ;
     
     vd_TBF_Start    :=  v_Get_9590.starttime;
     vd_TBF_Stop     :=  v_Get_9590.stoptime;
     vd_Read_Start :=  TRUNC(vd_Read_Start) + 1;
     vd_Read_Stop  :=  TRUNC(vd_Read_Stop);
      
--pkg_lpss_Tbf.pr_Get_TBF_Usage(in_Business, vd_Read_Start, vd_Read_Stop, vs_SiteID, vd_TBF_Start, vd_TBF_Stop, vn_TBF_Usage, vn_TBF_CurrentRead);

      IF vd_TBF_Start = vd_Read_Start AND vd_TBF_Stop = vd_Read_Stop THEN
         -- If the TBF period we have matches the DCM/DIM period, there's no need to look it up.
         NULL;
      ELSIF vd_TBF_Start < vd_Read_Start THEN
         -- 2006-02-10 *MY If the TBF period starts prior to the DCM period, there's no need to extend the TBF period.
         NULL;
      ELSE
         pkg_lpss_Tbf.pr_Get_TBF_Usage(in_Business, vd_Read_Start, vd_Read_Stop, vs_SiteID, vd_TBF_Start, vd_TBF_Stop, vn_TBF_Usage, vn_TBF_CurrentRead);
      END IF;
  
   --
   -- If the TBF period is larger than the meter read period, and this is a DCM record,
   -- try to get more meter read info.
   --
   IF vd_TBF_Start < vd_Read_Start AND vd_TBF_Stop = vd_Read_Stop AND in_MeterType = Pkg_Lpss_Variables.C_CUMULATIVE_METERTYPE
      AND vd_Read_Stop < pkg_lpss_Tbf.fn_get_RDS_ImplemenationDate THEN
    
      pkg_lpss_Tbf.pr_Get_DCM_Usage(vn_UidAccount, vd_Read_Start - 1, vd_Read_Start, vd_Read_Stop2, vn_Read_Usage2, vs_Valid2, vs_uiddcmusage );
      vd_Read_Start :=  TRUNC(vd_Read_Start) + 1;
      vn_Read_Usage := vn_Read_Usage + vn_Read_Usage2;
    
      IF vs_Valid2 = 'N' THEN  
         vs_Valid := 'N';  
      END IF;
   END IF;
   --
   -- If we found both a TBF and a meter read record but the usage values don't match,
   -- generate an exception.
   --
   IF vd_TBF_Start = vd_Read_Start  AND vd_TBF_Stop = vd_Read_Stop AND vn_TBF_Usage <> vn_Read_Usage THEN
       DBMS_OUTPUT.put_line( 'NO' ||','|| v_Get_9590.siteid ||','||vd_TBF_Start ||','||vd_TBF_Stop||','||vn_TBF_Usage ||','|| vd_Read_Start ||','|| vd_Read_Stop ||','|| vn_Read_Usage);
   ELSE
       DBMS_OUTPUT.put_line( 'YES' ||','|| v_Get_9590.siteid ||','||vd_TBF_Start ||','||vd_TBF_Stop||','||vn_TBF_Usage ||','|| vd_Read_Start ||','|| vd_Read_Stop ||','|| vn_Read_Usage);   
   END IF;
   end if ;
end loop ;
exception 
   when others then
      dbms_output.put_line (SQLERRM);
END ;