Hi Tom,

This is the file generated by my procedure:

"FV","3.0"
"FH","20170404","001","1","loftpaypal@loft.com"
"BH","1","USD","loftpaypal@loft.com"
"1","RF","03F359879C231283P","USD","13.99","","591111895"
"2","RF","0FA889952A037821C","USD","19.95","","591411705"
"BF","USD","","","2","33.94","",""
"FF","1"
"CS","69D0A4C9FF9318163E5B581EC2DD1268"

There are 8 lines, but there is an extra line 9th one which is blank. I need to get rid of that one. I'm using 2 procedures. One to generate the file and another to calculate the checksum value. I'm calling the checksum procedure in the main procedure. Here are the procedures I'm using:

------------------------------------------------------------------------------------------------------
-- Main Procedure -- 

create or replace PROCEDURE return_settlement
 IS

   WS_SYSTEM_DATE DATE := SYSDATE;
   v_OutputFile      UTL_FILE.FILE_TYPE;
   

   pp_fileName varchar2(300);
   FV_FILEVERSION NUMBER := 3.0;
   FH_FILEDATE CHAR(10) := TO_CHAR(WS_SYSTEM_DATE,'yyyymmdd');
   FH_FILESEQUENCENUMBER NUMBER := 001;
   

   fh_batchCount NUMBER :=0;
   fh_partnerEmailAddress varchar2(254):= 'xyz@gmail.com';
 
   PP_FH VARCHAR2(300);
   bh_batchId NUMBER :=0;
   BH_CURRENCYTYPE CHAR(4):='USD';
   merchant_id VARCHAR2(54):= 'Y8G7F3A9LJ6JQ_';
   bh_merchantEmailAddress varchar2(254):= 'xyz@gmail.com';
  

   rf_itemNumber NUMBER :=0;
   rf_transactionID varchar2(17);
   rf_currencyType CHAR(4):='USD';
   rf_amount NUMBER :=0;
   rf_memo varchar2(254);
   rf_invoiceID varchar2(17);

   bf_currencyType CHAR(4):='USD';

   ff_batchCount NUMBER :=0;
  

   PP_CS_CHECKSUM varchar2(32);
   V_OUT_CLOB CLOB;
   
   TTL_CR_TXN_AMT number;

  
   OutputFile UTL_FILE.FILE_TYPE;
   p_dir varchar2(50) := 'Q:\interface\outbound';
   p_filename varchar2(50) := 'Y8G7F3A9LJ6JQ_'||'REQUEST_'||TO_CHAR(SYSDATE,'YYYY-MM-DD')||'-'||LPAD(fh_fileSequenceNumber,3,0)||'.csv';
  

  CURSOR return_record  IS
     select REFERENCE_NO,amount,Invoice_ID from (
      SELECT B.REFERENCE_NO,B.GROSS_AMOUNT AS AMOUNT, a.store_no,a.transaction_no,a.transaction_date,
       (select line_note from aw_transaction_notes where aw_entry_id=a.aw_entry_id and note_type=53 and rownum <=1) as Invoice_ID
    FROM aw_transaction_header a, aw_transaction_detail b, store_salesaudit c, user_store d
    WHERE 
    a.store_no NOT IN (611,612)
    AND B.LINE_OBJECT  IN (711)
    AND A.AW_ENTRY_ID = B.AW_ENTRY_ID
    AND A.STORE_NO = C.STORE_NO
    AND A.STORE_NO = D.STORE_NO--
    and a.control_flag = 10
    and c.gl_company <> 2400
    AND D.DIVISION_CODE = 2
    and b.reference_no is not null)
    group by REFERENCE_NO,amount,Invoice_ID;  

       CURSOR return_record1  IS     
    select sum(AMOUNT) as TTL_CR_TXN_AMT  from (SELECT B.REFERENCE_NO,B.GROSS_AMOUNT AS AMOUNT
    FROM aw_transaction_header a, aw_transaction_detail b, store_salesaudit c, user_store d
    WHERE 
    a.store_no NOT IN (611,612)
    AND B.LINE_OBJECT  IN (711)
    AND A.AW_ENTRY_ID = B.AW_ENTRY_ID
    AND A.STORE_NO = C.STORE_NO
    and a.STORE_NO = D.STORE_NO--
    and a.CONTROL_FLAG = 10
    and c.gl_company <> 2400
    AND D.DIVISION_CODE = 2
    AND B.REFERENCE_NO IS NOT NULL
    group by b.REFERENCE_NO,b.Gross_amount); 
  
  


