CURSOR cur_miss_record
   IS
      SELECT R.PROG_PROGRAM_CD PROG_PROGRAM_CD,where.....



   v_msg                    VARCHAR2(32767) := 'Program Code' || CHR (9) ||
                                                 '# of mismatch' || CHR (13);
  v_dir_location           VARCHAR2 (100);
   v_dir_parm_name          VARCHAR2(200) := 'DEFAULTDATADIR';
    v_mail_sender_parm       VARCHAR2(100) := 'MAIL SENDER';
   v_mail_sender            VARCHAR2(100);
   v_mail_recipients_parm   VARCHAR2(200) := 'Mail CIT WMS to';
   v_mail_recipients        VARCHAR2(200);
   v_database               VARCHAR2 (20);
   v_rec_count              NUMBER := 0;
   v_file_name              VARCHAR2 (100) := 'CIT_WMS';   
   v_file_date              VARCHAR2 (20);
    v_filesize_limit         INTEGER := 32767;  
   v_file_handle            UTL_FILE.FILE_TYPE;
   v_file_length            NUMBER;
   v_blk_size               NUMBER;
   v_file_attachment        BFILE;
   v_rawbuf                 RAW (32767);
   v_amt                    PLS_INTEGER := 32767;
   v_offset                 PLS_INTEGER := 1;
   v_msg_text               MESSAGES.msg_text%TYPE;
  
	   r_error_rt           UTIL_BACKTRACE_PK.ERROR_RT;  -- CHG111303

BEGIN

-- find file directory
   SELECT GLOBAL_NAME
     INTO v_database
dbms_output.put_line ('Database = '||v_database);

-- find e-mail sender
   SELECT parm_val
     INTO v_mail_sender

dbms_output.put_line ('Sender = '||v_mail_sender);


    SELECT parm_val
      INTO v_mail_recipients

dbms_output.put_line ('Recipients = '||v_mail_recipients); 


   FOR rec_cit_attr IN cur_cit_attr
    LOOP
         v_rec_count := v_rec_count + 1;

         IF rec_cit_attr.prog_program_cd IS NOT NULL THEN
             v_msg :=
                  v_msg
               || rec_cit_attr.prog_program_cd
               || CHR (9)
               || CHR (9)
               || CHR (9)
               || CHR (9)
               || rec_cit_attr.total
               || CHR (13);
         ELSE
            v_msg :=
                  v_msg
               || 'TOTAL'
               || CHR (9)
               || CHR (9)
               || CHR (9)
               || CHR (9)
               || rec_cit_attr.total
               || CHR (13);
         END IF;
     END LOOP;   -- cur_cit_attr


     IF v_rec_count = 0 THEN
         v_msg := 'There is no mismatch.';

         UTL_MAIL.send (
             sender       => v_mail_sender,
             recipients   => v_mail_recipients,
             subject      => v_database || ' - Items on database,
             message      => v_msg);

          dbms_output.put_line (v_msg);

      ELSIF v_rec_count >=1 THEN

         SELECT parm_val
           INTO v_dir_location
           

        SELECT TO_CHAR (SYSDATE, 'yyyymmdd')
           INTO v_file_date
           FROM DUAL;

       v_file_name := v_file_name ||v_file_date || '.txt';

       v_file_handle := UTL_FILE.fopen (v_dir_location, v_file_name, 'W', max_linesize=>32767);

       dbms_output.put_line ('File = ' ||v_file_name);
       dbms_output.put_line ('Unix dir '||v_dir_location); -- DEFAULT_DATA_DIR


       UTL_FILE.PUT(V_FILE_HANDLE,V_MSG);


       FOR rec_missing_returns IN cur_missing_returns

       LOOP
         v_report :=
               v_report
            || RPAD (rec_missing_returns.prog_program_cd, 9)
            || ' '
            || LPAD (TO_CHAR (rec_missing_returns.entp_abn), 9)
            || ' '
            || RPAD (TO_CHAR (rec_missing_returns.period_end_date, 'MM/DD/YYYY'), 12)
            || ' '
            || RPAD (rec_missing_returns.return_status, 13)
            || ' '
            || LPAD (TO_CHAR (rec_missing_returns.loctr_nbr), 10)
            || ' '
            || RPAD (rec_missing_returns.created_by, 10)
            || ' '
            || RPAD (
                  TO_CHAR (rec_missing_returns.created_tmst, 'MM/DD/YYYY HH24:MI:SS'),
                  20)
            || CHR (10);

             utl_file.put_line(v_file_handle, v_report, TRUE);


                           v_report:= null;

      END LOOP;  
      UTL_FILE.fclose (v_file_handle);

      v_file_attachment := BFILENAME (v_dir_location, v_file_name);

      DBMS_LOB.fileopen (v_file_attachment, DBMS_LOB.file_readonly);

      DBMS_LOB.read (v_file_attachment,
                     v_amt,
                     v_offset,
                     v_rawbuf);
                     

      dbms_output.put_line ('Attachement created');

      v_file_length := DBMS_LOB.getlength  (v_file_attachment);

      dbms_output.put_line ('File Length = '||v_file_length);

      IF v_file_length < v_filesize_limit THEN
      
      dbms_output.put_line ('Attachement ready to be sent');

        -- UTL_MAIL has capacity to pick up a text file for emailing purpose only if it is less 
        -- than 32KB.
      
         sys.UTL_MAIL.send_attach_raw (
         sender          => v_mail_sender,
         recipients      => v_mail_recipients,
         subject         => 'Items on Oracle and not on WMS',
         MESSAGE         => 'There are mismatches to the attached report.',
         att_mime_type   => 'text/plain; charset=us-ascii',
         attachment      => v_rawbuf,
         att_inline      => TRUE,
         att_filename    => v_file_name);
      ELSE
 
dbms_output.put_line ('Test File too big for attachement !');

          dbms_output.put_line ('File too big for attachement !');

          v_msg := 'Please ask to copy and email the file '||v_file_name;

          dbms_output.put_line (v_msg);

           UTL_MAIL.send (
             sender       => v_mail_sender,
            recipients   => v_mail_recipients,
            subject      => v_database || ' Items on system '||v_file_name||
                                                 '- file created - too big for attachement',
             message      => v_msg);

      END IF;

      DBMS_LOB.fileclose (v_file_attachment);

   END IF;
EXCEPTION 
   WHEN OTHERS THEN
        r_error_rt := util_backtrace_pk.backtrace_fn;
        dbms_output.put_line ('Oracle Error: ' || 
                               SQLCODE || 
                              '~cit_wms_rpt_pr~' || 
                              '~line~' || 
                               TO_CHAR(r_error_rt.line_number) || 
                              '~' || SQLERRM);
        v_msg_text := 'LINE: ' || TO_CHAR(r_error_rt.line_number) || ' UNEXPECTED ERROR';
        message_server_pk.put_message_PR('Error', 
                                         'CIT_WMS_RPT_PR',
                                          SQLCODE,
                                          V_MSG_TEXT,
                                          SQLERRM,
                                          USER);
        RAISE_APPLICATION_ERROR(-20001, 'CIT_WMS_RPT_PR ' || V_MSG_TEXT);
END;
/