[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| PUBLIC PROCEDURE insert_to_audit_list
|
| DESCRIPTION
| This procedure inserts date ranges included in a array into the
| database.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| audit list API
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| RETURNS
| None.
|
| PARAMETERS
| p_person_id IN Person to be added.
| p_range_table IN Array containing the entries to be created.
| p_auto_audit_id OUT Identifier of the new record created, if multiple created returns -1.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 28-Jun-2004 J Rautiainen Created
|
*=======================================================================*/
PROCEDURE insert_to_audit_list(p_person_id IN NUMBER,
p_range_table IN range_table,
p_auto_audit_id OUT NOCOPY NUMBER) IS
l_auto_audit_id NUMBER;
select AP_AUD_AUTO_AUDITS_S.nextval INTO l_auto_audit_id from sys.DUAL;
INSERT INTO AP_AUD_AUTO_AUDITS(
AUTO_AUDIT_ID,
EMPLOYEE_ID,
AUDIT_REASON_CODE,
START_DATE,
END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES (
l_auto_audit_id,
p_person_id,
p_range_table(i).audit_reason_code,
decode(p_range_table(i).start_date,
c_min_date, SYSDATE,
p_range_table(i).start_date),
decode(p_range_table(i).end_date,
c_max_date, NULL,
p_range_table(i).end_date),
SYSDATE,
nvl(fnd_global.user_id, -1),
fnd_global.conc_login_id,
SYSDATE,
nvl(fnd_global.user_id, -1));
END insert_to_audit_list;
| PUBLIC PROCEDURE update_audit_list_entry_dates
|
| DESCRIPTION
| This procedure updates a audit list entry data.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| audit list API
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| RETURNS
| None.
|
| PARAMETERS
| p_auto_audit_id IN Identifier of the record to be updated.
| p_start_date IN Start date for the record.
| p_end_date IN End date for the record.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 28-Jun-2004 J Rautiainen Created
|
*=======================================================================*/
PROCEDURE update_audit_list_entry_dates(p_auto_audit_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE) IS
BEGIN
IF before(p_end_date,p_start_date) THEN
delete_audit_list_entry(p_auto_audit_id);
UPDATE AP_AUD_AUTO_AUDITS
SET START_DATE = p_start_date,
END_DATE = decode(p_end_date,
c_max_date, NULL,
p_end_date),
LAST_UPDATE_LOGIN = fnd_global.conc_login_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = nvl(fnd_global.user_id, -1)
WHERE AUTO_AUDIT_ID = p_auto_audit_id;
END update_audit_list_entry_dates;
| p_auto_audit_id IN Identifier of the record to be updated.
| p_new_date_range IN The new entry date range.
| p_old_date_range IN The old entry date range.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 28-Jun-2004 J Rautiainen Created
|
*=======================================================================*/
PROCEDURE move_existing_entry(p_auto_audit_id IN NUMBER,
p_new_date_range IN Date_Range_Type,
p_old_date_range IN Date_Range_Type) IS
BEGIN
IF before(p_new_date_range.start_date, p_old_date_range.start_date)
OR p_new_date_range.start_date = p_old_date_range.start_date THEN
update_audit_list_entry_dates(p_auto_audit_id,
p_new_date_range.end_date + 1,
p_old_date_range.end_date);
update_audit_list_entry_dates(p_auto_audit_id,
p_old_date_range.start_date,
p_new_date_range.start_date - 1);
| of the new record so that when inserting the new record it is disregarded. |
| The dates are set to the limits on purpose (start to latest and end to earliest). |
*====================================================================================*/
p_new_date_range.start_date := c_max_date;
insert_ranges range_table;
update_audit_list_entry_dates(p_overlap_rec.auto_audit_id,
p_old_date_range.start_date,
p_new_date_range.start_date - 1);
add_range_to_be_inserted(temp_range, insert_ranges, counter);
insert_to_audit_list(p_overlap_rec.employee_id, insert_ranges, l_auto_audit_id);
add_range_to_be_inserted(temp_range, p_range_table, p_counter);
| PUBLIC PROCEDURE delete_audit_list_entry
|
| DESCRIPTION
| This procedure deletes a given audit list entry.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| audit list API
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| RETURNS
| None.
|
| PARAMETERS
| p_auto_audit_id IN Identifier of the record to be deleted.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 28-Jun-2004 J Rautiainen Created
|
*=======================================================================*/
PROCEDURE delete_audit_list_entry(p_auto_audit_id NUMBER) IS
BEGIN
DELETE AP_AUD_AUTO_AUDITS
WHERE AUTO_AUDIT_ID = p_auto_audit_id;
END delete_audit_list_entry;
SELECT auto_audit_id, start_date, end_date
FROM ap_aud_auto_audits
WHERE employee_id = p_emp_rec.person_id
AND audit_reason_code = p_audit_rec.audit_reason_code
AND end_date is NULL;
update_audit_list_entry_dates(target_rec.auto_audit_id,
target_rec.start_date,
p_audit_rec.end_date);
SELECT *
FROM ap_aud_auto_audits
WHERE employee_id = p_emp_rec.person_id
AND ( ( trunc(start_date) between p_start_date
and p_end_date
OR
trunc(NVL(end_date,c_max_date)) between p_start_date
and p_end_date
)
OR
( p_start_date between trunc(start_date)
and trunc(NVL(end_date, c_max_date))
OR
p_end_date between trunc(start_date)
and trunc(NVL(end_date, c_max_date))
)
)
order by start_date;
insert_ranges range_table;
delete_audit_list_entry(overlap_rec.auto_audit_id);
| When creating termination any old entry included in the new date range is deleted. |
| In overlap case with any other status than termination, the overlap is removed with|
| termination taking precedence. |
*====================================================================================*/
IF includes(new_date_range, old_date_range) THEN
delete_audit_list_entry(overlap_rec.auto_audit_id);
| When creating loa any old entry included in the new date range is deleted, unless |
| the old record is termination, in which case the termination takes precedence and |
| the new loa record is updated so that there is no overlap. |
| If the there is overlap and the old record is not termination the overlap is |
| removed with loa taking precedence. |
*====================================================================================*/
IF includes(new_date_range, old_date_range) THEN
IF overlap_rec.audit_reason_code = c_termination THEN
split_new_entry(new_date_range, old_date_range,insert_ranges, counter);
delete_audit_list_entry(overlap_rec.auto_audit_id);
split_new_entry(new_date_range, old_date_range,insert_ranges, counter);
delete_audit_list_entry(overlap_rec.auto_audit_id);
add_range_to_be_inserted(temp_range, insert_ranges, counter);
insert_to_audit_list(p_emp_rec.person_id, insert_ranges, p_auto_audit_id);
| PUBLIC PROCEDURE add_range_to_be_inserted
|
| DESCRIPTION
| This procedure adds a range to the array used to store the ranges to
| be inserted as audit list entries.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| audit list API
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| RETURNS
| None.
|
| PARAMETERS
| p_date_range IN The entry date range.
| p_range_table IN OUT Array containing the new entries to be created.
| p_counter IN OUT Counter storing the count of lines in the array.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 28-Jun-2004 J Rautiainen Created
|
*=======================================================================*/
PROCEDURE add_range_to_be_inserted(p_range IN Date_Range_Type,
p_range_table IN OUT NOCOPY range_table,
counter IN OUT NOCOPY NUMBER) IS
BEGIN
IF empty(p_range)
OR before(p_range.end_date,p_range.start_date) THEN
RETURN;
END add_range_to_be_inserted;
SELECT *
FROM ap_aud_auto_audits
WHERE employee_id = p_emp_rec.person_id
AND ( ( trunc(start_date) between p_start_date
and p_end_date
OR
trunc(NVL(end_date,c_max_date)) between p_start_date
and p_end_date
)
OR
( p_start_date between trunc(start_date)
and trunc(NVL(end_date, c_max_date))
OR
p_end_date between trunc(start_date)
and trunc(NVL(end_date, c_max_date))
)
)
order by start_date;
| When deleting a data range any old entry included in the date range is deleted. |
| In overlap case the overlap is removed. |
*====================================================================================*/
IF includes(new_date_range, old_date_range) THEN
delete_audit_list_entry(overlap_rec.auto_audit_id);