BEGIN



  v_OutputFile := UTL_FILE.FOPEN('Q:\interface\outbound','Y8G7F3A9LJ6JQ_'||'REQUEST_'||TO_CHAR(SYSDATE,'YYYY-MM-DD')||'-'||LPAD(fh_fileSequenceNumber,3,0)||'.csv','W');

   FOR i in 1..100
  LOOP
  if ff_batchCount<=98 then
  ff_batchCount:= ff_batchCount+1;
  else
  bh_batchId:= bh_batchId+1;
  ff_batchCount:= 1;
  end if;
  END LOOP;
  


      V_OUT_CLOB := V_OUT_CLOB||'"'||'FV'||'"'||','||'"'||'3.0'||'"'|| CHR(13);

      V_OUT_CLOB := V_OUT_CLOB||'"'||'FH'||'"'||','||'"'||trim(FH_FILEDATE)||'"'||','||'"'|| LPAD(fh_fileSequenceNumber,3,0)||'"'||','||'"'||FF_BATCHCOUNT||'"'||','||'"'||'xyz@gmail.com'||'"'|| CHR(13);
      V_OUT_CLOB := V_OUT_CLOB||'"'||'BH'||'"'||','||'"'||BH_BATCHID||'"'||','||'"'||'USD'||'"'||','||'"'||'xyz@gmail.com'||'"'|| CHR(13);


  
  FOR return_record_REC IN return_record
  LOOP
  RF_ITEMNUMBER:= RF_ITEMNUMBER+1;
V_OUT_CLOB := V_OUT_CLOB||'"'||rf_itemNumber||'"'||','||'"'||'RF'||'"'||','||'"'||return_record_REC.REFERENCE_NO||'"'||','||'"'||'USD'||'"'||','||'"'||return_record_REC.AMOUNT||'"'||','||'"'||''||'"'||','||'"'||trim(return_record_REC.Invoice_ID)||'"'|| CHR(13);
 END LOOP;
 
 FOR return_record_REC1 IN return_record1
 LOOP 
 TTL_CR_TXN_AMT := return_record_REC1.TTL_CR_TXN_AMT;
 END LOOP;

V_OUT_CLOB := V_OUT_CLOB||'"'||'BF'||'"'||','||'"'||TRIM(BF_CURRENCYTYPE)||'"'||','||'"'||''||'"'||','||'"'||''||'"'||','||'"'||RF_ITEMNUMBER||'"'||','||'"'||TTL_CR_TXN_AMT||'"'||','||'"'||''||'"'||','||'"'||''||'"'|| CHR(13);

V_OUT_CLOB := V_OUT_CLOB||'"'||'FF'||'"'||','||'"'||FF_BATCHCOUNT||'"';


UTL_FILE.PUT(V_OUTPUTFILE, V_OUT_CLOB);

UTL_FILE.FCLOSE(V_OUTPUTFILE);

ADD_CHECKSUM1(V_OUT_CLOB, P_DIR, P_FILENAME ); -- This is the procedure for checksum being called.

  COMMIT;

  EXCEPTION
 WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('ERROR = '||TO_CHAR(SQLCODE)||' '||SQLERRM);
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('ERROR = '||TO_CHAR(SQLCODE)||' '||SQLERRM);
END return_settlement;

------------------------------------------------------------------------------------------------------
-- checksum procedure -- 

create or replace procedure ADD_CHECKSUM1( P_CLOB IN CLOB, P_DIR IN VARCHAR2, P_FILENAME IN VARCHAR2)
is
    l_hash      varchar2(32);
    l_output  utl_file.file_type;
    l_offset  number := 1;
    L_LEN     NUMBER := DBMS_LOB.GETLENGTH(P_CLOB);
    L_AMT     number := 32765;
    V_CLOB clob;
begin    
    L_HASH := RAWTOHEX(DBMS_CRYPTO.hash( P_CLOB,DBMS_CRYPTO.HASH_MD5));
    L_HASH := ltrim(l_hash,CHR(10)||CHR(13));
    
    L_OUTPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'w' );

    V_CLOB := P_CLOB||CHR(10)||'"'||'CS'||'"'||','||'"'||L_HASH||'"';

     UTL_FILE.PUT(L_OUTPUT,V_CLOB);

    utl_file.fclose( l_output );
end;

------------------------------------------------------------------------------------------------------