PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is
xmlstr varchar2(32767);
line varchar2(32767);
cnt NUMBER;
leng NUMBER;


begin
    xmlstr := dbms_lob.SUBSTR(result,32767);
    leng := dbms_lob.getlength(result);

  NHLS_UTILITIES.OUTPUT_log('XML length: ' || leng);

  SELECT count(*)
  INTO cnt
  FROM xxnhls_pay_hours2;

  NHLS_UTILITIES.OUTPUT_log('count lines: ' || cnt);

  FOR i in 1..cnt LOOP
    line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);

    Fnd_File.PUT_LINE(Fnd_File.OUTPUT,line);;

    xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
  END LOOP;
end; 

CREATE OR REPLACE PROCEDURE xxnhls_annual_element_values (errbuf OUT NOCOPY  VARCHAR2
                                                           ,retcode OUT NOCOPY  NUMBER
                                                           ,p_start_date DATE
                                                          , p_end_date DATE
                                                --          , p_element_type VARCHAR2
                                                         ) IS
/******************************************************************************
   name:       xxnhls_annual_element_values
   purpose:    xml output of all input values for a specific element over a period
   revisions:
   ver        date              author           description
   ---------  ----------  ---------------  ------------------------------------
   1.0        04/09/2013  Johann du Toit   initial creation 


   parameters:        p_start_date DATE
                      p_end_date DATE

   called by:       concurrent request


/* notes:
*******************************************************************************/     

gv_element_type_id NUMBER;
gv_run_result_id NUMBER;
gv_error NUMBER := 0;
element_entry NUMBER;
gv_input_cnt NUMBER :=0;
gv_assignment_number per_all_assignments_f.assignment_number%TYPE;
gv_emp_name per_all_people_f.first_name%TYPE;
gv_emp_last_name per_all_people_f.Last_name%TYPE;
gv_ccentre pay_cost_allocation_keyflex.segment3%TYPE;
gv_ccentre_desc FND_FLEX_VALUES_tl.description%TYPE;
gv_header VARCHAR2(32767) := 'x';
sqlstr VARCHAR2 (32767);
queryCtx dbms_xmlquery.ctxType;
result CLOB;
                                                            
CURSOR actions IS
SELECT /*+ INDEX_SS(PAA) */ paa.assignment_id, paa.assignment_action_id, date_earned
FROM pay_assignment_actions paa
     , pay_payroll_actions ppa
WHERE paa.payroll_action_id = ppa.payroll_action_id     
AND action_type = 'R' -- run
AND ppa.ACTION_STATUS = 'C' --completed
and paa.run_type_id = 62
--and assignment_id = 149
AND effective_date between p_start_date and p_end_date
order by assignment_id;

CURSOR element_values (p_assignment_id NUMBER,p_element_id NUMBER, p_element_entry_id NUMBER,  p_date DATE)  IS 
SELECT pivf.name, PEEVF.SCREEN_ENTRY_VALUE
FROM  pay_element_entries_f peef
      , PAY_ELEMENT_ENTRY_VALUES_F peevf
      , pay_input_values_f pivf
WHERE assignment_id = p_assignment_id
AND peef.element_type_id = p_element_id
AND PEEVF.INPUT_VALUE_ID = PIVF.INPUT_VALUE_ID
AND PEEF.ELEMENT_ENTRY_ID = PEEVF.ELEMENT_ENTRY_ID
and peef.element_entry_id = p_element_entry_id
AND p_date between PEEF.EFFECTIVE_START_DATE and PEEF.EFFECTIVE_END_DATE
AND p_date between PEEvF.EFFECTIVE_START_DATE and PEEvF.EFFECTIVE_END_DATE
AND p_date between Pivf.EFFECTIVE_START_DATE and PivF.EFFECTIVE_END_DATE
order by PEEVF.INPUT_VALUE_ID  ;


CURSOR col IS
SELECT LOWER(COLUMN_NAME) column_name, column_id
FROM SYS.DBA_TAB_COLUMNS
WHERE owner = UPPER('Apps')
AND table_name = UPPER('xxnhls_pay_hours2')
ORDER BY column_id;

PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is
xmlstr long; --varchar2(32767);
line varchar2(32767);
cnt NUMBER;
leng NUMBER;


begin
    xmlstr := dbms_lob.SUBSTR(result,32767);
    leng := dbms_lob.getlength(result);
    
    NHLS_UTILITIES.OUTPUT_log('XML length: ' || leng);
  --  NHLS_UTILITIES.OUTPUT_log('XML string 1: ' || xmlstr);
    
    SELECT count(*) 
    INTO cnt
    FROM xxnhls_pay_hours2;
    
    NHLS_UTILITIES.OUTPUT_log('count lines: ' || cnt);
    
    FOR i in 1..cnt LOOP
      line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
        
        NHLS_UTILITIES.OUTPUT_OUT(line);
      --   dbms_output.put_line(line);
       
        xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
    END LOOP;
    /*
    
    loop
    exit when xmlstr is null;
    
        line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
        
      --   Fnd_File.PUT_LINE(Fnd_File.OUTPUT,line);
      --   dbms_output.put_line(line);
       
        xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
        
    end loop; */
end;            
PROCEDURE pivot_data IS

