------------------------------------------------
    -- getXML
    -------------------------------------------------
    
    procedure getXML(
        p_wo_id  number,
        p_doc_id number
    )
    as
        v_ret clob;
    --    v_ret varchar2(32767);
        
        v_up_type varchar2(400);
        v_down_type varchar2(400);
    begin

        v_ret := build_xml_start_str;
        
        v_ret := build_xml_tag_str ( v_ret, 'ctworkorder version="1.0"' );        
        v_ret := build_xml_debug_str ( v_ret, p_wo_id );        
        v_ret := build_xml_tag_str ( v_ret, 'workorders' );
        
        for rec in (

        select 

           a.work_order_id,
           a.created_date  created_date,
           a.completed_comments,
        trim( a.wono ) workorder_name,
            'Contract ' || b.contract_code || ' WA# n' || b.group_id project,
            bbb.contractor_name PP_CUSTOM10,
            c.area C_AREA,
      --      a.h_histkey,
            a.created_by,
                  
           bb.po_number C_PONUMBER,
      
       
       
      --     d.length PP_LENGTH,
           d.material PP_MATERIAL,
      
            case when f.map_to is null then 'Z'
                 else f.map_to     
            end PP_SHAPE,
       
            a.h_assetid    asset_name,
            e.compcode asset_type,
             
            c.unitid up_name,
            c.unitid2 down_name,
            c.unittype unit_type
             
        from work_orders a
        inner join wo_groups b
        on a.group_id = b.group_id
        
        inner join contracts bb
        on b.contract_code = bb.contract_code
        
        inner join contractors bbb
        on bbb.contractor_id = bb.contractor_id
        
        left join VM_HANSEN_COMP c
        on a.h_compkey = c.compkey
        
        left join VM_HANSEN_COMP_CMS cc
        on a.h_compkey = cc.compkey
        
        left join v_hansen_wo_cctv d 
        on trim(a.wono) = trim(d.wono)
        
        left join v_hansen_comptype e  
        on e.comptype = c.comptype
        
        left join report_type_mapping f 
        on a.h_shape = f.map_from
        
        where a.group_id = p_wo_id
    

        ) loop
        
        
            v_ret := build_xml_wo_str ( v_ret, rec.workorder_name, rec.created_date );
            v_ret := build_xml_spec_tag_str ( v_ret, 'description', rec.completed_comments );
    
            -------------------------------------
            -- interventions
            -------------------------------------
            
            v_ret := build_xml_tag_str ( v_ret, 'interventions' );
            
            v_ret := build_xml_intevention_str ( v_ret, rec.asset_type, '{6AE3AB88 256F 4AF9 A345 17CA5A215A88}' );
            v_ret := build_xml_tag_str ( v_ret, 'fields' ); 
            
            v_ret := build_xml_str ( v_ret, 'CP_PROJECTNUMBER', rec.project );
            v_ret := build_xml_str ( v_ret, 'C_CITY', 'Toronto' );
            v_ret := build_xml_str ( v_ret, 'C_AREA', rec.C_AREA );
            v_ret := build_xml_str ( v_ret, 'P_OWNER', 'City of Toronto' );
            v_ret := build_xml_str ( v_ret, 'PP_PURPOSE', 'A' );
            v_ret := build_xml_str ( v_ret, 'PP_PONUMBER', rec.C_PONUMBER );
            v_ret := build_xml_str ( v_ret, 'PP_CUSTOMER', 'Operations Contract Services' );
                 
            v_ret := build_xml_tag_str ( v_ret, '/fields' );
            v_ret := build_xml_tag_str ( v_ret, '/intervention' );
            v_ret := build_xml_tag_str ( v_ret, '/interventions' );
            
                  
            -------------------------------------
            -- assets
            -------------------------------------
            
            if rec.up_name is not null and rec.down_name is not null then
                select unittype into v_up_type from vm_hansen_comp where unitid = rec.up_name and unitid2 is null;
                select unittype into v_down_type from vm_hansen_comp where unitid = rec.down_name and unitid2 is null;
            
            end if;
        
            v_ret := build_xml_tag_str ( v_ret, 'assets' );
        
           v_ret := build_xml_asset_str ( v_ret, rec.asset_name, rec.asset_type, rec.up_name, v_up_type, rec.down_name, v_down_type ); 
    
           v_ret := build_xml_tag_str ( v_ret, 'fields' );
           
            v_ret := build_xml_str ( v_ret, 'PP_MATERIAL', rec.PP_MATERIAL );
            v_ret := build_xml_str ( v_ret, 'PP_SHAPE', rec.PP_SHAPE );
            v_ret := build_xml_str ( v_ret, 'PP_CUSTOM10', rec.PP_CUSTOM10 );
            
            v_ret := build_xml_tag_str ( v_ret, '/fields' );
            v_ret := build_xml_tag_str ( v_ret, '/asset' );
            v_ret := build_xml_tag_str ( v_ret, '/assets' );
            v_ret := build_xml_tag_str ( v_ret, '/workorder' );
            
      
        end loop;
         
        v_ret := build_xml_tag_str ( v_ret, '/workorders' );
        v_ret := build_xml_tag_str ( v_ret, '/ctworkorder' );
      
      /*     
        update report_repository
            set 
                 content       = utl_raw.cast_to_raw( dbms_lob.substr(v_ret,2000,1)  ),
                file_name      = 'wo_' || p_wo_id || '.xml',
                mime_type      = 'application/xml',
             --   mime_type      = 'text/csv',
                doc_size       = dbms_lob.getlength( v_ret ),
                uploaded_date  = systimestamp,
                uploaded_by    = nvl( v('APP_USER'), user )

            where id = p_doc_id;
    */    
        
            /*
            
            select utl_raw.cast_to_varchar2(dbms_lob.substr(content)) 
            from af_docs where doc_ID = 229;
            
            */

    
    htp.p(v_ret);
    
    
    end getXML;
    
    ------------------------------------------------
    -- build_xml_str
    -------------------------------------------------
    function build_xml_str (
        f_orig_str    varchar2,
        f_field_name  varchar2,
        f_field_value varchar2
    ) return varchar2
    as
    begin
        return f_orig_str || '<field name="' || f_field_name || '" value="' || f_field_value || '" />' || CHR( 13 ) || CHR( 10 );
    end build_xml_str;


