CREATE TABLE pavan_teja
(
    id               INTEGER NOT NULL
   ,record_type      VARCHAR2 (10 BYTE) NOT NULL
   ,mbr_first_name   VARCHAR2 (50 BYTE) NOT NULL
   ,mbr_last_name    VARCHAR2 (50 BYTE) NOT NULL
   ,mbr_id           VARCHAR2 (10 BYTE)
   ,csr_amt          NUMBER (18, 4)
   ,rating_area      VARCHAR2 (5 BYTE)
);

ID    RECORD_TYPE    MBR_FIRST_NAME    MBR_LAST_NAME    MBR_ID        CSR_AMT    RATING_AREA
1     CMS            SHARON            CABERLY          0000046669    32.64      014
2     FAC            SHARON            CAVERLY          0000046669    32.64      14
3     CMS            JACK              PARROTTE         0000158332    0          014
4     FAC            JACK              PARROTT          0000158332    2          14
5     CMS            DELORES           LORANZO          0000179553    0          012
6     FAC            DELORES           LOZANO           0000179553    1          12

-- process behind the export to file button
DECLARE
    v_file_path       VARCHAR2 (200) := lib_owner.ph_oracle_environment.get_output_directory;
    v_delimiter       VARCHAR2 (2) := ',';
    v_file_handle     UTL_FILE.file_type;
    v_file_name       VARCHAR2 (50) := 'discrepancies';
    v_header          VARCHAR2 (32767);
    v_report_record   VARCHAR (32767);
BEGIN
    IF lib_owner.ph_oracle_environment.is_prod_db_yn = 'N'
    THEN
        v_file_name   := v_file_name || '_' || 'test' || '.csv';
    ELSE
        v_file_name   := v_file_name;
    END IF;

    --open file

    v_file_handle   := UTL_FILE.fopen (v_file_path, v_file_name, 'w');
    v_header        :=
           'Discrepancy Batch Id'
        || v_delimiter
        || 'Record Type'
        || v_delimiter
        || 'Exchange Member Id'
        || v_delimiter
        || 'Member First Name'
        || v_delimiter
        || 'Member Last Name'
        || v_delimiter
        || 'Exchange Subsciber Id'
        || v_delimiter
        || 'External Member Id'
        || v_delimiter
        || 'QHP Id'
        || v_delimiter
        || 'APTC Amount'
        || v_delimiter
        || 'CSR Amount'
        || v_delimiter
        || 'Tobacco User Code'
        || v_delimiter
        || 'Rating Area'
        || v_delimiter
        || 'Date of Birth';

    UTL_FILE.put_line (v_file_handle, v_header);

    FOR i IN 1 .. APEX_APPLICATION.g_f01.COUNT
    LOOP
        SELECT DISTINCT
                  disc_batch_id
               || v_delimiter
               || record_type
               || v_delimiter
               || exchange_mbr_id
               || v_delimiter
               || mbr_first_name
               || v_delimiter
               || mbr_last_name
               || v_delimiter
               || exchange_subsc_id
               || v_delimiter
               || ext_mbr_id
               || v_delimiter
               || qhp_id
               || v_delimiter
               || aptc_amt
               || v_delimiter
               || csr_amt
               || v_delimiter
               || tobacco_use_cd
               || v_delimiter
               || rating_area
               || v_delimiter
               || mbr_birth_date
          INTO v_report_record
          FROM indv_recon_disc
         WHERE disc_id IN (SELECT APEX_APPLICATION.g_f01 (i) FROM DUAL);

        UTL_FILE.put_line (v_file_handle, v_report_record);
    END LOOP;

    --close file
    UTL_FILE.fclose (v_file_handle);
END;