-- "Implicit Cursor BULK COLLECT"

DECLARE

  TYPE element_entries_rec IS RECORD
    (l_payroll           VARCHAR2(2),
     l_dept              hr.pay_people_groups.segment9%TYPE,
     l_name              hr.per_all_people_f.full_name%TYPE,
     l_assn_num          hr.per_all_assignments_f.assignment_number%TYPE,
     l_element_name      hr.pay_element_types_f.element_name%TYPE,
     l_hours             hr.pay_element_entry_values_f.screen_entry_value%TYPE,
     l_date_earned       hr.pay_element_entry_values_f.screen_entry_value%TYPE,
     l_date_paid         hr.pay_element_entry_values_f.screen_entry_value%TYPE,
     l_source            hr.pay_element_entry_values_f.screen_entry_value%TYPE,
     l_ee_eff_start      DATE,
     l_ee_eff_end        DATE,
     l_assn_eff_start    DATE,
     l_assn_eff_end      DATE,
     l_creator_type      pay_element_entries_f.creator_type%TYPE,
     l_el_entry_id       NUMBER,
     l_source_id         NUMBER);

  TYPE el_entries_table IS TABLE OF element_entries_rec;-- INDEX BY BINARY_INTEGER;

  l_el_entries_table el_entries_table;
  l_date  DATE;

