SET VERIFY OFF
SET SERVEROUTPUT ON SIZE 1000000


DECLARE
    companyID  varchar2(15);
    V_OUTPUT VARCHAR2(2000);
    V_HEADER VARCHAR(2000);
    V_PRODUCTION_PERIOD	 DATE   := '&&1';

    v_taxhdl varchar2(1);
    v_accounting_date number(6);
    v_production_date number(6);
    v_commodity varchar2(2);

    p_filename varchar2(200);
    fHandler UTL_FILE.FILE_TYPE;

    --Getting the company codes into companyID variable
   CURSOR C1 IS

        SELECT DISTINCT company as companyID
         FROM myTable
         WHERE (commodity = 'CN'
         AND  accounting_date = TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE('&&1','DD-MON-YYYY'),-1),'yyyymm'))
         AND production_date = TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE('&&1','DD-MON-YYYY'),-1),'yyyymm')))

    OR (commodity != 'CN'
        AND accounting_date = TO_NUMBER(TO_CHAR(TO_DATE('&&1','DD-MON-YYYY'),'yyyymm'))
        AND production_date = TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE('&&1','DD-MON-YYYY'),-1),'yyyymm')))

       ORDER BY company;

 CURSOR C2(p_company number) IS

            SELECT   taxhdl, accounting_date, production_date, commodity

             FROM myTable
             WHERE (commodity = 'CN'

              AND ACCOUNTING_DATE = to_number(to_char(ADD_MONTHS(TO_DATE('&&1','DD-MON-YYYY'),-1),'yyyymm'))
              AND company = p_company )
                    
             OR (commodity != 'CN'

           AND ACCOUNTING_DATE = to_number(to_char(TO_DATE('&&1','DD-MON-YYYY'),'yyyymm'))
           AND company = p_company )
                 

            GROUP BY taxhdl, accounting_date,production_date,commodity
            ORDER BY commodity, production_date;

     --Setting the the Heading of the file
  BEGIN

  V_HEADER := ('COMPANY_ID'||','||'TAXHDL' || ','  || 'ACCOUNTING_DATE'|| ','  ||'PRODUCTION_DATE' || ',' ||'COMMODITY');
                
 
   FOR REC IN C1 LOOP
           companyID := REC.companyID;
           p_filename := companyID||'.SUM'||'&&1'||'.csv';

          FOR C2_REC IN C2(companyID) LOOP

             v_taxhdl := C2_REC.taxhdl;
             v_accounting_date := C2_REC.accounting_date;
             v_production_date := C2_REC.production_date;
             v_commodity := C2_REC.commodity;
                

             V_OUTPUT := (companyID ||','||v_taxhdl|| ',' ||v_accounting_date||',' ||
             v_production_date||','||v_commodity);
             
               /*The part needs fixing*/                          
             fHandler := UTL_FILE.FOPEN('myDir',p_filename,'w'); 
             UTL_FILE.PUTF(fHandler,V_HEADER||'\n');
             UTL_FILE.PUTF(fHandler,V_OUTPUT);
             UTL_FILE.FCLOSE(fHandler);           
          END LOOP;      
--          exception
--            when NO_DATA_FOUND then
--                utl_file.fclose(f);
--
   END LOOP;
    
END;