98: FUNCTION retro_hours(i_row_id IN VARCHAR2) RETURN NUMBER IS
99:
100: CURSOR new_hours IS
101: SELECT retro.hours - expired.hours
102: FROM hxt_det_hours_worked_f expired,
103: hxt_det_hours_worked_f retro
104: WHERE retro.rowid = CHARTOROWID(i_row_id)
105: AND retro.parent_id = expired.parent_id
106: AND retro.element_type_id = expired.element_type_id
99:
100: CURSOR new_hours IS
101: SELECT retro.hours - expired.hours
102: FROM hxt_det_hours_worked_f expired,
103: hxt_det_hours_worked_f retro
104: WHERE retro.rowid = CHARTOROWID(i_row_id)
105: AND retro.parent_id = expired.parent_id
106: AND retro.element_type_id = expired.element_type_id
107: AND expired.pay_status = 'A'
105: AND retro.parent_id = expired.parent_id
106: AND retro.element_type_id = expired.element_type_id
107: AND expired.pay_status = 'A'
108: AND expired.effective_end_date = (SELECT MAX(ex.effective_end_date)
109: FROM hxt_det_hours_worked_f ex
110: WHERE ex.effective_end_date < retro.effective_start_date
111: AND ex.parent_id = retro.parent_id
112: AND ex.pay_status = 'A'
113: AND ex.element_type_id = retro.element_type_id);
133: FUNCTION retro_amount(i_row_id IN VARCHAR2) RETURN NUMBER IS
134:
135: CURSOR new_amount IS
136: SELECT retro.amount - expired.amount
137: FROM hxt_det_hours_worked_f expired,
138: hxt_det_hours_worked_f retro
139: WHERE retro.rowid = CHARTOROWID(i_row_id)
140: AND retro.parent_id = expired.parent_id
141: AND retro.element_type_id = expired.element_type_id
134:
135: CURSOR new_amount IS
136: SELECT retro.amount - expired.amount
137: FROM hxt_det_hours_worked_f expired,
138: hxt_det_hours_worked_f retro
139: WHERE retro.rowid = CHARTOROWID(i_row_id)
140: AND retro.parent_id = expired.parent_id
141: AND retro.element_type_id = expired.element_type_id
142: AND expired.pay_status = 'A'
140: AND retro.parent_id = expired.parent_id
141: AND retro.element_type_id = expired.element_type_id
142: AND expired.pay_status = 'A'
143: AND expired.effective_end_date = (SELECT MAX(ex.effective_end_date)
144: FROM hxt_det_hours_worked_f ex
145: WHERE ex.effective_end_date < retro.effective_start_date
146: AND ex.parent_id = retro.parent_id
147: AND ex.pay_status = 'A'
148: AND ex.element_type_id = retro.element_type_id);