BEGIN
   NHLS_UTILITIES.OUTPUT_LOG ('Pivot data');
  --Drop table
  BEGIN 
   NHLS_UTILITIES.OUTPUT_log('Drop pivot table');
   
    EXECUTE IMMEDIATE 'drop table xxnhls_pay_hours2';
    
  EXCEPTION
   WHEN OTHERS THEN
     NHLS_UTILITIES.OUTPUT_log('Error dropping table:' || SQLERRM);
  END;  
  
  -- Create Table
  BEGIN
  
    NHLS_UTILITIES.OUTPUT_log('Create pivot table');
    execute immediate 'CREATE table xxnhls_pay_hours2  as (select *  from table( pivot(  ''select * from xxnhls_pay_hours'' ) ))';
    
  EXCEPTION
    WHEN OTHERS THEN
      NHLS_UTILITIES.OUTPUT_log('Error creating pivot table: ' || SQLERRM);
  END;
  
EXCEPTION
  WHEN OTHERS THEN
    NHLS_UTILITIES.OUTPUT_log('Error in pivot:' || SQLERRM);
    IF SQLCODE = -942 THEN
      NHLS_UTILITIES.OUTPUT_log('the table did not exist!');
    END IF;
    
END;



BEGIN
    SELECT element_type_id
    INTO gv_element_type_id
    FROM pay_element_types_f
    WHERE element_name = 'NHLS Hours Input' ;
    --p_element_type;
    NHLS_UTILITIES.OUTPUT_LOG ('Element_type_id: ' || gv_element_type_id);
    


DELETE FROM xxnhls_pay_hours;
 
    
    FOR ass in actions LOOP

           BEGIN 
            SELECT run_result_id , ELEMENT_ENTRY_ID
            INTO gv_run_result_id, element_entry
            FROM pay_run_results
            where element_type_id = gv_element_type_id
            AND assignment_action_id = ass.assignment_action_id;
           
           EXCEPTION
             WHEN OTHERS THEN 
               gv_error := 1; 
           END; 
         
     IF gv_error  = 0 THEN  
     
      BEGIN 
      
            SELECT /*+ INDEX_DESC(PAAF) */ assignment_number
                   , first_name
                   , last_name
                   , NHLS_UTILITIES.GET_EMPLOYEE_CCENTRE(paaf.assignment_id,ass.date_earned)ccentre
            INTO gv_assignment_number
                 , gv_emp_name
                 , gv_emp_last_name
                 , gv_ccentre         
            FROM per_all_assignments_f paaf
                 , per_all_people_f papf
            WHERE assignment_id = ass.assignment_id
            AND paaf.person_id = papf.person_id
            AND ass.date_earned between papf.effective_start_date and papf.effective_end_date
            AND ass.date_earned between paaf.effective_start_date and paaf.effective_end_date;
                        
            gv_ccentre_desc := NHLS_UTILITIES.GET_EMPLOYEE_CCENTRE_DESC(gv_ccentre);
            

                
           -- NHLS_UTILITIES.OUTPUT_LOG ('..Assignment_id: ' || ass.assignment_id);
           -- NHLS_UTILITIES.OUTPUT_LOG ('..Date_earned: ' || ass.date_earned);
          --  NHLS_UTILITIES.OUTPUT_LOG ('..Element Entry ID: ' || element_entry);
          --  NHLS_UTILITIES.OUTPUT_LOG ('..Assignment_action_id: ' || ass.Assignment_action_id);
            
            gv_input_cnt := 1;
            
              FOR va IN element_values (ass.assignment_id, gv_element_type_id, element_entry, ass.date_earned) LOOP
               
                INSERT INTO xxnhls_pay_hours values (gv_assignment_number
                                                    ,  gv_emp_name
                                                     , gv_emp_last_name
                                                     , gv_ccentre   
                                                     , gv_ccentre_desc
                                                     , to_char(ass.date_earned, 'MON-YY')
                                                     , replace(va.name,' ','_')
                                                     , nvl(va.SCREEN_ENTRY_VALUE,0));      
                
               commit; 
              END LOOP;
                 

            END;
    
     END IF;
     
       gv_error := 0;
       
    END LOOP;
   
-- Output


  pivot_data; 
  
  for a IN col LOOP
    gv_header := replace(a.column_name,' ','_') || ',' || gv_header ;
   
  END LOOP;
  

   gv_header := substr(gv_header,1, length(gv_header)-2);
   
   NHLS_UTILITIES.OUTPUT_LOG('Header:' || gv_header);
   
  sqlstr := 'SELECT ' || gv_header ||' FROM apps.xxnhls_pay_hours2';
  
 NHLS_UTILITIES.OUTPUT_LOG(sqlstr); 
  
  queryCtx := dbms_xmlquery.newContext(sqlstr);
 
    dbms_xmlquery.setRowTag(
          queryCtx
        , 'EMP'
      );
      dbms_xmlquery.setRowSetTag(
          queryCtx
        , 'EMPSET'
      );         
      
  result := dbms_xmlquery.getXml(queryCtx);
  printClobOut(result);
  dbms_xmlquery.closeContext(queryCtx);                                    
  

  
END;

PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is
xmlstr varchar2(32767);
line varchar2(32767);
cnt NUMBER;

begin
  xmlstr := dbms_lob.SUBSTR(result,32767);

  SELECT count(*)
  INTO cnt
  FROM xxnhls_pay_hours2;

  FOR i in 1..cnt LOOP
    line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
    Fnd_File.PUT_LINE(Fnd_File.OUTPUT,line);;
    xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
  END LOOP;

end; 

SQL> declare
  2    result varchar2(100) := 'this|is|an|example|';
  3    l pls_integer := length(result);
  4    i pls_integer := 1;
  5    j pls_integer;
  6    k pls_integer := 1;
  7  begin
  8    while k <= l loop
  9      j := instr(result, '|', 1, i);
 10      dbms_output.put_line(substr(result, k, j-k));
 11      k := j + 1;
 12      i := i + 1;
 13    end loop;
 14  end;
 15  /
this
is
an
example

PL/SQL procedure successfully completed.