------------------------------------------------
    -- build_xml_tag_str
    -------------------------------------------------
    
    function build_xml_tag_str (
        f_orig_str varchar2,
        f_tag_str  varchar2
    ) return varchar2
    as
    begin
        return f_orig_str || '<' || f_tag_str || '>' || CHR( 13 ) || CHR( 10 );
    end build_xml_tag_str;
    
  ------------------------------------------------
    -- build_xml_start_str
    -------------------------------------------------  
    
    function build_xml_start_str return varchar2
    as
    begin
        return '<?xml version="1.0" encoding="iso-8859-1"?>' || CHR( 13 ) || CHR( 10 );
    end build_xml_start_str;
    

------------------------------------------------
    -- build_xml_debug_str
    -------------------------------------------------  
    
    function build_xml_debug_str (
        f_orig_str  varchar2,
        f_param_str varchar2
    )return varchar2
    as
    begin
        return f_orig_str || '<cms_build_dt value="' || to_char( systimestamp, 'yyyy-mm-dd HH24:MI:SS' ) || '" current_wo_id="' || f_param_str || '" ></cms_build_dt>' || CHR( 13 ) || CHR( 10 );   
    end build_xml_debug_str;
    
    
    ------------------------------------------------
-- build_xml_spec_tag_str
-------------------------------------------------  
    
    function build_xml_spec_tag_str (
        f_orig_str  varchar2,
        f_tag_name  varchar2,
        f_tag_value varchar2
    ) return varchar2
    as
    begin
        return f_orig_str || '<' || f_tag_name || '>' || f_tag_value || '</' || f_tag_name || '>' || CHR( 13 ) || CHR( 10 );
    end build_xml_spec_tag_str;
    
    
