Procedure With text file creation in pl/sql

CREATE OR REPLACE PROCEDURE APPS.PRC_NAME( errbuf  OUT  VARCHAR2
                                                      , retcode             OUT     VARCHAR2
                                                      , p_filename                     VARCHAR2
                                                      , p_file_path                     VARCHAR2
                                                      , p_bank_account_id          VARCHAR2
                                                      , p_stock_id                      NUMBER
                                                      , p_batch_name                 VARCHAR2
                                                      , p_start_check_date           VARCHAR2
                                                      , p_end_check_date           VARCHAR2
                                                      , p_start_check_num          VARCHAR2
                                                      , p_end_check_num            VARCHAR2
)
AS

    CURSOR C1 ( p_bank_account_id          IN        NUMBER
              , p_stock_id                 IN        NUMBER
              , p_batch_name            IN        VARCHAR2
              , p_start_check_date      IN         Date
              , p_end_check_date        IN         Date
              , p_start_check_num       IN         NUMBER
              , p_end_check_num         IN         NUMBER
            )
           
    IS
   
         /* Formatted on 5/22/2013 2:13:03 PM (QP5 v5.114.809.3010) */
  SELECT   TRIM (
              DECODE (INSTR (pv.segment1 || '-' || pvs.vendor_site_code,
                             ',',
                             1,
                             1),
                      0, pv.segment1 || '-' || pvs.vendor_site_code,
                      '"' || pv.segment1 || '-' || pvs.vendor_site_code || '"')
           )
              VENDOR_ID,
           TRIM (ai.invoice_num) Line_item_remit_comment_1,
           TRIM (TO_CHAR (ai.invoice_date, 'MM/DD/RRRR'))
              Line_item_remit_comment_2,
           TRIM (DECODE (INSTR (ai.invoice_amount,
                                ',',
                                1,
                                1),
                         0, ai.invoice_amount,
                         '"' || ai.invoice_amount || '"'))
              item_unit_price,
           ai.invoice_id
    FROM   ap_checks_all chk,
           CE_PAYMENT_DOCUMENTS acs,
           CE_BANK_ACCOUNTS bka,
           CE_BANK_BRANCHES_V bkb,
           ap_suppliers pv,
           ap_invoice_payments_all aip,
           ap_invoices_all ai,
           AP_SUPPLIER_SITES_ALL pvs
   WHERE       2 = 2
           AND chk.PAYMENT_DOCUMENT_ID = acs.PAYMENT_DOCUMENT_ID
           AND acs.INTERNAL_BANK_ACCOUNT_ID = bka.bank_account_id
           AND bka.bank_branch_id = bkb.BRANCH_PARTY_ID
           AND chk.VENDOR_ID = pv.vendor_id
           AND aip.check_id = chk.check_id
           AND aip.invoice_id = ai.invoice_id
           AND pvs.vendor_id = pv.vendor_id
           AND pvs.vendor_site_code = chk.vendor_site_code
           AND chk.STATUS_LOOKUP_CODE = 'NEGOTIABLE'
           AND bka.bank_account_id =
                 NVL (p_bank_account_id, bka.bank_account_id)
           --AND acs.check_stock_id    =
                 -- NVL(p_stock_id,acs.check_stock_id)  -- 11i
           AND acs.PAYMENT_DOCUMENT_ID =
                 NVL (p_stock_id, acs.PAYMENT_DOCUMENT_ID)  -- R12
           AND chk.check_date >= p_start_check_date
           AND chk.check_date <= p_end_check_date
           AND check_number >= NVL (p_start_check_num, check_number)
           AND check_number <= NVL (p_end_check_num, check_number)
           AND chk.checkrun_name = NVL (p_batch_name, chk.checkrun_name)
            -- AND bkb.bank_branch_name = 'INTERNAL'
GROUP BY   TRIM(DECODE (
                   INSTR (pv.segment1 || '-' || pvs.vendor_site_code,
                          ',',
                          1,
                          1),
                   0,
                   pv.segment1 || '-' || pvs.vendor_site_code,
                   '"' || pv.segment1 || '-' || pvs.vendor_site_code || '"'
                )),
           ai.invoice_num,
           TO_CHAR (ai.invoice_date, 'MM/DD/RRRR'),
           DECODE (INSTR (ai.invoice_amount,
                          ',',
                          1,
                          1),
                   0, ai.invoice_amount,
                   '"' || ai.invoice_amount || '"'),
           ai.invoice_id
