FUNCTION fn_validate_trailer
   (
      p_file_name   IN VARCHAR2,
      p_dir_name    IN VARCHAR2,
      p_clob_id     IN NUMBER, -- raghava remarks changes
      p_error_code  OUT VARCHAR2,
      p_error_param OUT VARCHAR2
   ) RETURN BOOLEAN IS
   
      --v_in_file       utl_file.file_type; -- raghava remarks changes
   
      v_in_rec CLOB; --VARCHAR2(4000); -- raghava remarks changes
   
      l_count         NUMBER := 0;
      record_count    NUMBER := 0;
      l_trailer_count NUMBER := 0;
      -- raghava remarks changes starts 
      v_file_bf    BFILE;
      v_manual_cl  CLOB;
      l_lang_ctx   NUMBER := dbms_lob.default_lang_ctx;
      l_charset_id NUMBER := 0;
      l_src_offset NUMBER := 1;
      l_dst_offset NUMBER := 1;
      l_warning    NUMBER;
      -- raghava remarks changes ends
   BEGIN
      debug.pr_debug('SF', 'Start of fn_validate_trailer ->');
      -- raghava remarks changes starts 
      debug.pr_debug('SF', 'UAT remarks issue p_clob_id ->' || p_clob_id);
      BEGIN
         SELECT mastertxt_bf, manual_cl
         INTO   v_file_bf, v_manual_cl
         FROM   iftb_clob_catalog
         WHERE  id = p_clob_id FOR UPDATE;
      EXCEPTION
         WHEN no_data_found THEN
            v_file_bf   := NULL;
            v_manual_cl := NULL;
      END;
      dbms_lob.fileopen(v_file_bf, dbms_lob.file_readonly);
      --v_in_file := utl_file.fopen(p_dir_name, p_file_name, 'r');
      -- raghava remarks changes ends      
      debug.pr_debug('SF', 'Trailer File Openeed ->');  
      
      LOOP
         BEGIN
            -- raghava remarks changes starts 
            --utl_file.get_line(v_in_file, v_in_rec);
           
           dbms_lob.loadclobfromfile(v_manual_cl,
                                      v_file_bf,
                                     -- dbms_lob.getlength(v_file_bf),
                                      dbms_lob.lobmaxsize,
                                      l_src_offset,
                                      l_dst_offset,
                                      l_charset_id,
                                      l_lang_ctx,
                                      l_warning);
            -- v_in_rec := ltrim(rtrim(v_in_rec));
            --v_in_rec := ltrim(rtrim(v_manual_cl));
             v_in_rec := (v_manual_cl);
              debug.pr_debug('SF', 'Trailer File Loaded ->'); 
            -- raghava remarks changes ends 
         
            IF substr(v_in_rec, 1, 3) = 'TRL'
            --OR (SUBSTR(v_in_rec,1,1) = '2' AND LENGTH(v_in_rec) <> 11)
            --OR SUBSTR(v_in_rec,1,1) = '3'
            THEN
               l_trailer_count := l_trailer_count + 1;
               l_count         := l_count + 1;
               debug.pr_debug('SF', 'Total no. of records is ' || l_count);
               --IF TO_NUMBER(SUBSTR(v_in_rec,12,10)) = l_count
               --IF to_number(cspkes_misc.fn_getparam(v_in_rec, 3, '~')) = l_count -- raghava remarks changes
               IF to_number(fn_getparam(v_in_rec, 3, '~')) = l_count
               THEN
                  debug.pr_debug('SF',
                                 'fn_validate_trailer ->No of records is correct in File.');
                  -- raghava remarks changes starts                  
                  -- utl_file.fclose(v_in_file);
                  dbms_lob.fileclose(v_file_bf);
                  -- raghava remarks changes ends
                  debug.pr_debug('SF', 'fn_validate_trailer -> Before return TRUE');
                  debug.pr_debug('SF',
                                 'End of fn_validate_trailer ----------------------------------->');
                  RETURN TRUE;
               ELSE
                  debug.pr_debug('SF',
                                 'fn_validate_trailer ->No of records is not correct in File.');
                  -- raghava remarks changes starts                  
                  -- utl_file.fclose(v_in_file);
                  dbms_lob.fileclose(v_file_bf);
                  -- raghava remarks changes ends
                  debug.pr_debug('SF', 'fn_validate_trailer -> Before return FALSE');
                  debug.pr_debug('SF',
                                 'End of fn_validate_trailer ----------------------------------->');
                  p_error_code  := 'SF-INF-004;';
                  p_error_param := 'fn_validate_trailer ->No of records is not correct in File.';
                  RETURN FALSE;
               
               END IF;
            END IF;
         
            l_count := l_count + 1;
         EXCEPTION
            WHEN utl_file.invalid_path THEN
               debug.pr_debug('SF',
                              'fn_validate_trailer ->Directory OR FILE name IS invalid OR NOT accessible');
               p_error_code  := 'SF-INF-002;';
               p_error_param := 'Directory OR FILE name IS invalid OR NOT accessible';
               -- raghava remarks changes starts                  
               -- utl_file.fclose(v_in_file);
               dbms_lob.fileclose(v_file_bf);
               -- raghava remarks changes ends
               RETURN FALSE;
            WHEN OTHERS THEN
               p_error_code  := 'SF-INF-003;';
               p_error_param := 'Error IN reading Trailer RECORD ' || SQLERRM;
               debug.pr_debug('SF',
                              'fn_validate_trailer -> IN OTHERS EXCEPTION - Error ' ||
                              SQLERRM);
               RETURN FALSE;
            
         END;
      END LOOP;
      IF l_trailer_count <> 1
      THEN
         debug.pr_debug('SF', 'Trailer not included in File ... Pls Check');
         p_error_code  := 'SF-INF-005;';
         p_error_param := 'Trailer not included in File ... Pls Check';
         RETURN FALSE;
      END IF;
   
   EXCEPTION
      WHEN OTHERS THEN
         debug.pr_debug('SF',
                        ' in motherhood wot of fn_validate_trailer with ' || SQLERRM);
         p_error_code  := 'SF-INF-003;';
         p_error_param := '';
         RETURN FALSE;
   END fn_validate_trailer;