------------------------------------------------
-- build_xml_wo_str
-------------------------------------------------  
         
    function build_xml_wo_str (
        f_orig_str      varchar2,
        f_wo_name       varchar2,
        f_date_creation timestamp
    ) return varchar2
    
    as
    begin
    
        return f_orig_str || '<workorder name="' || f_wo_name || '" date_creation="' || to_char( f_date_creation, 'yyyymmdd' ) || '">' || CHR( 13 ) || CHR( 10 );
     
    end build_xml_wo_str;
    
    
    ------------------------------------------------
-- build_xml_intevention_str
-------------------------------------------------  
         
    function build_xml_intevention_str (
        f_orig_str          varchar2,
        f_asset_type        varchar2,
        f_intervention_type varchar2
    ) return varchar2
    
    as
    begin
    
        return f_orig_str || 
        '<intervention asset_type="' || f_asset_type || 
        '" intervention_type="' || f_intervention_type || '">' || CHR( 13 ) || CHR( 10 );
         
     
    end build_xml_intevention_str;
    
  ------------------------------------------------
-- build_xml_asset_str
-------------------------------------------------  
         
    function build_xml_asset_str (
        f_orig_str   varchar2,
        f_asset_name varchar2,
        f_asset_type varchar2,
        f_up_name    varchar2,
        f_up_type    varchar2,
        f_down_name  varchar2,
        f_down_type  varchar2
        
    ) return varchar2
    
    as
    begin
    
        return f_orig_str || '<asset name="' || f_asset_name || '" type="' || f_asset_type || 
            '" up_name="' || f_up_name 
                || '" up_type="' || f_up_type || '" down_name="' || 
                f_down_name || '" down_type="' || f_down_type || '" >' || CHR( 13 ) || CHR( 10 );
     
    end build_xml_asset_str;   
    
    
   ------------------------------------------------