ORDER BY   3, 2;



--    CURSOR C2(p_invoice_num IN VARCHAR2)
    CURSOR C2(p_invoice_id IN NUMBER)
    IS
    select distinct '~' || 'PO# ' || ph.segment1 po_num
    from po_headers ph
       , po_distributions pd
       , ap_invoice_distributions aid
       , ap_invoices ai
    where pd.po_header_id = ph.po_header_id
      AND aid.po_distribution_id = pd.po_distribution_id
      AND ai.invoice_id = aid.invoice_id
      AND ai.invoice_id = p_invoice_id;




    --Type rec_c1 is record of c1%rowtype;

    SUBTYPE rec_c1 IS c1%rowtype;

    --type ree is record (rec_c1 c1%rowtype);

    type c1_table is table of rec_c1 index by binary_integer;

    --type c1_table is table of rec_c1; -- index by binary_integer;

    c1_matrix c1_table;

    v_record_type         VARCHAR2 ( 10 );
    v_check_no            NUMBER;
    v_eft_amount          NUMBER;
    v_check_date          DATE;
    v_vendor_name         VARCHAR2 ( 100 );
    v_bank_account_num    VARCHAR ( 30 );
    v_countflag1          NUMBER             := 0;
    v_countflag2          NUMBER             := 0;
    v_countflag3          NUMBER             := 0;
    v_amountflag1         NUMBER             := 0;
    v_amountflag2         NUMBER             := 0;
    v_amountflag3         NUMBER             := 0;
    v_totalamount_flag    NUMBER             := 0;
    v_totalcount_flag     NUMBER             := 0;
    v_sqlerrm             VARCHAR2 ( 200 );
    v_msg                 VARCHAR2 ( 100 );
    v_vendor_id           NUMBER;
    v_Paying_Company      VARCHAR2 ( 50 );
    v_Paying_Company_id   NUMBER;
    v_vendor_site_id      VARCHAR2(50);
    v_check_id            NUMBER;
    v_po_number           VARCHAR2(30);
    v_comment             VARCHAR2(1000);

    /*Defining Variables for the UTL_FILE*/
    v_filename            VARCHAR2 ( 100 );
    v_handle              UTL_FILE.FILE_TYPE;
    v_output              VARCHAR2 ( 4000 );
    l_delimiter           CONSTANT VARCHAR2 ( 1 )     := ',';
   
 
    l_bank_account_id          NUMBER(15,0);
    l_stock_id                 NUMBER(10,0);
    l_start_check_date         Date;
    l_end_check_date           Date;
    l_start_check_num          NUMBER;
    l_end_check_num            NUMBER;
    l_file_path                VARCHAR2(240);
    l_filename                 VARCHAR2(240);
    l_batch_name               VARCHAR2(50);