BEGIN

  DBMS_OUTPUT.PUT_LINE('Start Time: '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));

  SELECT DECODE(a.payroll_id,81,'B1',82,'B2',83,'M1'),
    ppg.segment9,
    p.full_name,
    a.assignment_number,  
    t.element_name,
    vh.screen_entry_value,
    ve.screen_entry_value,
    vp.screen_entry_value,
    vs.screen_entry_value,
    e.effective_start_date, 
    e.effective_end_date, 
    a.effective_start_date,
    a.effective_end_date,
    e.creator_type,
    e.element_entry_id,
    e.source_id
  BULK COLLECT INTO l_el_entries_table
  FROM hr.per_all_assignments_f a, 
    hr.per_all_people_f p,
    hr.pay_people_groups ppg,
    hr.per_time_periods ptp,
    hr.per_assignment_status_types past,
    hr.pay_element_entries_f e, 
    hr.pay_element_entry_values_f vh, 
    hr.pay_element_entry_values_f ve, 
    hr.pay_element_entry_values_f vp, 
    hr.pay_element_entry_values_f vs, 
    hr.pay_element_types_f t, 
    hr.pay_input_values_f ih, 
    hr.pay_input_values_f ie,
    hr.pay_input_values_f ip,
    hr.pay_input_values_f isi
  WHERE UPPER(user_status) LIKE '%TERMINATE%' and mod(a.person_id,200)=0
    AND ve.screen_entry_value IS NOT NULL
    AND e.effective_start_date BETWEEN p.effective_start_date AND p.effective_end_date
    AND e.effective_start_date BETWEEN t.effective_start_date AND t.effective_end_date
    AND e.effective_start_date BETWEEN ih.effective_start_date AND ih.effective_end_date
    AND e.effective_start_date BETWEEN ie.effective_start_date AND ie.effective_end_date
    AND e.effective_start_date BETWEEN ip.effective_start_date AND ip.effective_end_date
    AND e.effective_start_date BETWEEN isi.effective_start_date AND isi.effective_end_date
    AND e.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
    AND a.person_id = p.person_id
    AND a.assignment_id = e.assignment_id
    AND a.people_group_id = ppg.people_group_id
    AND past.assignment_status_type_id = a.assignment_status_type_id 
    AND ptp.payroll_id = a.payroll_id
    AND t.element_type_id = e.element_type_id
    -- hours 
    AND vh.element_entry_id = e.element_entry_id
    AND vh.effective_start_date = e.effective_start_date
    AND vh.effective_end_date = e.effective_end_date
    AND ih.input_value_id = vh.input_value_id
    AND UPPER(ih.name) LIKE 'HOURS'
    -- pp date earned  
    AND ve.element_entry_id = e.element_entry_id
    AND ve.effective_start_date = e.effective_start_date
    AND ve.effective_end_date = e.effective_end_date
    AND ie.input_value_id = ve.input_value_id
    AND UPPER(ie.name) = 'PAY PERIOD DATE EARNED'
    -- pp date paid  
    AND vp.element_entry_id = e.element_entry_id
    AND vp.effective_start_date = e.effective_start_date
    AND vp.effective_end_date = e.effective_end_date
    AND ip.input_value_id = vp.input_value_id
    AND UPPER(ip.name) = 'PAY PERIOD DATE PAID' 
    -- source 
    AND vs.element_entry_id = e.element_entry_id
    AND vs.effective_start_date = e.effective_start_date
    AND vs.effective_end_date = e.effective_end_date
    AND isi.input_value_id = vs.input_value_id
    AND UPPER(isi.name) = 'SOURCE' ; 

  FOR l_count IN l_el_entries_table.FIRST..l_el_entries_table.LAST
  LOOP
    NULL;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('End Time: '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
    
END;


--Start Time: 07-FEB-2007 14:08:00
--End Time: 07-FEB-2007 14:16:33

--"Explicit Cursor FOR Loop"

DECLARE

  CURSOR c_element_entries IS
  SELECT DECODE(a.payroll_id,81,'B1',82,'B2',83,'M1') payroll,
    ppg.segment9 dept,
    p.full_name,
    a.assignment_number,  
    t.element_name,
    vh.screen_entry_value hours,
    ve.screen_entry_value pp_date_earned,
    vp.screen_entry_value pp_date_paid,
    vs.screen_entry_value source,
    e.effective_start_date ee_eff_start, 
    e.effective_end_date ee_eff_end, 
    e.creator_type,
    e.element_entry_id,
    e.source_id,
	a.effective_start_date assn_eff_start,
	a.effective_end_date assn_eff_end
  FROM hr.per_all_assignments_f a, 
    hr.per_all_people_f p,
    hr.pay_people_groups ppg,
    hr.per_time_periods ptp,
    hr.per_assignment_status_types past,
    hr.pay_element_entries_f e, 
    hr.pay_element_entry_values_f vh, 
    hr.pay_element_entry_values_f ve, 
    hr.pay_element_entry_values_f vp, 
    hr.pay_element_entry_values_f vs, 
    hr.pay_element_types_f t, 
    hr.pay_input_values_f ih, 
    hr.pay_input_values_f ie,
    hr.pay_input_values_f ip,
    hr.pay_input_values_f isi
  WHERE UPPER(user_status) LIKE '%TERMINATE%' and mod(a.person_id,200)=0
    AND ve.screen_entry_value IS NOT NULL
    AND e.effective_start_date BETWEEN p.effective_start_date AND p.effective_end_date
    AND e.effective_start_date BETWEEN t.effective_start_date AND t.effective_end_date
    AND e.effective_start_date BETWEEN ih.effective_start_date AND ih.effective_end_date
    AND e.effective_start_date BETWEEN ie.effective_start_date AND ie.effective_end_date
    AND e.effective_start_date BETWEEN ip.effective_start_date AND ip.effective_end_date
    AND e.effective_start_date BETWEEN isi.effective_start_date AND isi.effective_end_date
    AND e.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
    AND a.person_id = p.person_id
    AND a.assignment_id = e.assignment_id
    AND a.people_group_id = ppg.people_group_id
    AND past.assignment_status_type_id = a.assignment_status_type_id 
    AND ptp.payroll_id = a.payroll_id
    AND t.element_type_id = e.element_type_id
    -- hours 
    AND vh.element_entry_id = e.element_entry_id
    AND vh.effective_start_date = e.effective_start_date
    AND vh.effective_end_date = e.effective_end_date
    AND ih.input_value_id = vh.input_value_id
    AND UPPER(ih.name) LIKE 'HOURS'
    -- pp date earned  
    AND ve.element_entry_id = e.element_entry_id
    AND ve.effective_start_date = e.effective_start_date
    AND ve.effective_end_date = e.effective_end_date
    AND ie.input_value_id = ve.input_value_id
    AND UPPER(ie.name) = 'PAY PERIOD DATE EARNED'
    -- pp date paid  
    AND vp.element_entry_id = e.element_entry_id
    AND vp.effective_start_date = e.effective_start_date
    AND vp.effective_end_date = e.effective_end_date
    AND ip.input_value_id = vp.input_value_id
    AND UPPER(ip.name) = 'PAY PERIOD DATE PAID' 
    -- source 
    AND vs.element_entry_id = e.element_entry_id
    AND vs.effective_start_date = e.effective_start_date
    AND vs.effective_end_date = e.effective_end_date
    AND isi.input_value_id = vs.input_value_id --and full_name = 'Renwick, Brian'
    AND UPPER(isi.name) = 'SOURCE'; 
	
--order by 1,2,3,4,7,5

l_count number := 0;
l_date date;

BEGIN

dbms_output.put_line('Start Time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));

  FOR l_rec IN c_element_entries
  LOOP
    NULL;
  END LOOP;
  
dbms_output.put_line('End Time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));

END;

--Start Time: 07-FEB-2007 14:19:21
--End Time: 07-FEB-2007 14:27:59