-- run_query
-------------------------------------------------   
    procedure run_query ( p_original_qry_str clob )
    is
    
        qry_str clob;
        var        clob;
        var_real_cols clob := null;
           
        var_result clob;
        var_after_first_from clob;
        
        var_1 clob;
        v_arr APEX_APPLICATION_GLOBAL.VC_ARR2;
        v_arr_result APEX_APPLICATION_GLOBAL.VC_ARR2;
        
        v_cnt int;       
        tmp_replace_str varchar2( 20 ) := '$$$TMP_PLACE_STR$$$';
        
        TYPE cur_type IS REF CURSOR;
        c              cur_type;
        
        v_qry_str clob;
        
        final_output_str clob := null;
        
    begin
     
        qry_str := replace( p_original_qry_str, chr(10), ' ' );
        qry_str := replace( p_original_qry_str, chr(13), ' ' );
        
        if  qry_str is null
            or
            instr( trim( lower( qry_str ) ), 'select' ) <> 1 
        then
            dbms_output.put_line( 'null qury or no select, not a format required' );        
            return;
        end if;
        
        if instr( trim( lower( qry_str ) ), 'from' ) = 0 
        then
            dbms_output.put_line( 'no from, not a format required' );        
            return;
        end if;
        
        
        -- get output column list, includin any alias:
        --
        -- get substring from inside of first occurence of 
        -- 'select' and 'from' 
        
        select trim( substr( 
                qry_str,
                instr( lower( qry_str ), 'select' ) + length( 'select' ), 
                instr( lower( qry_str ), 'from' ) - instr( lower( qry_str ), 'select' ) - length( 'select' )
                ) ) into var from dual;
        
            dbms_output.put_line( 'column list before detecting sub query: ' || var ); 
            
            -- try to detect sub-query inside main select and from
            
            select REGEXP_COUNT(var, 'select') into v_cnt from dual;
            
            dbms_output.put_line( 'number of select: ' || v_cnt ); 
              
            
            select trim( substr( 
                qry_str,
                instr( lower( qry_str ), 'select' ) + length( 'select' ), 
                instr( lower( qry_str ), 'from', 1, v_cnt + 1 ) - instr( lower( qry_str ), 'select' ) - length( 'select' )
                ) ) into var from dual;
     
        var := replace( var, chr(10), ' ' );
        var := replace( var, chr(13), ' ' );
        
        dbms_output.put_line( 'column list: ' || var ); 
        
        select trim( substr( 
                qry_str,
                instr( lower( qry_str ), 'from', 1, v_cnt + 1 ) + length( 'from' ) + 1, 
                length( qry_str ) - instr( lower( qry_str ), 'from', 1, v_cnt + 1 ) - length( 'from' )
         
                ) ) into var_after_first_from from dual;
        
        var_after_first_from := replace( var_after_first_from, ';', ' ' );
        
        select mod( REGEXP_COUNT(var, '"'), 2 ) into v_cnt from dual;
        
        
        -- number of '"' must be even number or 0 in column list string
        
        if v_cnt <> 0 then
            dbms_output.put_line( 'not even numbrt of ", not a format required' );        
            return;
        end if;
        
              
        -- parse based on '"' and evey even number 
        -- of element is the string as column alias inside '"'
        
        v_arr := APEX_UTIL.STRING_TO_TABLE(
                        p_string    => var,
                        p_separator => '"'
                    );
        
        if v_arr.count > 0 then
        
            FOR z IN 1..v_arr.count LOOP
                             
                if mod( z, 2 ) = 0 then
                       
             --       dbms_output.put_line( v_arr(z) ); 
                     
                    v_arr(z) := replace( v_arr(z), ',', tmp_replace_str ); 
                    
              --      dbms_output.put_line( v_arr(z) ); 
                    
                end if;
                
                
             END LOOP; 
             
           
           
           -- rebuild whole column list string with replaced 
           -- characters, erase any posible ',' inside '"' pairs
           
            var := '';
                
            FOR z IN 1..v_arr.count LOOP
                             
                if var is not null then                   
                    var := var || '"' || v_arr(z);            
                else
                    var := v_arr(z);  
                end if;
                
                
             END LOOP; 
         
         end if;
           
   --     dbms_output.put_line( 'cols  after rebuild: ' || var ); 
        
        -- now we can safely parse column list 
        -- into separate column title based on delimiter ','
        
        v_arr := APEX_UTIL.STRING_TO_TABLE(
                        p_string    => var,
                        p_separator => ','
                    );
        
        FOR z IN 1..v_arr.count LOOP
         
            dbms_output.put_line( 'col before parse: ' || v_arr(z) );  
        
            
            v_arr(z) := trim( v_arr(z) );  
            
            var_1 := v_arr(z);
            dbms_output.put_line( trim( SUBSTR( v_arr(z), instr( v_arr(z), ' ', -1 ), length(v_arr(z)) - instr( v_arr(z), ' ', -1 ) + 1 ) ) );  
            
            -- final word supposes to be an alias, but "end" from 
            -- case when MUST be excluded !!!
            -- Also, if last word are wrapped with '"', then even with a space inside, we neeeed to
            -- conside the whole words as alias !!!!
            
            
            dbms_output.put_line( 'pos of the 2nd "' || instr( v_arr(z), '"', -1 ) );
            
            if instr( v_arr(z), '"', 1 ) > 1 
            then
            
                v_arr(z) := substr( 
                                v_arr(z), 
                                instr( v_arr(z), '"', 1 ),                               
                                length( v_arr(z) ) - instr( v_arr(z), '"', 1 ) + 1
                                );
                
                dbms_output.put_line( 'alias with ":' || v_arr(z)  );
                
                if var_real_cols is not null then               
                    var_real_cols := var_real_cols || ' || ' || '''' || tmp_replace_str || '''' || ' || ' ||
                                substr( 
                                    var_1, 
                                    1,
                                    instr( var_1, '"', 1 ) - 1
                                    );
                else
                    var_real_cols := 
                                    substr( 
                                        var_1, 
                                        1,
                                        instr( var_1, '"', 1 ) - 1
                                    );
                end if;
                
                
                
                dbms_output.put_line( 'var_real_cols: ' || var_real_cols ); 
                
            else
            
            
            if instr( v_arr(z), ' ', -1 ) > 1 
               and
               trim( SUBSTR( v_arr(z), instr( v_arr(z), ' ', -1 ), length(v_arr(z)) - instr( v_arr(z), ' ', -1 ) + 1 ) ) <> 'end'
            then
            
                v_arr(z) := substr( 
                                v_arr(z), 
                                instr( v_arr(z), ' ', -1 ) + 1,                               
                                length( v_arr(z) ) - instr( v_arr(z), ' ', -1 ) + 1
                                );
                
                if var_real_cols is not null then               
                    var_real_cols := var_real_cols || ' || ' || '''' || tmp_replace_str || '''' || ' || ' ||
                                substr( 
                                    var_1, 
                                    1,
                                    instr( var_1, ' ', -1 )
                                    );
                else
                    var_real_cols := 
                                    substr( 
                                        var_1, 
                                        1,
                                        instr( var_1, ' ', -1 )
                                    );
                end if;
                
                
                
                dbms_output.put_line( 'var_real_cols: ' || var_real_cols ); 
                
            else
                
                if var_real_cols is not null then               
                    var_real_cols := var_real_cols || ' || ' || '''' || tmp_replace_str || ''' || ' || var_1;
                else
                    var_real_cols := var_1;
                end if;
                
          --      dbms_output.put_line( 'var_real_cols: ' || var_real_cols ); 
                
            end if;
            
            
                end if;
                
            -- for each parsed column title, resume replaced ','
            
            v_arr(z) := replace( v_arr(z), tmp_replace_str, ',' ); 
            
            -- delete '"'
            
            v_arr(z) := replace( v_arr(z), '"', '' ); 
            
      --      dbms_output.put_line( v_arr(z) ); 
            
         END LOOP;
           
         v_cnt := 0;
         
         -- begin to build csv
         FOR z IN 1..v_arr.count LOOP
         
            if final_output_str is null then
                final_output_str := '"' || v_arr(z) || '"';
            else
                final_output_str := final_output_str || ',"' || v_arr(z) || '"';
            end if;
            
         end loop;
         
         final_output_str := final_output_str || CHR( 13 ) || CHR( 10 );
         
         
         dbms_output.put_line( 'var_real_cols: ' || var_real_cols ); 
         
         dbms_output.put_line( 'query to run: ' || 'select ' || var_real_cols || ' from ' ||  var_after_first_from );
         
      --   OPEN c FOR qry_str;
         OPEN c FOR 'select ' || var_real_cols || ' from ' ||  var_after_first_from;
        loop
        
        v_cnt := v_cnt + 1;

            FETCH c INTO var_result;
            EXIT WHEN c%NOTFOUND;
            
       --     dbms_output.put_line( 'var_result: ' || var_result );
            
            v_arr_result := APEX_UTIL.STRING_TO_TABLE(
                                p_string    => var_result,
                                p_separator => tmp_replace_str
                            );
        
            if v_arr_result.count > 0 then
            
         --       dbms_output.put_line( 'ROW : ' || v_cnt );
                
                FOR z IN 1..v_arr_result.count LOOP
                
                
            --    dbms_output.put_line( 'column [ ' || v_arr(z) || ' ] : ' || v_arr_result(z) );
               
               
                if z = 1 then
                     final_output_str := final_output_str || '"' || v_arr_result(z) || '"';
                else
                     final_output_str := final_output_str || ',"' || v_arr_result(z) || '"';
                end if;
               
             
                
                end loop;
                
                final_output_str := final_output_str || CHR( 13 ) || CHR( 10 );
            
            end if;
        
        end loop;
            
        FOR z IN 1..length( final_output_str ) / 32000 + 1 LOOP
            
            htp.p( substr( final_output_str, ( z - 1 ) * 32000 + 1, 32000 ) );
            
        end loop;
        
     --   htp.p(final_output_str);

        
    end run_query;