BEGIN

    fnd_file.put_line ( FND_FILE.LOG, 'Entered Program Logic.');


    /* Fetching account_id from account number*/ 
    BEGIN
        SELECT bank_account_id
          INTO l_bank_account_id
      FROM ap_bank_accounts_all
         WHERE bank_account_id = p_bank_account_id;
    EXCEPTION
        WHEN OTHERS THEN
                v_sqlerrm := SUBSTR ( SQLERRM, 1, 200 );
                Fnd_File.put_line ( Fnd_File.LOG,    'Error Writing Payment Record - ' || '  ' || v_sqlerrm);
                Fnd_File.put_line ( Fnd_File.OUTPUT,    'Error Writing Payment Record - ' || '  ' || v_sqlerrm);
    END;


    l_bank_account_id          := p_bank_account_id;
    l_stock_id                 := p_stock_id;
    l_start_check_date         := to_date(substr(nvl(p_start_check_date,'1951/01/01'), 1, 10 ), 'YYYY/MM/DD');
    l_end_check_date           := to_date(substr(nvl(p_end_check_date,'4712/12/31'), 1, 10 ), 'YYYY/MM/DD');
    l_start_check_num          := p_start_check_num;
    l_end_check_num            := p_end_check_num;
    l_file_path                := p_file_path;
    l_filename                 := p_filename;
    l_batch_name               := p_batch_name;


    Fnd_File.put_line ( Fnd_File.LOG, 'PARAMETERS PASSED :' );
    Fnd_File.put_line ( Fnd_File.LOG, '-------------------');
    Fnd_File.put_line ( Fnd_File.LOG, 'Output File Path :' || l_file_path|| '*' );
    Fnd_File.put_line ( Fnd_File.LOG, 'Account id :' || l_bank_account_id || '*');
    Fnd_File.put_line ( Fnd_File.LOG, 'Stock   id :' || l_stock_id || '*');
    Fnd_File.put_line ( Fnd_File.LOG, 'Batch Number :' || l_batch_name || '*');
    Fnd_File.put_line ( Fnd_File.LOG, 'Payment Date from :' || l_start_check_date || '*');
    Fnd_File.put_line ( Fnd_File.LOG, 'Payment Date to :' || l_end_check_date || '*');
    Fnd_File.put_line ( Fnd_File.LOG, 'Payment Number from :' || l_start_check_num || '*');
    Fnd_File.put_line ( Fnd_File.LOG, 'Payment Number to :' || l_end_check_num || '*');
    Fnd_File.put_line ( Fnd_File.LOG, 'Output File Name :' || l_filename || '*' );
    FND_FILE.put_line ( Fnd_File.LOG, '' );


    /*Opening Cursor and Fetching the data*/
    fnd_file.put_line ( FND_FILE.LOG, 'Opening Cursor.');

    OPEN C1 (  l_bank_account_id
          , l_stock_id
          , l_batch_name
          , l_start_check_date
          , l_end_check_date
          , l_start_check_num
          , l_end_check_num
          );

    fnd_file.put_line ( FND_FILE.LOG, 'Cursor open... fetching now...');

    FETCH C1
    bulk collect into c1_matrix;

    v_countflag1 := 0;

    fnd_file.put_line ( FND_FILE.LOG, 'Entering Loop');


    If C1%rowcount > 0 Then

        /* Adding format prefix, postfix and extension to the user given file name and opening the UTIL file*/

        if p_filename is null then
            v_filename := '101004992_PNC' || '_' || to_char(sysdate, 'mmddyyyy') || '.txt';
        else
            v_filename := '101004992_PNC' || '_' || to_char(sysdate, 'mmddyyyy') || '_' || p_filename || '.txt';
        end if;

     v_handle := UTL_FILE.FOPEN ( l_file_path, v_FileName, 'W', 4500 );

        /*Adding some information to the output file*/
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'OUTPUT FOR THE PROGRAM - MasTec ePayables BOA Extract');
        Fnd_File.put_line ( Fnd_File.OUTPUT, '--------------------------------------------------------------');
        FND_FILE.put_line ( Fnd_File.OUTPUT, '' );
        FND_FILE.put_line ( Fnd_File.OUTPUT, '****************************************************************************************');
        FND_FILE.put_line ( Fnd_File.OUTPUT, 'NOTE: - The Output below is for user reference/information purpose only!');
        FND_FILE.put_line ( Fnd_File.OUTPUT, '        PLEASE DO NOT SEND THIS OUTPUT TO BANK.');
        FND_FILE.put_line ( Fnd_File.OUTPUT, '        A file by name ' || v_filename || ' is created at \\co-mia-stgprd1\MoveitCentral\ES\WORKS'
                                            || 'to be sent to bank.');
        FND_FILE.put_line ( Fnd_File.OUTPUT, '****************************************************************************************');
        FND_FILE.put_line ( Fnd_File.OUTPUT, '' );
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'PROGRAM RUN DATE :' || TO_CHAR ( SYSDATE, 'DD-MON-YYYY HH24:MI:SS' ));
        Fnd_File.put_line ( Fnd_File.OUTPUT, '---------------------------------------------------------------');
        FND_FILE.put_line ( Fnd_File.OUTPUT, '' );
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'PARAMETERS PASSED :' );
        Fnd_File.put_line ( Fnd_File.OUTPUT, '-----------------------------------------------');
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Account id :' || l_bank_account_id || '*');
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Stock id :' || l_stock_id || '*');
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Batch Number :' || l_batch_name || '*');
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Payment Date from :' || l_start_check_date || '*');
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Payment Date to :' || l_end_check_date || '*');
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Payment Number from :' || l_start_check_num || '*');
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Payment Number to :' || l_end_check_num || '*');
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Output File Name :' || l_filename || '*' );
        FND_FILE.put_line ( Fnd_File.OUTPUT, '' );
        FND_FILE.put_line ( Fnd_File.OUTPUT,    'Below Similar Output Will Print In TEXT FILE Named :' || v_filename);
        Fnd_File.put_line ( Fnd_File.OUTPUT, '------------------------------------------------------------------');


        fnd_file.put_line ( FND_FILE.LOG, 'Done printing Parameters to CP output.');




          /*Writing the header value as per the format*/
        v_output := NULL;
        v_output := --'Payment Id'
                            --|| l_delimiter
                             'Vendor Id'
                            || l_delimiter
                            || 'Line Item Remit Comment 1'
                            || l_delimiter
                            || 'Line Item Remit Comment 2'
                            || l_delimiter
                            || 'Item Unit Price'
                            || l_delimiter;
              

        fnd_file.put_line ( FND_FILE.LOG, 'Prepared output header.');

        FND_FILE.put_line ( Fnd_File.OUTPUT, v_output);

        fnd_file.put_line ( FND_FILE.LOG, 'Done printing output header.');


        FOR i in c1_matrix.FIRST..c1_matrix.LAST LOOP

            fnd_file.put_line ( FND_FILE.LOG, 'Loop:' || i);

            /*Starting one more block, so that any if any exception comes, it will terminate from the block and continue with the loop*/

            BEGIN
               
                fnd_file.put_line ( FND_FILE.LOG, 'Preparing Comment with PO List');

                v_comment := c1_matrix(i).Line_item_remit_comment_1;

                fnd_file.put_line ( FND_FILE.LOG, 'Comment=' || v_comment);
                fnd_file.put_line ( FND_FILE.LOG, 'Looping for comment');

                FOR rec_C2 in C2(c1_matrix(i).invoice_id) LOOP

                    v_comment := v_comment || rec_c2.po_num;

                    fnd_file.put_line ( FND_FILE.LOG, 'Looping for comment. Comment = ' || v_comment);

                END LOOP;

                fnd_file.put_line ( FND_FILE.LOG, 'Outside comment loop. Comment = ' || v_comment);
               

                   v_output := substr ( c1_matrix(i).vendor_id, 1, 50 )
                            || l_delimiter
                            || substr ( v_comment, 1, 64 )
                            || l_delimiter
                            || substr ( c1_matrix(i).Line_item_remit_comment_2, 1, 64 )
                            || l_delimiter
                            || substr ( c1_matrix(i).item_unit_price, 1, 64);
                                                      
                            fnd_file.put_line ( FND_FILE.LOG, 'Output value  = ' || v_output);

                /*Writing the output variable to the UTIL File*/
            
               v_handle := utl_file.FOPEN(l_file_path, v_filename, 'AB');
            
               v_output := v_output || chr(13); 
              
                fnd_file.put_line ( FND_FILE.LOG, 'Output value file  = ' || v_output);
              
                           
               v_output := UTL_RAW.CAST_TO_RAW (v_output);
              
              UTL_FILE.PUT_RAW( v_handle,v_output);
                   
               UTL_FILE.FFLUSH(v_handle);
              
               fnd_file.put_line ( FND_FILE.LOG, 'Output value file1  = ' || v_output);
                
               
                fnd_file.put_line ( FND_FILE.LOG, 'Output line printed');   
                
                
                  v_countflag1 := v_countflag1 + 1;
       
         
            EXCEPTION
                WHEN OTHERS THEN
                    v_sqlerrm := SUBSTR ( SQLERRM, 1, 200 );
                    Fnd_File.put_line ( Fnd_File.LOG,    'Error Writing Payment Record - ' || '  ' || v_sqlerrm);
                    Fnd_File.put_line ( Fnd_File.OUTPUT,    'Error Writing Payment Record - ' || '  ' || v_sqlerrm);
            END;
        END LOOP;


        CLOSE C1;       

        

        fnd_file.put_line ( FND_FILE.LOG, 'Closed Cursor');
       
              UTL_FILE.FCLOSE ( V_handle );    
               
                

        fnd_file.put_line ( FND_FILE.LOG, 'Closed File. EXIT.');

    ELSE    /* When there are no Payments to process */

        fnd_file.put_line ( FND_FILE.LOG, 'No Payments available for printing to file.');

        Fnd_File.put_line ( Fnd_File.OUTPUT, '');
        Fnd_File.put_line ( Fnd_File.OUTPUT, '');
        Fnd_File.put_line ( Fnd_File.OUTPUT, '----------------------------ERROR--------------------------------------');
        FND_FILE.put_line ( Fnd_File.OUTPUT, 'None of the payments matched your criteria.');
        FND_FILE.put_line ( Fnd_File.OUTPUT, 'Please check that your payment batch is proper and that you are not limiting payments with any other parameter while submitting the program.');
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Payment file is not created.');
        Fnd_File.put_line ( Fnd_File.OUTPUT, '');
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'PARAMETERS PASSED :' );
        Fnd_File.put_line ( Fnd_File.OUTPUT, '-------------------');
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Account Number :' || p_bank_account_id);
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Stock ID :' || p_Stock_id);
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Batch Number :' || p_batch_name);
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Payment Date from :' || p_start_check_date);
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Payment Date to :' || p_end_check_date);
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Payment Number from :' || p_start_check_num);
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Payment Number to :' || p_end_check_num);
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Output File Name :' || p_filename);
        FND_FILE.put_line ( Fnd_File.OUTPUT, '' );
        Fnd_File.put_line ( Fnd_File.OUTPUT, '-----------------------------------------------------------------------');
        Fnd_File.put_line ( Fnd_File.OUTPUT, '');
        Fnd_File.put_line ( Fnd_File.OUTPUT, '');


    END IF;

    fnd_file.put_line ( FND_FILE.LOG, 'Outside Loop');



    --Total No.of Records - transaction summary of output file.
    Fnd_File.put_line ( Fnd_File.OUTPUT, ' ' );
    Fnd_File.put_line ( Fnd_File.OUTPUT, '-------------------------------------------------------------------------------------------------');
    Fnd_File.put_line ( Fnd_File.OUTPUT, 'Summary of the EFT Details in The Output File:');
    Fnd_File.put_line ( Fnd_File.OUTPUT, '-------------------------------------------------------------------------------------------------');
    Fnd_File.put_line ( Fnd_File.OUTPUT,    'Total Count Of Transactions Printed In Output File :' || v_countflag1);
    Fnd_File.put_line ( Fnd_File.OUTPUT, '-------------------------------------------------------------------------------------------------');

        fnd_file.put_line ( FND_FILE.LOG, 'Done. EXIT.');

EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN
        fnd_file.put_line ( FND_FILE.LOG, 'Error in opening file invalid path ');
        fnd_file.put_line ( FND_FILE.OUTPUT, 'Error in opening file invalid path ');
    WHEN UTL_FILE.INVALID_MODE THEN
        fnd_file.put_line ( FND_FILE.LOG, 'Error in opening file invalid mode ');
        fnd_file.put_line ( FND_FILE.OUTPUT, 'Error in opening file invalid mode ');
    WHEN UTL_FILE.INVALID_OPERATION THEN
        fnd_file.put_line ( FND_FILE.LOG, 'Error in opening file invalid operation ');
        fnd_file.put_line ( FND_FILE.OUTPUT, 'Error in opening file invalid operation ');
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
        fnd_file.put_line ( FND_FILE.LOG, 'Error in opening file invalid filehandle ');
        fnd_file.put_line ( FND_FILE.OUTPUT, 'Error in opening file invalid filehandle ');
    WHEN UTL_FILE.WRITE_ERROR THEN
        fnd_file.put_line ( FND_FILE.LOG, 'Error in opening file write error ');
        fnd_file.put_line ( FND_FILE.OUTPUT, 'Error in opening file write error ');
    WHEN OTHERS THEN
        v_msg := 'ERROR: ' || ' ' || SUBSTR ( SQLERRM, 1, 200 );
        fnd_file.put_line ( FND_FILE.LOG, 'Error in opening file ' || p_FileName || v_msg);
        Fnd_File.put_line ( Fnd_File.OUTPUT, 'Main Error Due to: ' || v_msg );
END PRC_NAME;