create or replace procedure xxaj_utl_file
as

v_file_handle    utl_file.file_type;
p_end_date date := '31-DEC-2016';

cursor c1 is

  select distinct employer_id,to_char(sysdate, 'YYMMDD') cuur_year,to_char (sysdate, 'HH24MISS') time_stamp
  from   xxnmc_hr_wps_employee_det
  where  income_fixed <> 0;
  
cursor c_supplier(p_employer_id number) is

  select z.employer_id, z.test_results
  from(
  select  employer_id,trim(edr)||','||personal_number||','||agent_id||','||account_number||','||pay_start_date||','||pay_end_date||','||days_in _period
         ||','||income_fixed||'.'||'00'||','||'0.00'||','||' 0' test_results
  from   xxnmc_hr_wps_employee_det
  where  income_fixed <> 0
  union 
  select employer_id,'SCR'||','||employer_id||','||nvl (bank_routing_number,'401920110')||','||to_char (sysdate, 'YYYY-MM-DD')
         ||','||to_char (sysdate, 'HH24MI')||','||to_char(to_date(pay_end_date,'YYYY-MM-DD'),'MMYYYY')||','||count(personal_number)
         ||','||sum(income_fixed)||'.'||'00'||','||'AED'||','||to_char(to_date(p_end_date,'DD-MON-YYYY'),'MONYY')
  from   xxnmc_hr_wps_employee_det
  where  income_fixed <> 0
  group by employer_id,'SCR'||','||employer_id||','||nvl (bank_routing_number,'401920110')||','||to_char (sysdate, 'YYYY-MM-DD')
         ||','||to_char (sysdate, 'HH24MI')||','||to_char(to_date(pay_end_date,'YYYY-MM-DD'),'MMYYYY')           
  ) z;
     
begin

  for r1 in c1 loop
 
    v_file_handle := utl_file.fopen('XXNMC_PAYROLL_WPS',r1.employer_id||r1.cuur_year||r1.time_stamp||'.SIF', 'W',32767);
    
  for c_rec in c_supplier (r1.employer_id) loop
  
    utl_file.put_line(v_file_handle,c_rec.test_results);  
   
    dbms_output.put_line('Read access OK');
    
  end loop;
  
    utl_file.fclose(v_file_handle);

  commit;
  
  end loop;

exception
  when others then
    utl_file.fclose_all;
    dbms_output.put_line('other exception');

end xxaj_utl_file;
/

exception
  when others then
    utl_file.fclose_all;
    dbms_output.put_line('other exception');